Handle DateTime field in Gears

SQLite in Gears supports DATETIME data type, but it works differently from JavaScript context. SQLite only support the following formats as shown on SQLite Date and Time Functions. If you provide invalid format, SQLite will not give an error, but the function call (SQLite DateTime function) will return null, or record could not be inserted/updated. There are two ways to handle this situation.

1. If you don't use ORM JavaScript library such JazzRecord or JStORM, you could make this field an Integer field and call getTime() of JavaScript Date object to store. For displaying data back, you can still sort by ascending/descending order. Pass value that get from getTime() to a constructor of Date object to convert a Date object.

var db = google.gears.factory.create('beta.database');
db.open('database-test');
db.execute('create table if not exists Test (Phrase text, Timestamp int)');
//store in db
db.execute('insert into Test values (?, ?)', ['Monkey!', new Date().getTime()]);
var rs = db.execute('select * from Test order by Timestamp desc');

while (rs.isValidRow()) {
//convert to date object
   var date = new Date(rs.field(1));
   alert(rs.field(0) + '@' + date.getDate() + "/" + (date.getMonth()+1) + "/" + date.getFullYear());
   rs.next();
}
rs.close();

One exception with this is that you can use SQLite DateTime functions when querying back because the format you store is not recognized by SQLite. You have to loop through in JavaScript and an if statement.

2. This way is preferable because it supports full SQLite Date and Time Functions, but it needs some helper method to transform format between SQLite and JavaScript back and forth.


var Util = {
   fromJSDateToSQLiteDate: function(date) {
      var toTwoCharacters = function(number) {
         return (number.toString().length == 2) ? number.toString() : '0' + number.toString();
      };
      var strYear = date.getFullYear();
      var strMonth = toTwoCharacters(date.getMonth() + 1);
      var strDate = toTwoCharacters(date.getDate());
      var strHour = toTwoCharacters(date.getHours());
      var strMinute = toTwoCharacters(date.getMinutes());
      var strSecond = toTwoCharacters(date.getSeconds());
      return strYear + '-' + strMonth + '-' + strDate + ' ' + strHour + ':' + strMinute + ':' + strSecond;
   },

   fromSQLiteDateToJSDate: function(strDateTime) {
      var strDate = strDateTime.split(' ')[0];
      var strTime = strDateTime.split(' ')[1];
      var arrDate = strDate.split('-');
      var arrTime = strTime.split(':');
      return new Date(arrDate[0], arrDate[1]-1, arrDate[2], arrTime[0], arrTime[1], arrTime[2]);
   }
};