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.

, , , , , ,

  1. #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. #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. #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. #4 by francois jacquier - August 24th, 2009 at 08:08

  5. #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?

  6. #6 by Fabio - November 11th, 2010 at 08:27

    there’s an error in the UTCToJulianDay method, the right method is
    function UTCToJulianDay(newDate:Date):Number{
    return ((newDate.time – (newDate.getTimezoneOffset() * 60000)) / 86400000 + 2440587.5);
    }
    Thanks for the great job!

  7. #7 by dinko - June 23rd, 2011 at 07:48

    @ Arnaud
    Thanks! It worked perfectly!
    Regards,
    Dinko

  8. #8 by Thaylor Mosquera Castro - May 23rd, 2012 at 13:04

    Can you help me insert a record with a DATE field in sqlite from air, I have my sql to insert but insert all other fields except the DATE

    Pueden ayudarme a insertar un registro con un campo DATE en sqlite desde air, yo tengo mi sql para insertar pero inserta todos los demas campos menos el DATE

    private function getJulianDate(y:Number, m:Number, d:Number):String {
    var D0:Number = Date.UTC(1582, 9, 15);
    var J0:Number = 2299160.5;
    var d:Number = Date.UTC(y, m, d);
    var jd:Number = (d-D0)/1000/60/60/24+J0;
    return (jd<J0 ? "invalid" : jd.toString());
    }

    private function inscompro(unidades:String,idproducto:String):void{
    conectar();
    var sql:SQLStatement = new SQLStatement();
    sql.addEventListener(SQLEvent.RESULT,ucodigo);
    sql.sqlConnection = conn;
    var sqltext:String = "";
    sql.clearParameters();
    //Alert.show(getJulianDate(1984,7,1));
    sqltext = "INSERT INTO 'compras' (fechaPedidoCOM,contidadCOM,productos_idPRO) VALUES("+getJulianDate(1984,7,1)+",:contidad,:idproducto)";
    Alert.show(sqltext);
    sql.parameters[":contidad"] = unidades;
    sql.parameters[":idproducto"] = idproducto;
    sql.text = sqltext;
    try {
    sql.execute();
    }
    catch(error:SQLError) {
    Alert.show("Error en almacenamiento "+ error.toString());
    }
    }

    would be very grateful

    estaria muy agradecido

(will not be published)
Subscribe to comments feed
  1. No trackbacks yet.