Date/Time Functions In MySQL

Looking For A Date?

As an industry-standard database, MySQL comes with a variety of different data types, including types for integers, floating-point numbers, strings, date and time values, and data collections. Users looking for temporal types are, however, particularly spoilt for choice, because MySQL includes such a wide variety of date and time types that it’s sure to handle anything you throw at it. Just to give you an idea of its versatility - you can store a timestamp with the TIMESTAMP type, a single date or time with the DATE and TIME types, a combination of both dates and times with the hybrid DATETIME type, or just a year value with the YEAR type.

Of course, data types, by themselves are only one piece of the puzzle; in order to do something with them, you need functions. And MySQL scores high here as well, providing over 40 built-in functions to process and manipulate date and time values. This date and time API includes functions to obtain the current date and time, to perform sophisticated date arithmetic, to extract the different components of a timestamp, to rejigger a time value into different formats, to convert between different dates and times, and much, much more. Flip the page, and let me show you how.

The Right Type

In the MySQL world, a date and/or time value can appear in either of two formats: the machine-readable kind, like this,

20040119122635

or the human-readable kind, like this:

"2004-01-19 12:26:35"

MySQL accepts both these formats - however, you must remember to enclose the second type of value in quotes whenever you use it, as it’s technically a string.

Now, in order to use a temporal value in a MySQL table, the corresponding field must be declared as a date/time type. This might sound simple, but it’s not - you see, MySQL supports not one, not two, but rather five different date/time data types, ranging from simple types that only hold a date or time value to more complex hybrids containing hour, minute, day, month and year information.

• The DATE type is good for storing date values which do not contain a time component. A 3-byte type, it can store values in the range 1000-01-01 to 9999-12-31.

• The TIME type is the opposite, allowing you to store time values without a date component. This is useful for storing both times and durations, and also uses 3 bytes. Its allowed range of values is -838:59:59 to 838:59:59.

• The YEAR type is only useful if you’re looking to store the year component of a date. It accepts both 4- and 2-digit values in the range 1901 to 2155, and uses 1 byte of memory (for year values outside this range, consider using an INT type instead).

Consider the following example, which demonstrates these three types in action:

mysql> CREATE TABLE dummy (d DATE, t TIME, y YEAR);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO dummy (d, t, y) VALUES (20030908, "11:12:13", 1978);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO dummy (d, t, y) VALUES ("2003-11-10", 231000, 00);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM dummy;
+------------+----------+------+
| d          | t        | y    |
+------------+----------+------+
| 2003-09-08 | 11:12:13 | 1978 |
| 2003-11-10 | 23:10:00 | 0000 |
+------------+----------+------+
2 rows in set (0.00 sec)

• If you need to specify the date and time together, consider using the DATETIME type, which occupies 8 bytes of memory and accepts values in the range 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Here’s an example:

mysql> CREATE TABLE dummy (dt DATETIME);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO dummy (dt) VALUES ("2004-06-04 09:15");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dummy (dt) VALUES (20040718061728);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM dummy;
+---------------------+
| dt                  |
+---------------------+
| 2004-06-04 09:15:00 |
| 2004-07-18 06:17:28 |
+---------------------+
2 rows in set (0.00 sec)

• Finally, one of MySQL’s most interesting date/time types is the TIMESTAMP type, used to store…yup, timestamps. Similar to the DATETIME type, this one too allows you to store date and time information in a single field. The difference lies in the fact that MySQL automatically fills the first TIMESTAMP field in a row with the current date and time when the corresponding record is created or updated. Here’s an example:

