Re: [sqlite] translate time comparison statement
[EMAIL PROTECTED] wrote:
Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
SELECT school_name from schools WHERE julianday('now') -
julianday(arrival_date) > 7
Dennis Cote <[EMAIL PROTECTED]> wrote:
SELECT school_name from schools
WHERE date(arrival_date) < date('now', '-7 days');
SELECT school_name from schools
WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
All answers above are correct, as far as I can see at a quick
glance. But here is an efficiency tip: You can move the
constant date calculations into a subquery and thereby only
evaluate them once for the whole statement instead of once
for each row of result. For example:
WHERE (SELECT julianday('now')) - julianday(arrival_day) > 7
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
WHERE (SELECT julianday(date('now'))) - julianday(date(arrival_date))>7
This is a dirty trick and it does make the query more difficult
to read, so only use it if it is necessary for performance.
--
D. Richard Hipp <[EMAIL PROTECTED]>
This is a good tip.
FYI - this sort of factoring of repeated or expensive subselects is
exactly what the SQL:1999 standard WITH clause is designed for. In
standard SQL this could be written as:
WITH oldest (date) AS (SELECT date('now', 'localtime', -7 days'))
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date FROM oldest)
This creates a temporary table called oldest with a single field called
date, and fills it with the result of the select expression which is
only executed once. This table can then be referred to in the main
select statement. This is basically the same thing that SQLite is doing
automatically when you factor the constant calculations into a subselect.
As with many of the more advanced standard SQL features, it can be a
little wordy for simple cases such as this. However it is just as
effective for more complicated cases. For example, if there were two
date fields that needed to be tested, the expensive date call would
still only be executed once. This power comes from the fact that the
subqueries are named, and can be referenced by name in multiple places.
WITH oldest (date) AS (SELECT date('now', 'localtime', -7 days'))
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date FROM oldest)
OR date(completed_date) < (SELECT date FROM oldest)
Whereas with the current SQL supported by SQLite the same subselect
would have to be repeated, and executed, twice (At least until the
optimizer is smart enough to detect and factor the common sub
expressions automatically, which may never happen.).
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
OR date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
Note that the WITH clause allows multiple subselect to be explicitly
factored by the user, and that each subquery can refer to any other
subqueries defined earlier. As an example, the steps used to prepare the
limit date could be separated like this.
WITH
now (date) AS (SELECT date('now')),
local_now (date) AS (SELECT date((SELECT date FROM now), 'localtime')),
oldest (date) AS (SELECT date((SELECT date FROM local_now), '-7 days'))
SELECT school_name FROM schools
WHERE date(arrival_date) < (SELECT date FROM oldest)
OR date(completed_date) < (SELECT date FROM oldest)
Re: [sqlite] translate time comparison statement
Mark Wyszomierski wrote:
You may want
WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
Mark,
You should still use the 'localtime' modifier on the 'now' value if your
timestamps are local time since 'now' always returns UTC times.
WHERE julianday(date('now', 'localtime')) - julianday(date(arrival_date)) > 7
Dennis Cote
Re: [sqlite] translate time comparison statement
Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
>
> SELECT school_name from schools WHERE julianday('now') -
> julianday(arrival_date) > 7
>
Dennis Cote <[EMAIL PROTECTED]> wrote:
>
> SELECT school_name from schools
> WHERE date(arrival_date) < date('now', '-7 days');
>
> SELECT school_name from schools
> WHERE date(arrival_date) < date('now', 'localtime', '-7 days');
>
Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
>
> WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
>
All answers above are correct, as far as I can see at a quick
glance. But here is an efficiency tip: You can move the
constant date calculations into a subquery and thereby only
evaluate them once for the whole statement instead of once
for each row of result. For example:
WHERE (SELECT julianday('now')) - julianday(arrival_day) > 7
WHERE date(arrival_date) < (SELECT date('now','localtime', '-7 days'))
WHERE (SELECT julianday(date('now'))) - julianday(date(arrival_date))>7
This is a dirty trick and it does make the query more difficult
to read, so only use it if it is necessary for performance.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] translate time comparison statement
Cool thanks,
Mark
On 1/11/06, Kurt Welgehausen <[EMAIL PROTECTED]> wrote:
>
> You may want
>
> WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
>
> so that time of day isn't part of the comparison; otherwise,
> you're correct.
>
> Regards
>
Re: [sqlite] translate time comparison statement
You may want
WHERE julianday(date('now')) - julianday(date(arrival_date)) > 7
so that time of day isn't part of the comparison; otherwise,
you're correct.
Regards
Re: [sqlite] translate time comparison statement
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
[sqlite] translate time comparison statement
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'
Thanks,
Mark
