Posts Tagged Julian Day

Date range query using SQLite and AIR

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.

, , , , , ,