Mark Wyszomierski wrote:
Hi all,
I'm trying to translate some MySQL to sqlite. Ran into a problem with this
time comparison statement. I translated as best I could, it seems to work,
is this alright? (I'm trying to see if any records are more than 7 days old,
based on the value of 'arrival_date' field):
Original:
SELECT school_name from schools WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) >
arrival_date"
To sqlite:
SELECT school_name from schools WHERE julianday('now') -
julianday(arrival_date) > 7
And an example of the date value stored in the 'arrival_date' field looks
like:
'2006-01-10 16:14:19'
Mark,
What you have should work fine but the following might be a little clearer.
SELECT school_name from schools
WHERE date(arrival_date) < date('now', '-7 days');
This assumes your timestamps are UTC (or GMT). If not, you must convert
now to localtime like this.
SELECT school_name from schools
WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
HTH
Dennis Cote
