May 312011
 
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)

Continue reading »

Aug 242010
 

Recently a colleague sent me a quick instant message asking if I knew of any way in Oracle to make a query run long enough to test a “cancel query” function he was writing in Java.  He had tried several queries that usually produced the results he was looking for, however there wasn’t enough data in his test environment to reproduce the wait time he needed.  I thought about it for a minute and then proposed the following solution.

Oracle provides the DBMS_LOCK package which allows users to manage their own locks.  One handy subprogram in this package in the SLEEP method, which suspends the current session for the specified number of seconds.  I have used DBMS_LOCK.SLEEP a number of times in the past so I knew this would do the trick. Continue reading »

Feb 022010
 

Ever been working with an application window and wondered if it would fit into a particular screen resolution?  I have.  I am continually testing our apps in different browser windows and periodically I check to make sure we are still within the maximum screen resolution as indicated in our requirements documents.  I had been using this nifty little Firefox plugin which did the job for me.  But, I don’t have that capability yet (that I know of) with Google Chrome.  And, what if I happen to be testing a non-browser-based application?  Well, I got tired of wondering and decided to do something about it. Continue reading »

 Posted by at 4:39 pm
Nov 112009
 

I thought I would post a whitepaper I wrote earlier this year under the auspices of my employer, M. Miller & Associates.  This whitepaper is basically a summary of things I have observed over the course of my career in IT, particularly in regard to database design.  I can assure you that I eat my own dog food when it comes to the things I discuss.  I’m in the trenches every day putting into practice these concepts in order to produce solid, forward thinking designs.  We’ll eventually post this on the company website, but until then I make it available here in hopes it provokes thought and discussion.  Let me know what you think.

OLAP Fiendly OLTP Design – Planning Ahead for the Inevitable Data Warehouse

Sep 112009
 

My current development environment has to be the best configuration I’ve had to date in my development career.  Here are some of the specifics:

  • HP EliteBook laptop (8730w)
  • 2.4GHz Core 2 Duo 64-bit
  • 6GB Memory
  • openSuse 11.1 64-bit

The move to Linux from Windows has been a huge plus, though not without its own challenges.  But it’s not just the hardware and OS.  Here are the tools that round out a highly productive development environment: Continue reading »