MySQL Date and Time Functions, Part 1 Page 2

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.