Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 2, 2003

MySQL date and time functions, Part 1 - Page 2

By Ian Gilfillan

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

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM