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.


DATETIMEYYYY-MM-DD HH:MM:SS
DATEYYYY-MM-DD
TIMESTAMPYYYYMMDDHHSSMM
TIMEHH:MM:SS
YEARYYYY


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 | +-------------------------+



Calculating age

One of the most commonly performed date calculations is one which calculates age. Unfortunately there isn't a function to do it, so you need to do a bit of thinking. Let's begin with doing some simple arithmetic - you can see when I'm running these queries from the earlier results (31 March 2003). First, we'll simply subtract the year of birth from the current year. If you're reading this article well after I wrote it, of course your results may be different:


mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-01-01');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-01-01') |
+---------------------------------------+
|                                    32 |
+---------------------------------------+


If you were using this to return the age, the result would be correct. Someone born on the 1st of January 1971 would be 32 years old on the 31st of March 2003. But let's test for someone born on the 31st of December:


mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-12-31');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-12-31') |
+---------------------------------------+
|                                    32 |
+---------------------------------------+


Here the age is wrong. This person would not have turned 32 yet. The problem is that the calculation above only checks the year part of the date. It comes down to subtracting 1971 from 2003, regardless of the month and day. The best way to do this is to compare whether the current month and day are larger than the birth month and day. If it is, a full year has passed, and the year portion of the calculation can be left. If it isn't, a full year hasn't passed, and you need to subtract one from the year portion. It sounds tricky, but it isn't really. Let's break it down slowly. It helps us greatly that MySQL evaluates a true expression to 1, and a false expression to 0. For example:


mysql> SELECT 23>19;
+-------+
| 23>19 |
+-------+
|     1 |
+-------+

mysql> SELECT 23<19; +-------+ | 23<19 | +-------+ | 0 | +-------+



23 is greater than 19(true), so MySQL returns '1', while 23 is not less than 19(false), so MySQL returns '0'. Now we need a way to return the 'MM-DD' portion of the date. Fortunately there's a string function that comes in handy, RIGHT(). This returns a certain number of characters from a supplied string, starting from the right. For example:


mysql> SELECT RIGHT('abcdef',2);
+-------------------+
| RIGHT('abcdef',2) |
+-------------------+
| ef                |
+-------------------+


This returns 2 characters, starting from the right of the string, thus 'ef'. If you ask for more characters than the string is long, MySQL simply returns the whole string:


mysql> SELECT RIGHT('abcdef',9);
+-------------------+
| RIGHT('abcdef',9) |
+-------------------+
| abcdef            |
+-------------------+


So, to return the 'MM-DD' portion of a date, you need to return the five rightmost characters, for example:


mysql> SELECT RIGHT(CURRENT_DATE(),5);
+-------------------------+
| RIGHT(CURRENT_DATE(),5) |
+-------------------------+
| 03-31                   |
+-------------------------+


Now we have everything we need to calculate an age. The query will have a portion that calculates the difference in years, and then we'll subtract either 0 or 1, depending on the month-day portion. Here is the full query:


mysql> SELECT YEAR(CURRENT_DATE()) - YEAR('1971-12-31') 
- (RIGHT(CURRENT_DATE(),5)<'12-31') AS age;
+------+
| age  |
+------+
|   31 |
+------+


Let's create a small table with some test data, so we can try out our query as you'd be more likely to use it - when returning data from a table:
CREATE TABLE friends(name VARCHAR(20),birthday DATE);
INSERT INTO friends(name,birthday) VALUES('mo','1971-12-31');
INSERT INTO friends(name,birthday) VALUES('jo','1971-01-01');

And now the full query:


mysql> SELECT name,YEAR(CURRENT_DATE()) - YEAR(birthday) - 
(RIGHT(CURRENT_DATE(),5)<RIGHT(birthday,5)) AS age FROM friends;
+------+------+
| name | age  |
+------+------+
| mo   |   31 |
| jo   |   32 |
+------+------+


The only extra complexity here is that we need to use the RIGHT() function to return the month-day portion from the birthday, and can't just hardcode it as before.


In the next installment, we'll look at some of the other MySQL date and time functions that can help make your life a lot easier. There are quite a few of them, and we've barely scratched the surface so far.


This article originally appeared on DatabaseJournal.com.