Handling Date with Google Gears

Suppose you execute the following DML statement in Google Gears:
db.execute(
"insert or replace into mytable "+
"(id, modified_on) values (?, ?)",
[null, new Date()]
);

This seemingly works but you are in for a surprise because Date is stored as Date.toString(). SQLite ducktying brain can’t infer that it’s a date datum. If you ran select query you will get string back.
Sat Jan 12 2008 02:34:58 GMT-0800 »
(Pacific Standard Time)

The downside of storing date as text that you can’t use any SQLite date time functions in your queries. Due to manifest typing, SQLite happily stores text in a date column. We would like to do better. We would like to store JavaScript Date as native SQLite Date without any loss of information. SQLite itself stores date in Julian Day representation while JavaScript cannonical date representation is number of milliseconds since 1 January 1970 00:00:00 UTC. So, we need to convert JavaScript Date to either Julian Day or text as understood by SQLite. Considering the ease of implementation, efficiency and lossless representation, YYYY-MM-DDTHH:MM:SS.FFF date format seems to fit the bill. This is ISO8601 format. However after some digging in SQLite code, it turns out that if it SQLite also works even if we don’t zero pad fields. So this simple JavaScript method with minor departure from ISO8601 format does the job well.
dateToSQLiteFormat = function(date) {
return date.getUTCFullYear()+
"-"+date.getUTCMonth() + 1+
"-"+date.getUTCDate()+
"T"+date.getUTCHours()+
":"+date.getUTCMinutes()+
":"+date.getUTCSeconds()+"."+
date.getUTCMilliseconds();
};

So far so good. How about converting SQLite dates to JavaScript Date object. This function does that trick.
dateSqlFragment = function(column, alias) {
return " ((strftime('%s', "+column+") -
strftime('%S', "+column+"))*1000 +
strftime('%f', "+column+")*1000) "+
(alias "");
};

var rs = db.execute(
“select id, “+dateSqlFragment(“modified_on”) +
” from mytable”);

Date date = new Date(rs.field(1));

Advertisements

One thought on “Handling Date with Google Gears

  1. Hello,I useddatetime(millisTill70, ‘unixepoch’)function to get the Julian day number in my Sqlite queries and saved the date in millis.In Java, I would go forString foo(){Calendar c = Calendar.getInstance().setTime(myDate);SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd’T’HH:mm:ss.SSS”);return sdf.format(c.getTime());}to convert them into ISO8601.Best,sacoskun

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: