View source for Working with Dates and Times in MySQL

In this chapter of [[MySQL Essentials]] we are going to look at storing dates and times in a database table and also retrieving and manipulating these values.

== Date and Time Formats ==

MySQL supports a number of date and time column formats. These can be summarized as follows:

* DATE - Stores a date value in the form YYYY-MM-DD. For example 2008-10-23.

* DATETIME - Stores a date and time value of the form YYYY-MM-DD HH:MM:SS. For example 2008-10-23 10:37:22. The supported range of dates and times is 1000-01-01 00:00:00 all the way through to 9999-12-31 23:59:59

* TIMESTAMP - Similar to DATETIME with some differences depending on the version of MySQL and the mode in which the server is running.

== Creating Date and Time Fields ==

A table containing DATE and DATETIME columns is created much the same way as any other columns in a table. For example, we can create a new table called ''orders'' which contains order number, order item, order date and order delivery columns as follows:

<pre>
CREATE TABLE `MySampleDB`.`orders` (
  `order_no` INT  NOT NULL AUTO_INCREMENT,
  `order_item` TEXT  NOT NULL,
  `order_date` DATETIME  NOT NULL,
  `order_delivery` DATE  NOT NULL
)
ENGINE = InnoDB;
</pre>

The order_date column is a DATETIME field because we want to record the precise time and date that the order was placed. For the delivery date it will be impossible to predict the exact time of day the package will arrive, so we just want to record the date. 

== Date and Time Formats ==

Whilst it is most common to store dates using a dash (-) as the delimiter and a colon (:) as the time delimiter it is in fact possible to use any character, or no character between the date and time segments. For example, the following formats all achieve the same result:

<pre>
2008-10-23 10:37:22
20081023103722
2008/10/23 10.37.22
2008*10*23*10*37*22
</pre>

As you can see MySQL provides considerable flexibility in how dates and times are formatted. 

== Date and Time Functions ==

In addition to providing mechanisms for storing dates and times, MySQL also provides a wide range of functions that can be used to manipulate dates and times. The following table provides a list of the more common functions available for working with times and dates in MySQL:

 <table border="1">
<colgroup>
<col>

<col>
</colgroup>
<thead><tr>
<th><span class="bold"><strong>Name</strong></span></th>
<th><span class="bold"><strong>Description</strong></span></th>
</tr></thead>
<tbody>
<tr>
<td>
<a href="date-and-time-functions.html#function_adddate"><code class="literal">ADDDATE()</code></a>(v4.1.1)</td>
<td>Add dates</td>
</tr>

<tr>
<td>
<a href="date-and-time-functions.html#function_addtime"><code class="literal">ADDTIME()</code></a>(v4.1.1)</td>
<td>Add time</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_convert-tz"><code class="literal">CONVERT_TZ()</code></a>(v4.1.3)</td>
<td>Convert from one timezone to another</td>
</tr>
<tr>

<td><a href="date-and-time-functions.html#function_curdate"><code class="literal">CURDATE()</code></a></td>
<td>Return the current date</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_current-date"><code class="literal">CURRENT_DATE()</code>, <code class="literal">CURRENT_DATE</code></a></td>
<td>Synonyms for CURDATE()</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_current-time"><code class="literal">CURRENT_TIME()</code>, <code class="literal">CURRENT_TIME</code></a></td>

<td>Synonyms for CURTIME()</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_current-timestamp"><code class="literal">CURRENT_TIMESTAMP()</code>, <code class="literal">CURRENT_TIMESTAMP</code></a></td>
<td>Synonyms for NOW()</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_curtime"><code class="literal">CURTIME()</code></a></td>
<td>Return the current time</td>
</tr>

<tr>
<td><a href="date-and-time-functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a></td>
<td>Add two dates</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_date-format"><code class="literal">DATE_FORMAT()</code></a></td>
<td>Format date as specified</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_date-sub"><code class="literal">DATE_SUB()</code></a></td>
<td>Subtract two dates</td>

</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_date"><code class="literal">DATE()</code></a>(v4.1.1)</td>
<td>Extract the date part of a date or datetime expression</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_datediff"><code class="literal">DATEDIFF()</code></a>(v4.1.1)</td>
<td>Subtract two dates</td>
</tr>

<tr>
<td>
<a href="date-and-time-functions.html#function_day"><code class="literal">DAY()</code></a>(v4.1.1)</td>
<td>Synonym for DAYOFMONTH()</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_dayname"><code class="literal">DAYNAME()</code></a>(v4.1.21)</td>
<td>Return the name of the weekday</td>
</tr>
<tr>

<td><a href="date-and-time-functions.html#function_dayofmonth"><code class="literal">DAYOFMONTH()</code></a></td>
<td>Return the day of the month (1-31)</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_dayofweek"><code class="literal">DAYOFWEEK()</code></a></td>
<td>Return the weekday index of the argument</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_dayofyear"><code class="literal">DAYOFYEAR()</code></a></td>
<td>Return the day of the year (1-366)</td>
</tr>

<tr>
<td><a href="date-and-time-functions.html#function_extract"><code class="literal">EXTRACT</code></a></td>
<td>Extract part of a date</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_from-days"><code class="literal">FROM_DAYS()</code></a></td>
<td>Convert a day number to a date</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_from-unixtime"><code class="literal">FROM_UNIXTIME()</code></a></td>
<td>Format date as a UNIX timestamp</td>

</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_get-format"><code class="literal">GET_FORMAT()</code></a>(v4.1.1)</td>
<td>Return a date format string</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_hour"><code class="literal">HOUR()</code></a></td>
<td>Extract the hour</td>
</tr>
<tr>
<td>

<a href="date-and-time-functions.html#function_last-day"><code class="literal">LAST_DAY</code></a>(v4.1.1)</td>
<td>Return the last day of the month for the argument</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_localtime"><code class="literal">LOCALTIME()</code>, <code class="literal">LOCALTIME</code></a></td>
<td>Synonym for NOW()</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_localtimestamp"><code class="literal">LOCALTIMESTAMP</code>, <code class="literal">LOCALTIMESTAMP()</code></a>(v4.0.6)</td>

<td>Synonym for NOW()</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_makedate"><code class="literal">MAKEDATE()</code></a>(v4.1.1)</td>
<td>Create a date from the year and day of year</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_maketime"><code class="literal">MAKETIME</code></a>(v4.1.1)</td>
<td>MAKETIME()</td>

</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_microsecond"><code class="literal">MICROSECOND()</code></a>(v4.1.1)</td>
<td>Return the microseconds from argument</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_minute"><code class="literal">MINUTE()</code></a></td>
<td>Return the minute from the argument</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_month"><code class="literal">MONTH()</code></a></td>

<td>Return the month from the date passed</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_monthname"><code class="literal">MONTHNAME()</code></a>(v4.1.21)</td>
<td>Return the name of the month</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_now"><code class="literal">NOW()</code></a></td>
<td>Return the current date and time</td>
</tr>

<tr>
<td><a href="date-and-time-functions.html#function_period-add"><code class="literal">PERIOD_ADD()</code></a></td>
<td>Add a period to a year-month</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_period-diff"><code class="literal">PERIOD_DIFF()</code></a></td>
<td>Return the number of months between periods</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_quarter"><code class="literal">QUARTER()</code></a></td>
<td>Return the quarter from a date argument</td>

</tr>
<tr>
<td><a href="date-and-time-functions.html#function_sec-to-time"><code class="literal">SEC_TO_TIME()</code></a></td>
<td>Converts seconds to 'HH:MM:SS' format</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_second"><code class="literal">SECOND()</code></a></td>
<td>Return the second (0-59)</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_str-to-date"><code class="literal">STR_TO_DATE()</code></a>(v4.1.1)</td>

<td>Convert a string to a date</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_subdate"><code class="literal">SUBDATE()</code></a></td>
<td>When invoked with three arguments a synonym for DATE_SUB()</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_subtime"><code class="literal">SUBTIME()</code></a>(v4.1.1)</td>
<td>Subtract times</td>
</tr>

<tr>
<td><a href="date-and-time-functions.html#function_sysdate"><code class="literal">SYSDATE()</code></a></td>
<td>Return the time at which the function executes</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_time-format"><code class="literal">TIME_FORMAT()</code></a></td>
<td>Format as time</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_time-to-sec"><code class="literal">TIME_TO_SEC()</code></a></td>
<td>Return the argument converted to seconds</td>

</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_time"><code class="literal">TIME()</code></a>(v4.1.1)</td>
<td>Extract the time portion of the expression passed</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_timediff"><code class="literal">TIMEDIFF()</code></a>(v4.1.1)</td>
<td>Subtract time</td>
</tr>

<tr>
<td>
<a href="date-and-time-functions.html#function_timestamp"><code class="literal">TIMESTAMP()</code></a>(v4.1.1)</td>
<td>With a single argument, this function returns the date or  datetime expression. With two arguments, the sum of the arguments</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_timestampadd"><code class="literal">TIMESTAMPADD()</code></a>(v5.0.0)</td>
<td>Add an interval to a datetime expression</td>
</tr>
<tr>

<td>
<a href="date-and-time-functions.html#function_timestampdiff"><code class="literal">TIMESTAMPDIFF()</code></a>(v5.0.0)</td>
<td>Subtract an interval from a datetime expression</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_to-days"><code class="literal">TO_DAYS()</code></a></td>
<td>Return the date argument converted to days</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_unix-timestamp"><code class="literal">UNIX_TIMESTAMP()</code></a></td>
<td>Return a UNIX timestamp</td>

</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_utc-date"><code class="literal">UTC_DATE()</code></a>(v4.1.1)</td>
<td>Return the current UTC date</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_utc-time"><code class="literal">UTC_TIME()</code></a>(v4.1.1)</td>
<td>Return the current UTC time</td>
</tr>

<tr>
<td>
<a href="date-and-time-functions.html#function_utc-timestamp"><code class="literal">UTC_TIMESTAMP()</code></a>(v4.1.1)</td>
<td>Return the current UTC date and time</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_week"><code class="literal">WEEK()</code></a></td>
<td>Return the week number</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_weekday"><code class="literal">WEEKDAY()</code></a></td>

<td>Return the weekday index</td>
</tr>
<tr>
<td>
<a href="date-and-time-functions.html#function_weekofyear"><code class="literal">WEEKOFYEAR()</code></a>(v4.1.1)</td>
<td>Return the calendar week of the date (1-53)</td>
</tr>
<tr>
<td><a href="date-and-time-functions.html#function_year"><code class="literal">YEAR()</code></a></td>
<td>Return the year</td>
</tr>

<tr>
<td><a href="date-and-time-functions.html#function_yearweek"><code class="literal">YEARWEEK()</code></a></td>
<td>Return the year and week</td>
</tr>
</tbody>
</table>