In past week at FlashBrain, Where do I work; We were working on one cool AIR application with Flash. Naresh – My good friend and colleague at FlashBrain was dealing with timestamps for better algorithm for online and offline sync with AIR.

We were trying to put timestamp on local and remote database to compare the data based on time to get idea about which record is last updated. Our remote database is MySQL and local database is SQLite which is a part of AIR. TIMESTAMP is a known data type for MySQL and which automatically inserts the timestamp based on whenever you create the record. While, in SQLite it does not support TIMESTAMP as native data type for fields.

Usually, SQLite converts or casts the data types automatically and in this process it guesses the data types and tries to convert the data in to nearest possible data type. So if you will enter TIMESTAMP like you are doing in MySQL, it will treat the data as numeric and will not solve the purpose for TIMESTAMP. SQLite only supports DATETIME as field data type. This DATETIME is also stored in specific format of DDDD.DDDD which is julian day number expressed as floating point value. So to store TIMESTAMP in to a SQLite with AIR, you need to convert your TIMESTAMP value in to this DDDD.DDDD format before you insert in to SQLite. Also, at time of retrival of data, you need to convert the data in to your desired data type before you use it.

Here is example, Naresh entered in our internal Knowledge Base, Which we named as Knowledge Dose:

Inserting the data in to SQLite:

INSERT INTO <table> (<column>) VALUES (’2008-06-09 07:20:00′);

here, the important thing is – <column> data type should be DATETIME.

Fetching the data back from SQLite:

SELECT datetime(<column>) AS <variableName> FROM <table>;

I hope this would be helpful to someone!