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.
great, thanks for this!
Great~~~
??…
Thanks for posting this. How odd, and ridiculous!
Yes! Precisely what I was looking for. Thank you for this much needed elucidation.
Thanks – Couldn’t change my column from DateTime and AIR wouldn’t read the timestamp – this saved my app!
Thanks for sharing this, I’ve been struggling with this for quite a while.
Thanks – much needed advice
Thanks, that’s what I googling for.
I tried this in my insert
STRFTIME(‘%J’,’2008-01-02 03:04:05′)
for fun and my field showed this value. weird i had DATETIME as column type
2454467.627835648
Not sure why. I have something like this but i cant seem to get it into sqlite. I use CF and mysql quite a bit and i’ve never had such a hard time with dates.
I guess i’m not following you on this
Hey Rob, that is right – Julian date format is stored as a float. Run a SQL statement like this to see the date more easily readable:
SELECT STRFTIME(‘%Y-%m-%d’,my_column) FROM my_table
Cold Fusion probably has more lenient date parsing. AIR is really strict with Date casting. My guess is that they’ll make this a little easier in a later release, but this is what we have to work with currently.
I’ve used ActionScript Date objects directly, without any problems either inserting or retrieving the data.
I think the difference is that I create the column with a declared data type “Date” (or “DATE”) rather than “DATETIME”. If you use “Date” then AIR knows you’re going to be using ActionScript Date objects, and it does all the conversion automatically for you.
I assume the reason you’re using DATETIME is because you have an existing SQLite database or you’re reusing code from another app… If not, I’d suggest just using Date rather than DATETIME.
This information is in the documentation — although perhaps a bit buried:
http://livedocs.adobe.com/flex/3/langref/localDatabaseSQLSupport.html#columnAffinity
Paul Robertson
Adobe AIR team
(I’m the person who wrote the documentation for the SQLite feature)
Hi Paul
Is there a trick to updating a DATE column?
I am passing an AS3 Date obj in the SQLite statement but is not working.
Thanks for stopping by Paul. I should update this post a bit because I’ve since learned quite a lot more about list item renderers.
I’ve found that you can actually make the DateField more reliable in a DataGrid by extending the DateField class and overriding the “data” getter/setter. The problem happens in DateField.data setter where it detects “_listData is DataGridListData” and then craps out with a Type Coercion. If you extend DateField then you can override data and parse/cast the value however you like.
Your link definitely cleared up some suspicions I had about the column affinity. It also kinda blew my mind that you guys built in XML and Object column types, which has my curiosity.
It’s interesting how SQLite is documented so that it just looks for keywords. ie varchar(25) or varchar(10) both contain “char” so SQLite treats it as text. [http://www.sqlite.org/datatype3.html] So, perhaps it is the same with “date” meaning that “datetime” and “date” would both trigger to AIR that the column affinity is a date?
Thanks again.
Just wanted to say thanks for the post. Really helped save some time!
Today, I’m using this technique but today my application just breaked and I don’t know what to do to resolve the following issue:
Type Coercion failed: cannot convert 2454942.2072685184 to Date
Any tips? Thanks!
Hey Alex, it looks like you do have a julian date there, did you make sure your sqlite column type/affinity is “date” or “datetime” ? even though those column types are not technically recognized by sqlite, AIR does internally rely on them.
I am trying to insert a date in the db from javascript using parameters. The date inserts fine, but when I retrieve the data the date is returned as an object. I also tried formatting the date using the STRFTIME, but that returns null.
Hey Hermanshu, I’m not as familiar with using JavaScript in AIR apps. If you got an object back, you might inspect it to see if it’s some type of date structure, in which case you don’t need to do any of manipulation in your query.
I am getting the error on a SELECT query and none of the tables has any kind of date column. The datatypes are:
int
varchar(255)
varchar(1)
bit
varchar(50)
varchar(8)
Hey Tim, try running you query directly using something like SQL Query Manager first. If it runs fine there, then you can look further into AIR. It sounds like you may just have an error in your SQL statement, though.
So, I figured I’d add my experience. My data is written using a java application via Hibernate (persistence manager). I’m used to working in mysql/java so I strongly type the database like I would there even though it is probably pointless. All my date and time columns are DATETIME in the database. Hibernate (for wrong or right) stores dates to the DB using unix milisecond epoch time. This is probably an artifact of how the data normally travels to and from other DB engines. The Hibernate dialect (the thing that handles all these details) is pretty simple. I suppose I could have changed this there, but I digress.
The interesting thing for me was that queries like this:
SELECT MIN(someDateTime) AS d FROM SomeTable
would work if I then wrapped the results in a new Date(r['d']). The value in ‘d’ was the literal epoch time in miliseconds.
What didn’t work was just querying the date directly. It always came back as null. What I figured out (thanks to your post) is that AS was trying to coerce the date in the simple query into a date and failing. In the MIN/MAX (and I assume any other aggregate method) AS couldn’t tell the type so it just guessed number…
For me, because the data I’m working with only has second level precision to start with, I just modified my queries thusly:
SELECT … DATETIME(someDateTime/1000, ‘unixepoch’) ….
In my case java stores all dates in UTC, so if you want this to be local, you could add ‘local’ right after ‘unixepoch’ and sqlite would handle the timezone conversion.
The /1000 is because sqlite only understands unix epoch in seconds. I suppose I could also have just changed the column type to be int or something else NOT datetime and my new Date(…) trick from MIN/MAX would have worked. Still, a little insane.
Hi Jason,
I just found this page while researching an error #3115 in an AIR SQLite database. You have a lot of interesting info here, so thanks for that.
I already designed my Sqllite db a little differently, storing the DATE as an integer which I get by converting an AS3 Date object using the getTime() method. This returns the number of milliseconds since 1/1/1970. It’s pretty simple to convert back and forth from dates to integers using the getTime() and setTime() functions and so I haven’t run into any reasons not to do store dates this way.
I would appreciate any comments you may have on this method, especially if you see something which could cause a problem for me in the future. For instance, this method would not store the time zone information. But AS3 always adds the local TZ to any new Date object so I didn’t see that as an issue. Anything you see that I’m missing?
Thanks!
really you can store dates any way you want and inserting into the DB is easy. But if you plan to use SQLStatement.itemClass so that your sql queries are returned as strongly-typed objects then you may run into casting errors when selecting data. You can get around it using the workaround statement mentioned above:
SELECT STRFTIME(‘%J’,my_column) as my_column from my_table
then AIR will be able to cast the value of my_column as a date
Hey Jason,
am I missing something, or is the statement:
SELECT STRFTIME(‘%J’,my_column) as my_column from my_table
actually wrong?
I reached the desired effect with:
SELECT time(dateCreated) as dateCreated from clients;
However, this is not the problem that brought me here. Rather, I am having difficulties with storing dates on my sqlite table through actionscript.
My data type for my date column on my table is DATE, and I am referencing an actionscript Date object when executing my INSERT statement, but the values for my date (dateCreated) column keep appearing as numbers, not formated as desired: yyyy, mm, dd, hh:mm:ss!
Since I noticed Paul’s comment to be two and a half years old, and since his advice does not seem to be working in my case, I was wondering if you know what could be the issue… perhaps something changed since march 2009?
Julian dates are stored in the DB as a float. This is what Flex seems to like – you could think of them as serialized dates. If you want to visually read them straight out of the database then you can format them to display however you want.
Above is a section I wrote “Formatting a Julian date manually in SQL so you can read it” that will show you how you can visually look at dates in the database through a query browser.
Keep in mind this is not how you want to format your SQL statements in your app – this is just for visually inspecting the database.
Hey Jason,
thank you for your reply.
Would you please give a look to this one last question?
This is my SQLStatement:
public var insertClient:String = “INSERT INTO clients (…status, dateCreated, dateModified, …) VALUES (… :status, time(‘NOW’), time(‘NOW’), ….)”;
This stores dates on my database as floats. However, when I use the same statement through an sqlite administrator or command line dates seem to be stored in human readable format (yyyy – mm – dd). I want to store dates on my database, through actionscript code, in yyyy – mm – dd format. do you know if this is possible or not?