UPDATE 3/25/09: Paul Robertson from the AIR team stopped by and writes that declaring your SQLite column affinity (ie column type) as “DATE” will instruct AIR to handle all date conversions for you automatically. The problems I experienced mainly surfaced in a DataGrid when using a DateField and I have not had a chance to poke around with that yet. Another approach is to extend DateField and override the “data” setter which is how the DataGrid supplies item editors with their value. Then you can deal with casting issues manually, however that may be more of a hack. The article below still has some good information that is still helpful for dealing with Dates in AIR/SQLite.
Working with SQLite and Flex/AIR Date values can be tricky and various caveats are not particularly well documented. The confusion (for me) is that ActionScript is loaded with UTC functions, and SQLite will happily insert them into DATETIME columns. Everything appears fine, however SQLite does not actually recognize this format as a Date and treats it as plain text. You have no way to see this happened until you try to apply some date formatting functions and notice SQLite returning NULL. SQLite is so lax about data integrity that you can insert anything into any column type and will never receive any warnings. AIR, though, will attempt to cast values behind the scenes based on column types and so you will run into ‘Invalid Date’ errors and weird glitches when attempting to update data.
The magic solution is the Julian Date Format which both SQLite and AIR recognize as a date value. This is somewhat surprising as ActionScript has no built-in support for outputting Julian dates. If you’re like me, you may have already hacked up workarounds using int fields with timestamps, however your matching ActionScript class properties have to be hacked to match, and the hacking can trickle down throughout your code. This also prevents you from using the SQLStatement.itemClass functionality, which is nice when using Cairngorm, DAOs, value objects, etc.
To avoid the pain follow these rules when working with dates:
1. If you want a strongly typed Date field in AIR, the relevant SQLite column must be defined as DATETIME. The interesting thing about this is that DATETIME is not technically a recognized SQLite column type and according to the SQLite docs it will be considered numeric. But AIR is obviously looking at the column definition somewhere in the framework because it will refuse to automatically cast any value where the column type is not DATETIME.
2. Whenever inserting or updating DATETIME fields, you must store it in Julian format (or NULL). SQLite will happily accept many common date formats. However AIR will behave inconsistently. Here is how to insert Julian dates in a variety of ways:
Inserting a Julian date manually via SQL:
To do this, simply have SQLite format your date value to Julian format using ‘%J’
UPDATE my_table SET my_column = STRFTIME('%J','2008-01-02 03:04:05')
Inserting a Julian date via AIR (with parameters):
Parameters are the best way to build SQL statements as you can use strongly typed Date variables and AIR will deal with the formatting for you.
statement.text = "UPDATE my_table SET my_column = :my_value"; statement.parameters[":my_value"] = new Date(2008,0,2,3,4,5); // Jan 02, 2008 03:04:05
Inserting a Julian date via AIR (without parameters):
If you are not using parameters, you have to pre-format the date into something that SQLite can parse. This is surprisingly obnoxious and requires you to write a couple of helper functions. (Note – if you know of an easier way to do this, please post a comment.)
public function lpad(original:Object, length:int, pad:String):String
{
var padded:String = original == null ? "" : original.toString();
while (padded.length < length) padded = pad + padded;
return padded;
}
public function toSqlDate(dateVal:Date):String
{
return dateVal == null ? null : dateVal.fullYear
+ "-" + lpad(dateVal.month + 1,2,'0') // month is zero-based
+ "-" + lpad(dateVal.date,2,'0')
+ " " + lpad(dateVal.hours,2,'0')
+ ":" + lpad(dateVal.minutes,2,'0')
+ ":" + lpad(dateVal.seconds,2,'0')
;
}
var myDate:Date = new Date(2008,0,2,3,4,5); // Jan 02, 2008 03:04:05
statement.text = "UPDATE my_table SET my_column = strftime('%J','" + toSqlDate(myDate) + "')";
Fudging data to work around AIRs validation
If you absolutely refuse to change your schema (for example you insist on using timestamps, or you have to maintain compatibility with other clients) you can get AIR to play along during READ operations by altering your select statement like so:
SELECT STRFTIME('%J',my_column) as my_column from my_table
This does assume that the data is in a format that SQLite recognizes as a date. If SQLite can’t parse the date value, then it will just return NULL. For hilarity sake, you can also use this ridiculous date format which surprisingly works with AIR. A word of warning about this workaround is that, even though you will be able to read data, you may not be able to update data via SQLCommand parameters if your column types are DATETIME because AIR will complain about an invalid date (see errors below). You will have either have to write your own SQL statements without parameters or else change your column types to int or varchar.
Formatting a Julian date manually in SQL so you can read it:
Julian values are great and all that, but it’s pretty much impossible to eyeball them when you’re working at the command line. SQLite recognizes Julian formatting as a valid date, so you can use the STRFTIME function to format and output it any way you like. Below is a simple example that is easier to read:
SELECT STRFTIME('%Y-%m-%d %H:%M:%S',my_column) as my_column_formatted FROM my_table
Common errors that occur while working with dates:
Invalid Date
You may see this in a DataGrid instead of the expected date value. This is because you have a DATETIME column in SQLite, however the value is not in Julian format. Even though SQLite may recognize it as a date value, AIR does not. The solution is to clean your data so that all dates are Julian format, or alternatively change the column type to VARCHAR.
‘Error #3115: SQL Error.’, details:’could not convert string value to date’
This error occurs when you try to update a record that has one or more DATETIME columns that do not have the date stored in Julian format. The weird part is that even if you are not touching that specific column in your insert/update statement – AIR will still validate the Date and throw this error. The solution is to clean your data so that all dates are Julian format, or alternatively change the column type to VARCHAR.
If you have any tips or corrections please post a comment and I’ll incorporate it into the article.
