I decided I would investigate more on Dates and SQLite. As you know I had some trouble when trying to extract a date range from my SQLite database because Flash and SQLite don’t save dates in the same format. Flash saves it in UTC format and SQLite as a Julian day.
Looking around a bit I found on the php website some information about how to do the conversion from Julian day to UTC. All I had to do was to turn the formula around. But that didn’t work because the Date in Flash is also offested to account for the timezone of where the computer is. Once that was solved, everything worked fine. Here is a function that will do the conversion:
function UTCToJulianDay(newDate:Date):Number{ return ( newDate.time - (newDate.getTimezoneOffset()*60000)) / 86400000) + 2440587.5) }
The time method of Date object return milliseconds so we have to change everything in milliseconds. The getTimezoneOffset method returns minutes so by multiplying by 60000 you get its millisecond equivalent. 86400000 is the number of milliseconds in a day and 2440587.5 is the julian day at 1/1/1970 0:00 UTC.
So using this function will enable you to use the greater than, lesser than and BETWEEN operator in SQLite when you want to search for date ranges. Here are some example of SQL query you might do:
sqlStmt.text = "SELECT * FROM main.Entry WHERE dateF >; " + UTCToJulianDay(new Date()); //this will return all entries where dateF is bigger than now var date1:Date = new Date(2009, 2, 3) ; //March 3rd 2009 var date2:Date = new Date(2009, 3, 3); //April 3rd 2009 sqlStmt.text = "SELECT * FROM main.Entry WHERE dateF BETWEEN " + UTCToJulianDay(date1) + " AND " + UTCToJulianDay(date2) ; //this will return all entries Between March 3rd and April 3rd
It is that easy to do date range comparison; you just have to know how.
#1 by Russ - March 6th, 2009 at 12:48
You can also format the date column to match the date format you want.
something like this:
SELECT strftime(‘%d-%m-%Y’, recordedDate) AS theDate, FROM myTable WHERE theDate BETWEEN ’02-02-2009′ AND ’13-02-2009′
…russ
#2 by Arnaud - April 23rd, 2009 at 06:32
Hello,
If your date is stored as a Date by your AIR app, you can also make the following
SELECT * FROM myTable
WHERE date(dateField) BETWEEN “2009-02-01″ AND “2009-02-28″
This is much more simple and there is no need to convert data excepted by the date() function in the query but nothing in the actionscript code.
It actualy depends on your specific needs
Cheers
Arnaud
#3 by Charlie Hubbard - June 29th, 2009 at 15:25
You don’t need to write your own conversion routine since SQLite provides one for you. All you have to do is call strftime(“%J”, date). For example:
select * from SomeTable where create_at > strftime( “%J”, startDate ) and strftime( “%J”, endDate )
That will convert it to Julian for you.
#4 by francois jacquier - August 24th, 2009 at 08:08
good complementary information at http://www.verysimple.com/blog/2008/09/09/working-with-dates-in-flex-air-and-sqlite/
#5 by Luie - May 20th, 2010 at 17:19
Is there any way to prompt user for enter the date they want, then generate the report?