Mar '13

An Alternative Way to Manage Dates/Times in MySQL

Generally speaking dates and times are managed in MySQL (and other SQL engines) in one of two ways; either a ‘YY-MM-DD HH:MM:SS’ string or a Unix timestamp which contains seconds since the Unix epoch – midnight on 1/1/1970.

The first option is more readable, yet there is more overhead in date comparison logic – both the coding and the internal translation and calculation involved. The second is faster to process but, unless you’re some sort of savant, you can’t read it yourself.

So the alternative I use is a BIGINT field containing a single number generated as YYYYMMDDHHMMSS. It is readable, it is lightweight in terms of comparison logic and serves me very well in a number of applications. It’s also just as easy to generate and work with in PHP:

$date1 = date('YmdHis');
$date2 = date('YmdHis', mktime($an_hour, $a_minute, $a_second, $a_month, $a_day, $a_year));
if($date1 > $date2)
    echo 'Whoop!';

Leave a Reply