MySQL date and time functions, Part 1

MySQL Date and Time Types

There are a number of useful date and time functions in MySQL. I’ve seen too many applications performing date calculations at the code level when the same can be done using built-in MySQL functions. Before we launch into the functions, however, let’s refresh our memory and look at which date and time types are available to MySQL.

DATETIME YYYY-MM-DD HH:MM:SS
DATE YYYY-MM-DD
TIMESTAMP YYYYMMDDHHSSMM
TIME HH:MM:SS
YEAR YYYY

The TIMESTAMP column stores the full 14 characters, but you can display it in different ways. If you define the column as TIMESTAMP(2), for example, only the two-digit year will be displayed, but the full value is stored. If you later decide to display the full value, you can change the table definition, and the full value will appear. Below is a list of various ways to define a TIMESTAMP, and the resultant display.

TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

MySQL is quite lenient in how it reads date formats. Although it is wise to use the convention, you can use any other punctuation character you like. For example, if you created the following table:

CREATE TABLE time_table(dt DATETIME);

Instead of inserting a record using the convention, as follows:

INSERT INTO time_table(dt) VALUES(‘2003-03-31 11:22:12’)

you could use ‘=’ and ‘+’ signs, as follows:

INSERT INTO time_table(dt) VALUES(‘2003=03=31 11+22+12’)

But although I’m sure they exist, I haven’t come across a good reason to use this, so I suggest you keep to the conventions unless absolutely necessary.

Simple Date Calculations

Date calculations are relatively easy. The first function we’re going to look at is the YEAR() function, which returns a year from a given date. For example:

mysql> SELECT YEAR('2003-03-31');
+--------------------+
| YEAR('2003-03-31') |
+--------------------+
|               2003 |
+--------------------+

We can perform simple arithmetic on a date using the ‘+’ and ‘-‘ operators. For example, to find out which year is five years ahead of a given date, you can use:

mysql> SELECT YEAR('2003-03-31')+5;
+----------------------+
| YEAR('2003-03-31')+5 |
+----------------------+
|                 2008 |
+----------------------+

And to find out which year was five years in the past:

mysql> SELECT YEAR('2003-03-31')-5;
+----------------------+
| YEAR('2003-03-31')-5 |
+----------------------+
|                 1998 |
+----------------------+

Of course you don’t have to hard-code the date. MySQL is quite capable of telling the date and time, using the NOW() function:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2003-03-31 00:32:21 |
+---------------------+

or just the date with the CURRENT_DATE() function:

mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2003-03-31     |
+----------------+

There are also functions for the other date and time intervals; MONTH(), DAYOFMONTH(), HOUR(), MINUTE() and SECOND(). For example:

mysql> SELECT MONTH(NOW()) AS m,
DAYOFMONTH(NOW()) AS d,
HOUR(NOW()) AS h,
MINUTE(NOW()) AS m,
SECOND(NOW()) AS s;
+------+------+------+------+------+
| m    | d    | h    | m    | s    |
+------+------+------+------+------+
|    3 |   31 |    1 |   53 |   38 |
+------+------+------+------+------+

The DAYOFMONTH() function is an exception to the naming conventions because there are a number of other ways to return the day. DAYOFMONTH() returns the day as a numeric from 1 to 31, but there is also DAYNAME() which returns the actual name of the day, DAYOFWEEK() which returns a number from 1 (Sunday) to 7(Saturday) and DAYOFYEAR() returning a number from 1 to 366. Some examples:

mysql> SELECT DAYNAME('2000-01-01');
+-----------------------+
| DAYNAME('2000-01-01') |
+-----------------------+
| Saturday              |
+-----------------------+
mysql> SELECT DAYOFWEEK('2000-01-01');
+-------------------------+
| DAYOFWEEK('2000-01-01') |
+-------------------------+
|                       7 |
+-------------------------+
mysql> SELECT DAYOFYEAR('2000-12-31');
+-------------------------+
| DAYOFYEAR('2000-12-31') |
+-------------------------+
|                     366 |
+-------------------------+

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles