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(yesterday.date), 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.

, , ,