May
31
2011
31
2011
Calculating Age Based on Date of Birth in SQLite
An article by 
On a recent project I needed to calculate a person’s age relative to the system date based on an input value of a birth date. In this particular scenario I needed to do so using the SQLite engine, and the specific requirement was to calculate age using the following logic:
If person is younger than 1 month, present the age in days (e.g. 30 days)
If person is 1 month or older but younger than 2 months, present the age in weeks (e.g. 8 wks)
If person is 2 months or older but younger than one year, present the age in months (e.g. 3 mos)
If person is 1 year or older, present the age in years (e.g. 2 yrs)
My initial thought was obviously <i>not</i> to reinvent the wheel, so I did what any logical database guy would do – I consulted the SQLite documentation for what I expected to be a plethora of date-specific SQL functions I could leverage. After all, other RDBMS implementations offer direct support for the functionality I needed. PostgreSQL, for example offers the age function (http://www.postgresql.org/docs/9.0/static/functions-datetime.html).
If you are familiar at all with SQLite you have probably been faced with the interesting implementation of dynamic data types. One example of this is the fact that there is no native “date” data type. One nuance that stems from this implementation is the seemingly limited set of options when it comes to date functions – at least upon initial observation.
I found a number of posts (like this one http://stackoverflow.com/questions/3123951/sqlite-how-to-calculate-age-from-birth-date) that discussed calculating such date values using the built-in SQLite functions, however they fell short in getting to the granularity I needed. Other posts (such as this one http://www.mail-archive.com/sqlite-users@sqlite.org/msg20531.html) suggested writing my own custom SQLite function in C. This was not an option since my particular requirement relied on using the SQLite implementation inside the context of the Adobe Flash player. So, I was ultimately left to fend for myself, and this kind of challenge is what developers sometimes need to stoke their initiative and get their creative wheels turning. It definitely worked for me, not that I needed the motivation 
Before I started, I knew that I would need a decent set of data against which to test my SQL. As I typically tend to do for such needs, I fired up Microsoft Excel and generated a list of dates along with their corresponding years/months/days calculations using basic Excel functions. I then turned the resulting Excel data into SQL INSERT statements and loaded them into a SQLite table named AGE_TEST for testing. Here’s an excerpt of the Excel data, based on my local system date of 5/31/2011:
</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">DOB Years Months Days</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">9/11/05 5 8 20</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">1/1/10 1 4 30</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">1/2/10 1 4 29</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">...</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">5/29/11 0 0 2</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">5/30/11 0 0 1</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">5/31/11 0 0 0</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">
And here are the respective Excel formulae I used:
</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">Years: =DATEDIF(A2,TODAY(),"Y")</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">Months: =DATEDIF(A7,TODAY(),"YM")</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">Days: =DATEDIF(A7,TODAY(),"MD")</div> <div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">
After a bit of trial and error, I finally ended up with something that worked across all of my test dates. Admittedly (as you can probably tell from the code), the months calculation was the most challenging. Here’s the SQL solution I ended up with:
</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">SELECT CASE</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">/* DOB is beyond current date */</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN dob > date('now') THEN NULL</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">/* DOB equal to current date */</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN dob = date('now') THEN '0 days'</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">/* subject is less that 1 month old */</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-1 month') < dob THEN CAST((julianday('now') - julianday(dob)) AS INTEGER) || ' days'</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">/* subject is less that 2 months old */</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-2 month') < dob THEN CAST((julianday('now') - julianday(dob))/7 AS INTEGER) || ' wks'</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">/* subject is less than 1 year old */</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-1 year') < dob THEN</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">CASE</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-11 month') >= dob then 11</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-10 month') >= dob then 10</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-9 month') >= dob then 9</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-8 month') >= dob then 8</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-7 month') >= dob then 7</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-6 month') >= dob then 6</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-5 month') >= dob then 5</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-4 month') >= dob then 4</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-3 month') >= dob then 3</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-2 month') >= dob then 2</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN date('now','-1 month') >= dob then 1</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">END || ' mos'</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">/* subject is 1 year or older */</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">ELSE ((strftime('%Y', 'now') - strftime('%Y', dob)) -</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">(CASE</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">WHEN (strftime('%m', dob) < strftime('%m', 'now')) OR</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">(strftime('%m', dob) = strftime('%m', 'now') AND</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">strftime('%d', dob) <= strftime('%d', 'now')) THEN 0</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">ELSE 1</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">END)) || ' yrs'</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">END AS person_age</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">FROM (SELECT dob</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">FROM age_test)</div>
<div id="_mcePaste" style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;">
On a recent project I needed to calculate a person’s age relative to the system date based on an input value of a birth date. In this particular scenario I needed to do so using the SQLite engine, and the specific requirement was to calculate age using the following logic:
- If person is younger than 1 month, present the age in days (e.g. 30 days)
- If person is 1 month or older but younger than 2 months, present the age in weeks (e.g. 8 wks)
- If person is 2 months or older but younger than one year, present the age in months (e.g. 3 mos)
- If person is 1 year or older, present the age in years (e.g. 2 yrs)
My initial thought was obviously to not reinvent the wheel, so I did what any logical database guy would do – I consulted the SQLite documentation for what I expected to be a plethora of date-specific SQL functions I could leverage. After all, other RDBMS implementations offer direct support for the functionality I needed. PostgreSQL, for example offers the age function.
If you are familiar at all with SQLite you have probably been faced with the interesting implementation of dynamic data types. One example of this is the fact that there is no native “date” data type. One nuance that stems from this implementation is the seemingly limited set of options when it comes to date functions – at least upon initial observation.
I found a number of posts (like this one) that discussed calculating such date values using the built-in SQLite functions, however they fell short in getting to the granularity I needed. Other posts (such as this one) suggested writing my own custom SQLite function in C. This was not an option since my particular requirement relied on using the SQLite implementation inside the context of the Adobe Flash player. So, I was ultimately left to fend for myself, and this kind of challenge is what developers sometimes need to stoke their initiative and get their creative wheels turning. It definitely worked for me, not that I needed the motivation
Before I started, I knew that I would need a decent set of data against which to test my SQL. As I typically tend to do for such needs, I fired up Microsoft Excel and generated a list of dates along with their corresponding years/months/days calculations using basic Excel functions. I then turned the resulting Excel data into SQL INSERT statements and loaded them into a SQLite table named AGE_TEST for testing. Here’s an excerpt of the Excel data, based on my local system date of 5/31/2011:
DOB Years Months Days 9/11/05 5 8 20 1/1/10 1 4 30 1/2/10 1 4 29 ... 5/29/11 0 0 2 5/30/11 0 0 1 5/31/11 0 0 0
And here are the respective Excel formulae I used:
Years: =DATEDIF(A2,TODAY(),"Y") Months: =DATEDIF(A2,TODAY(),"YM") Days: =DATEDIF(A2,TODAY(),"MD")
After a bit of trial and error, I finally ended up with something that worked across all of my test dates. Admittedly (as you can probably tell from the code), the months calculation was the most challenging. Here’s the SQL solution I ended up with:
SELECT CASE
/* DOB is beyond current date */
WHEN dob > date('now') THEN NULL
/* DOB equal to current date */
WHEN dob = date('now') THEN '0 days'
/* subject is less that 1 month old */
WHEN date('now','-1 month') < dob THEN CAST((julianday('now') - julianday(dob)) AS INTEGER) || ' days'
/* subject is less that 2 months old */
WHEN date('now','-2 month') < dob THEN CAST((julianday('now') - julianday(dob))/7 AS INTEGER) || ' wks'
/* subject is less than 1 year old */
WHEN date('now','-1 year') < dob THEN
CASE
WHEN date('now','-11 month') >= dob then 11
WHEN date('now','-10 month') >= dob then 10
WHEN date('now','-9 month') >= dob then 9
WHEN date('now','-8 month') >= dob then 8
WHEN date('now','-7 month') >= dob then 7
WHEN date('now','-6 month') >= dob then 6
WHEN date('now','-5 month') >= dob then 5
WHEN date('now','-4 month') >= dob then 4
WHEN date('now','-3 month') >= dob then 3
WHEN date('now','-2 month') >= dob then 2
WHEN date('now','-1 month') >= dob then 1
END || ' mos'
/* subject is 1 year or older */
ELSE ((strftime('%Y', 'now') - strftime('%Y', dob)) -
(CASE
WHEN (strftime('%m', dob) < strftime('%m', 'now')) OR
(strftime('%m', dob) = strftime('%m', 'now') AND
strftime('%d', dob) <= strftime('%d', 'now')) THEN 0
ELSE 1
END)) || ' yrs'
END AS person_age
FROM (SELECT dob
FROM age_test)
I’d love to hear your thoughts.
Leave a comment
Apache Ant
Bible
Business Intelligence
Data Warehousing
Linux
Mac OS/X
openSuse
Oracle
Oyen
SQL
SQLite
Subversion
UEX
VMWare Data Warehousing (2)
Development (8)
Eternal (3)
iPhone (1)
Uncategorized (1)
Wasabi-SIG (1)
WP Cumulus Flash tag cloud by Roy Tanck requires Flash Player 9 or better.