mysql> CREATE TABLE dummy (v VARCHAR(20), ts TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO dummy (v) VALUES ('Shazam!');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dummy;
+---------+----------------+
| v       | ts             |
+---------+----------------+
| Shazam! | 20040119205317 |
+---------+----------------+
1 row in set (0.00 sec)

Note that you can also set a TIMESTAMP field to the current date and time by inserting a NULL value into it. Take a look:

mysql> CREATE TABLE dummy (ts TIMESTAMP);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO dummy (ts) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM dummy;
+----------------+
| ts             |
+----------------+
| 20040119205220 |
+----------------+
1 row in set (0.00 sec)

Notice also the difference in how MySQL displays DATETIME and TIMESTAMP values - the former are displayed in human-readable format, while the latter are displayed as numeric machine-readable values without delimiters or spaces.

Oh yeah - just in case you were wondering, MySQL is completely Y2K-compliant, although you should still read the information at http://www.mysql.com/doc/en/Y2K_issues.html in case your application uses 2-digit year values.

The Time Is NOW()

A basic requirement for many SQL queries is the ability to retrieve the current date and time. MySQL provides a simple, no-nonsense function for this requirement, aptly called NOW(), that returns the relevant information. By default, this function will return the date in the “YYYY-MM-DD HH:MM:SS” format.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2004-01-19 12:14:59 |
+---------------------+
1 row in set (0.01 sec)

While the above format is human-friendly, it is ill-suited to *NIX boxes used to dealing with date and time value in the YYYYMMDDHHMMSS format. But hey, MySQL is no ordinary database - the RDBMS is smart enough to automatically use this second format when the function is invoked in a mathematical context, as shown below.

mysql> SELECT NOW(), NOW() + 5;
+---------------------+----------------+
| NOW()               | NOW() + 5      |
+---------------------+----------------+
| 2004-01-18 12:26:30 | 20040119122635 |
+---------------------+----------------+
1 row in set (0.00 sec)

The only drawback of the NOW() function is that it returns both date and time components in a single value. If you need either one of the two values, you could resort to the CURRENT_DATE() or CURDATE() functions for the current date,

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2004-01-18     |
+----------------+
1 row in set (0.02 sec)

and the CURRENT_TIME() or CURTIME() functions for the current time.

mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 12:32:33       |
+----------------+
1 row in set (0.00 sec)

Just like the NOW() function, the above functions will also return the values in machine-friendly YYYYMMDD format (for dates) or HHMMSS format (for times) if used in a numerical context.

Breaking It Down

What if you need to drill down even further, to the specific components that make up a date or time value? Well, MySQL comes with an entire family of functions designed to extract each component of a timestamp separately.

The YEAR() function returns the year component of a date value,

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2004 |
+-------------+
1 row in set (0.00 sec)

while the MONTH() function returns the month component.

mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

The day of the month can be obtained via the DAYOFMONTH() function,

mysql> SELECT DAYOFMONTH(NOW());
+-------------------+
| DAYOFMONTH(NOW()) |
+-------------------+
|                18 |
+-------------------+
1 row in set (0.00 sec)

The hour component of a time value can be extracted with the HOUR() function,

mysql> SELECT HOUR(NOW());
+-------------+
| HOUR(NOW()) |
+-------------+
|          12 |
+-------------+
1 row in set (0.00 sec)

while the MINUTE() and SECOND() functions can be used to extract the minute and second components respectively:

mysql> SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
|            52 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT SECOND(NOW());
+---------------+
| SECOND(NOW()) |
+---------------+
|            41 |
+---------------+
1 row in set (0.00 sec)

You can use the DAYNAME() function to obtain the current day of the week, as shown below:

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Sunday         |
+----------------+
1 row in set (0.00 sec)

This function will also work with any date that you pass it - look at the next example, which returns the day of the week corresponding to the date July 4 1776:

mysql> SELECT DAYNAME("1776-07-04");
+-----------------------+
| DAYNAME("1776-07-04") |
+-----------------------+
| Thursday              |
+-----------------------+
1 row in set (0.00 sec)

Similarly, there’s also a MONTHNAME() function,

mysql> SELECT MONTHNAME("1776-07-04");
+-------------------------+
| MONTHNAME("1776-07-04") |
+-------------------------+
| July                    |
+-------------------------+
1 row in set (0.00 sec)

and a DAYOFYEAR() function, which returns the day number corresponding to the specified date:

mysql> SELECT DAYOFYEAR("1977-07-18");
+-------------------------+
| DAYOFYEAR("1977-07-18") |
+-------------------------+
|                     199 |
+-------------------------+
1 row in set (0.00 sec)

The QUARTER() function returns a number between 1 and 4 to indicate which quarter the specified date belongs to:

mysql> SELECT QUARTER("1993-05-12");
+-----------------------+
| QUARTER("1993-05-12") |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

Note that this is a calendar quarter, not a financial quarter, so if you use this to generate financial reports, you might need to tweak it a little.

Number Games

When dealing with temporal data, one of the more common (and complex) tasks involves performing addition and subtraction operations on date and time values. Consider, for example, the simple task of calculating a date 63 days hence. Usually, in order to do this with any degree of precision, you need to factor in a number of different variables: the month you’re in, the number of days in that month, the number of days in the months following, whether or not the current year is a leap year, and so on. MySQL comes with several functions designed specifically to perform such calculations.

The first function in the list, the TO_DAYS() function, returns a number corresponding to a specific date. This number is calculated as the number of days elapsed between year 0 and the specified date. Consider the following examples, which illustrate:

mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
|         731963 |
+----------------+
1 row in set (0.00 sec)

As with the earlier set of functions, you can provide a custom date to this function in either string(“YYYY-MM-DD”) or numeric (YYYYMMDD) format as shown below:

mysql> SELECT TO_DAYS("1977-07-18");
+-----------------------+
| TO_DAYS("1977-07-18") |
+-----------------------+
|                722283 |
+-----------------------+
1 row in set (0.00 sec)

Or you could also do the following:

mysql> SELECT TO_DAYS("19770718");
+---------------------+
| TO_DAYS("19770718") |
+---------------------+
|              722283 |
+---------------------+
1 row in set (0.00 sec)

How about the opposite? Let’s say you want to know the date corresponding to a specified number of days from the year 0. Just use the FROM_DAYS() function:

mysql> SELECT FROM_DAYS(709993);
+-------------------+
| FROM_DAYS(709993) |
+-------------------+
| 1943-11-24        |
+-------------------+
1 row in set (0.00 sec)

You can even use these two functions together to test that it’s working as advertised (you skeptic, you!). Consider the following snippet, which illustrates:

mysql> SELECT FROM_DAYS(TO_DAYS("1947-08-15"));
+----------------------------------+
| FROM_DAYS(TO_DAYS("1947-08-15")) |
+----------------------------------+
| 1947-08-15                       |
+----------------------------------+
1 row in set (0.00 sec)

As you may have guessed, the TO_DAYS() and FROM_DAYS() functions make it very easy to execute the example alluded earlier in this article - adding 63 days to a date value and obtaining the resulting value - since they automatically adjust for the number of years in a specific month. Consider the following examples, which illustrate by adding 1 day to the last day of February in a leap and non-leap year:

mysql> SELECT FROM_DAYS(TO_DAYS("2000-02-28") + 1);
+--------------------------------------+
| FROM_DAYS(TO_DAYS("2000-02-28") + 1) |
+--------------------------------------+
| 2000-02-29                           |
+--------------------------------------+
1 row in set (0.00 sec)

Note that the output accounts for the fact that the year 2000 is a leap year. And now look at the second example, which returns the right date for a non-leap year:

mysql> SELECT FROM_DAYS(TO_DAYS("2001-02-28") + 1);
+--------------------------------------+
| FROM_DAYS(TO_DAYS("2001-02-28") + 1) |
+--------------------------------------+
| 2001-03-01                           |
+--------------------------------------+
1 row in set (0.00 sec)

Math Class

In addition to futzing with days and dates, MySQL also provides you with the ability to perform date arithmetic on specific date and time values, with its DATE_ADD() and DATE_SUB() functions. The syntax of these functions is somewhat more complex than the ones you’ve seen thus far - here’s what it looks like:

DATE_ADD(date,INTERVAL expr type)

DATE_SUB(date,INTERVAL expr type)

In order to better understand this, consider the following example, which adds 1 year to the current date and returns the new value:

mysql>  SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
+----------------------------------+
| DATE_ADD(NOW(), INTERVAL 1 YEAR) |
+----------------------------------+
| 2005-01-18 14:37:18              |
+----------------------------------+
1 row in set (0.01 sec)

That was pretty straightforward. Let’s take a more complex requirement - adding 5 days, 10 hours, 15 minutes and 13 seconds to 12:00 p.m. on the February 14 2004.

mysql> SELECT DATE_ADD("2004-02-14 12:00:00", INTERVAL "5 10:15:13" DAY_SECOND);

+-------------------------------------------------------------------+
| DATE_ADD("2004-02-14 12:00:00", INTERVAL "5 10:15:13" DAY_SECOND) |
+-------------------------------------------------------------------+
| 2004-02-19 22:15:13                                               |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

In order to better understand this, it’s necessary to delve a little deeper into the syntax of the DATE_ADD() and DATE_SUB() functions:

1. The first parameter, startDate, is a date or time value in either string (“YYYY-MM-DD HH:MM:SS”) or number (YYYYMMDDHHMMSS) format. This parameter specifies the date to use as a base for the calculation.

2. The second parameter consists of three separate components, which together specify the interval which is to be added to (or subtracted from) the first parameter. The components consist of the keyword INTERVAL, followed by the expression “expr” which is the interval period and the “type” - a keyword providing information on the interval calculation to be performed.

The relationship between the formatting of the interval period and the keyword following it is fixed, and is illustrated below.

The following examples should demonstrate how this works:

mysql>  SELECT DATE_ADD(19770718, INTERVAL 321 DAY);
+--------------------------------------+
| DATE_ADD(19770718, INTERVAL 321 DAY) |
+--------------------------------------+
| 1978-06-04                           |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_SUB('2005-12-13 03:30:00', INTERVAL "05:30" HOUR_MINUTE);
+---------------------------------------------------------------+
| DATE_SUB('2005-12-13 03:30:00', INTERVAL "05:30" HOUR_MINUTE) |
+---------------------------------------------------------------+
| 2005-12-12 22:00:00                                           |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2005-12-13', INTERVAL "5-2" YEAR_MONTH);
+---------------------------------------------------+
| DATE_ADD('2005-12-13', INTERVAL "5-2" YEAR_MONTH) |
+---------------------------------------------------+
| 2011-02-13                                        |
+---------------------------------------------------+
1 row in set (0.00 sec)

Note that if you use illegal date or time values, MySQL will still attempt to return a valid result by performing adjustments on the various values. This could result in unexpected output - as in the following example:

mysql> SELECT DATE_ADD("2005-11-31", INTERVAL 1 DAY);
+----------------------------------------+
| DATE_ADD("2005-11-31", INTERVAL 1 DAY) |
+----------------------------------------+
| 2005-12-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

The above example takes an illegal date - Nov 31 - and tries to add a day to it. MySQL tries to cover for your stupidity by correcting Nov 31 to Dec 1 and then adding a day.

Sitting Pretty

One of the most common challenges developers face when building database-backed applications involves formatting a date for display. In order to make this task a little easier, MySQL comes with a convenient DATE_FORMAT() function that allows you to manipulate the display of date values until they’re exactly the way you want them.

Here’s the general syntax for the DATE_FORMAT() function:

DATE_FORMAT(date, format)

This function will output the “date” according to the “format”. The “format” here is a string consisting of one or more of the following specifiers (this is an abbreviated list from the MySQL manual at http://www.mysql.com/doc/en/Date_and_time_functions.html, refer to the manual for the complete set of specifiers):

Here’s an example of how this could be used:

mysql> SELECT DATE_FORMAT(NOW(), "%W, %D %M %Y");
+------------------------------------+
| DATE_FORMAT(NOW(), "%W, %D %M %Y") |
+------------------------------------+
| Monday, 19th January 2004            |
+------------------------------------+
1 row in set (0.00 sec)

A quick glance at the table above, and you’ll see that this transformation has been accomplished simply by mapping the required output to the appropriate identifiers from the table.

Here are some more examples:

mysql> SELECT DATE_FORMAT("20031010121230", "%Y-%m-%e");
+-------------------------------------------+
| DATE_FORMAT("20031010121230", "%Y-%m-%e") |
+-------------------------------------------+
| 2003-10-10                                |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT("20031010121230", "%k:%i %p");
+-------------------------------------------+
| DATE_FORMAT("20031010121230", "%k:%i %p") |
+-------------------------------------------+
| 12:12 PM                                  |
+-------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT DATE_FORMAT("20011215101030", "%H%i hrs on %a %d %M %y");
+----------------------------------------------------------+
| DATE_FORMAT("20011215101030", "%H%i hrs on %a %d %M %y") |
+----------------------------------------------------------+
| 1010 hrs on Sat 15 December 01                           |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Log Out

And that’s about it for the date/time functions in MySQL. I started with a quick explanation of the different date and time data types in MySQL: the DATE, TIME and YEAR types, which allow you to input simple temporal values, and the DATETIME and TIMESTAMP types, which allow you to enter combined date/time values. In particular, I showed you how the TIMESTAMP type comes in handy if you need to automatically mark records with the time they were created or updated. Each of these types has its own pros and cons and it’s important to factor them in at the time of database design, because once you have created your database, it’s a tough task to keep changing the properties of your table columns.

Then, I moved on to the functions that help you manipulate these temporal values in the database. I started off with the NOW() function, which lets you retrieve the current date and time, and also showed you the CURRENT_DATE() and CURRENT_TIME() functions to get the current date and time as separate values. Next came the functions for extracting different components of a date or time value - I explored the YEAR(), MONTH(), HOUR(), MINUTE(), SECOND() and DAYOFMONTH() functions, and showed you how they can be used to retrieve specific bits of a date/time value for use in your scripts or application logic.

I then proceeded to MySQL’s date arithmetic functions. I showed you the FROM_DAYS() and TO_DAYS() functions, which allow you to calculate the number of days between two dates, and the DATE_ADD() and DATE_SUB() functions, which allow you to add and subtract date/time values. Finally, I closed things up with a description of the DATE_FORMAT() function that come in handy when formatting date/time values for display purposes.

And that’s about it. I hope you enjoyed this article, and that it offered you some insight into the date and time functions at your disposal in MySQL. Till next time…stay healthy!

Note: Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!