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.
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.
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:
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:
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:
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:
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:
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:
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.