SQLite dates and JavaScript formatting

If I were to guess, I'd say that at least 50% of all database tables I create for any system contain a timestamp field. I think anyone could agree that this is a pretty commonplace. Given the importance and frequency of date operations, I'm a little surprised that JavaScript doesn't natively handle them a little better.

Don't get me wrong, JavaScript is one of my favorite languages, and I don't mean to put it down. But, if you've logged any time developing in a language like ColdFusion, you're used to ridiculously simple date operations. In ColdFusion, I could use the following and expect a decent result:

<cfoutput>#DateFormat(now(), "give me a nice looking date")#</cfoutput>

Okay, a slight exaggeration, but it gets the point across. Not to say date/time formatting is impossible in JavaScript, far from it. Thanks to the pervasiveness of JavaScript, we are able to find a function for just about anything on the web.

The inspiration for this post came from my dealings with the SQLite database, which as anyone would expect, doesn't measure up to the major players like MySQL and Oracle. But how could it, it runs in a browser. And with browsers sending out new releases every 5 minutes (FireFox, yes, I'm talking about you), we can also expect changes to the SQLite engine as a much faster rate than we're used to. In my case, it was the use of timestamp fields which I struggled with.

Many posts throughout the web suggested the use of a syntax similar to:

CREATE TABLE IF NOT EXISTS mytable (
   my_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   my_field1 VARCHAR NOT NULL
   my_created DATETIME DEFAULT (datetime('now', 'localtime')));


While syntactically correct, and did not throw any run-time errors, the above code didn't actually store a date/time value, but rather just null.

Some further searching brings me to newer posts about the subject, which indicate that in newer versions of SQLite, the preferred syntax is:

CREATE TABLE IF NOT EXISTS mytable (
   my_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
   my_field1 VARCHAR NOT NULL
   my_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

This worked beautifully, and stored the dates in the ODBC date format that I've grown to love over the years (YYYY-MM-DD HH:MM:SS). But, wouldn't it figure that this format can't be parsed by JavaScript!

After a brief period of annoyance, I did some more searching, and found the following function which will convert a date from this UTC format (yes, I'm so old school that I call it ODBC format) to one that can be parse by JavaScript.

function dateFromUTC( dateAsString, ymdDelimiter ) {
    var pattern = new RegExp( "(\\d{4})" + ymdDelimiter + "(\\d{2})" + ymdDelimiter + "(\\d{2}) (\\d{2}):(\\d{2}):(\\d{2})" );
    var parts = dateAsString.match( pattern );

    return new Date( Date.UTC(
      parseInt( parts[1] )
    , parseInt( parts[2], 10 ) - 1
    , parseInt( parts[3], 10 )
    , parseInt( parts[4], 10 )
    , parseInt( parts[5], 10 )
    , parseInt( parts[6], 10 )
    , 0
    ));
}


I apologize to the fella who wrote this code, I can't seem to locate your post. The best I can do is not claim this as my own!

As a bonus, here's another piece of code which I, again, pulled from the web, but modified for our SQLite timestamp related needs. It will display a human readable date as a function of the current date/time (i.e. 5 minutes ago).

function prettyDate(time){
    if(!time)
        return '';
       
    var date = new Date( dateFromUTC(time, '-') ),
        diff = (((new Date()).getTime() - date.getTime()) / 1000),
        day_diff = Math.floor(diff / 86400);
           
    if ( isNaN(day_diff) || day_diff < 0)
        return '';
           
    return day_diff == 0 && (
            diff < 60 && "just now" ||
            diff < 120 && "1 minute ago" ||
            diff < 3600 && Math.floor( diff / 60 ) + " minutes ago" ||
            diff < 7200 && "1 hour ago" ||
            diff < 86400 && Math.floor( diff / 3600 ) + " hours ago") ||
        day_diff == 1 && "yesterday" ||
        day_diff < 7 && day_diff + " days ago" ||
        day_diff < 60 && Math.ceil( day_diff / 7 ) + " weeks ago" ||
        (day_diff >= 60 && day_diff < 600) && Math.ceil(day_diff / 30) + ' months ago' ||
        day_diff >= 600 && Math.ceil(day_diff / 365) + ' years ago';
}