Posts Tagged Date

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.

, , , , , ,


Comparing dates in SQLite and AIR

I was doing an application where I had to compare dates in my SQLite database, actually I needed to get from it all entries from yesterday. It turns out that this is not a so obvious operation in SQLite.

The first part of the operation is to get yesterday in date format. To do so I used the logic from Pixelbox post:

public static const MILLISECOND:Number = 1;
public static const SECOND:Number = MILLISECOND * 1000;
public static const MINUTE:Number = SECOND * 60;
public static const HOUR:Number = MINUTE * 60;
public static const DAY:Number = HOUR * 24;
public static const WEEK:Number = DAY * 7;
var yesterday:Date = new Date();
yesterday.time -= DateUtilities.DAY;

That part was easy.

Now comparing yesterday with dates in the databse was the hard part. It turns out that SQLite stores dates as a Julian day( interval of time in days and fractions of a day, since January 1, 4713 BC Greenwich noon) and that Flash saves them as UTC time (number of milliseconds since midnight January 1, 1970, universal time) which makes it pretty hard to compare.

Now I solved my problem this way:

"SELECT * FROM main.Entry WHERE STRFTIME('%d', dateF) = '" + 
DateUtils.lpad(String(, 2, "0") + "' AND STRFTIME('%m', dateF) = '" + 
DateUtils.lpad(String(yesterday.month + 1), 2, "0") ;

Where lpad is just a function to add a leading 0 if my day or month is smaller than 10 and dateF is the name of my date column in my database.  In this example, I got from the database all entry where the date had the same day and the same month as yesterday. I didn’t compare the year but it follows the same logic, here is a list of other parameters you can feed to the STRFTIME function.

Now this will only help you if you need a single day from your database, but if you are looking for a range comparison this will not help you. I will be looking for a way to convert UTC time to Julian Day and check if that works. Stay posted.

, , ,