[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)
