> On Thu, 12 Sep 2013 14:01:04 +0100
> Simon Davies <[email protected]> wrote:
>
> > Why not
> > SELECT * FROM "entry" WHERE
> > bankdate >= date('now','start of month')
> > AND bankdate < date('now','start of month','+1 month')
>
> The half-open interval strikes again! :-)
And you are using UTC ...
Notwithstanding the timezone you want to use,
explain select *
from entry
where bankdate >= date('now', 'start of month')
and bankdate < date('now', 'start of month', '-1 day');
will generate the following code:
SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},FUNCTION:date(item[0] = 'now'
item[1] = 'start of
month')),LT({0:0},FUNCTION:date(item[0] = 'now'
item[1] = 'start of month'
item[2] = '-1 day')))
END
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Goto 0 19 0 00
2 OpenRead 0 2 0 1 00
3 Rewind 0 17 0 00
4 Column 0 0 1 00
5 String8 0 3 0 now 00
6 String8 0 4 0 start of month 00
7 Function 3 3 2 date(-1) 02
8 Lt 2 16 1 collseq(BINARY) 6a
9 String8 0 5 0 now 00
10 String8 0 6 0 start of month 00
11 String8 0 7 0 -1 day 00
12 Function 7 5 2 date(-1) 03
13 Ge 2 16 1 collseq(BINARY) 6a
14 Column 0 0 8 00
15 ResultRow 8 1 0 00
16 Next 0 4 0 01
17 Close 0 0 0 00
18 Halt 0 0 0 00
19 Transaction 0 0 0 00
20 VerifyCookie 0 1 0 00
21 TableLock 0 2 0 entry 00
22 Goto 0 2 0 00
sqlite>
You will note that the two date functions are executed for each candidate row.
But, if you have a suitable index, the date functions are only executed once:
create index entry_bankdate on entry (bankdate);
SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},FUNCTION:date(item[0] = 'now'
item[1] = 'start of
month')),LT({0:0},FUNCTION:date(item[0] = 'now'
item[1] = 'start of month'
item[2] = '-1 day')))
END
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Goto 0 21 0 00
2 OpenRead 1 3 0 keyinfo(1,BINARY) 00
3 String8 0 2 0 now 00
4 String8 0 3 0 start of month 00
5 Function 3 2 1 date(-1) 02
6 IsNull 1 19 0 00
7 SeekGe 1 19 1 1 00
8 String8 0 4 0 now 00
9 String8 0 5 0 start of month 00
10 String8 0 6 0 -1 day 00
11 Function 7 4 1 date(-1) 03
12 IsNull 1 19 0 00
13 IdxGE 1 19 1 1 00
14 Column 1 0 7 00
15 IsNull 7 18 0 00
16 Column 1 0 8 00
17 ResultRow 8 1 0 00
18 Next 1 13 0 00
19 Close 1 0 0 00
20 Halt 0 0 0 00
21 Transaction 0 0 0 00
22 VerifyCookie 0 2 0 00
23 TableLock 0 2 0 entry 00
24 Goto 0 2 0 00
sqlite>
** I cannot say if this is correct behavior or not. I would say that it is
correct for each row because date('now') retrieves the date "now", not "then",
so perhaps it should be executed for each row ... though it may not be exactly
what you expect since you are probably making an assumption about "now" -- is
it the "now" at the beginning of the query, or is it the "now" at which each
row is tested. They are indeed different "now"s. But with an index in the
optimization it is difficult to use a moving "now"ness. However, this is a
minor issue since you can force the correct code to be generated for whatever
you intention may be **
Without the index (or even with the index) you can force the date functions to
only be executed once on the first loop-through by putting them in a scalar
subselect (or selecting the values into a temp table):
explain select *
from entry
where bankdate >= (select date('now', 'start of month'))
and bankdate < (select date('now', 'start of month', '-1 day'));
SELECT {0:0}
FROM {0,*} = entry
WHERE AND(GE({0:0},(SELECT FUNCTION:date(item[0] = 'now'
item[1] = 'start of month')
LIMIT 1
END)),LT({0:0},(SELECT FUNCTION:date(item[0] = 'now'
item[1] = 'start of
month'
item[2] = '-1 day')
LIMIT 1
END)))
END
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Goto 0 30 0 00
2 OpenRead 0 2 0 1 00
3 Rewind 0 28 0 00
4 Column 0 0 1 00
5 Once 0 13 0 00
6 Null 0 3 0 00
7 Integer 1 4 0 00
8 String8 0 6 0 now 00
9 String8 0 7 0 start of month 00
10 Function 3 6 5 date(-1) 02
11 Move 5 3 0 00
12 IfZero 4 13 -1 00
13 Lt 3 27 1 collseq(BINARY) 6a
14 Column 0 0 1 00
15 Once 1 24 0 00
16 Null 0 8 0 00
17 Integer 1 9 0 00
18 String8 0 11 0 now 00
19 String8 0 12 0 start of month 00
20 String8 0 13 0 -1 day 00
21 Function 7 11 10 date(-1) 03
22 Move 10 8 0 00
23 IfZero 9 24 -1 00
24 Ge 8 27 1 collseq(BINARY) 6a
25 Column 0 0 14 00
26 ResultRow 14 1 0 00
27 Next 0 4 0 01
28 Close 0 0 0 00
29 Halt 0 0 0 00
30 Transaction 0 0 0 00
31 VerifyCookie 0 3 0 00
32 TableLock 0 2 0 entry 00
33 Goto 0 2 0 00
sqlite>
selecting the values into a temporary join table is a little more efficient,
especially if the query will contain multiple joins:
explain select *
from entry,
(select date('now', 'start of month') as begindate,
date('now', 'start of month', '-1 day') as enddate) as
dates
where bankdate >= begindate
and bankdate < enddate;
SELECT item[0] = {0:0} AS bankdate
item[1] = {1:0} AS begindate
item[2] = {1:1} AS enddate
FROM {0,*} = entry
{1,*} = SELECT item[0] = FUNCTION:date(item[0] = 'now'
item[1] = 'start of month') AS
begindate
item[1] = FUNCTION:date(item[0] = 'now'
item[1] = 'start of month'
item[2] = '-1 day') AS enddate
END (tabname=sqlite_subquery_56EBD0_) (AS dates)
WHERE AND(GE({0:0},{1:0}),LT({0:0},{1:1}))
END
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Integer 15 1 0 00
2 Once 0 15 0 00
3 OpenEphemeral 1 2 0 00
4 Goto 0 32 0 00
5 String8 0 4 0 now 00
6 String8 0 5 0 start of month 00
7 Function 3 4 2 date(-1) 02
8 String8 0 6 0 now 00
9 String8 0 7 0 start of month 00
10 String8 0 8 0 -1 day 00
11 Function 7 6 3 date(-1) 03
12 MakeRecord 2 2 9 00
13 NewRowid 1 10 0 00
14 Insert 1 9 10 08
15 Return 1 0 0 00
16 OpenRead 0 2 0 1 00
17 Rewind 1 30 0 00
18 Rewind 0 29 0 00
19 Column 0 0 11 00
20 Column 1 0 12 00
21 Lt 12 28 11 collseq(BINARY) 6a
22 Column 1 1 13 00
23 Ge 13 28 11 collseq(BINARY) 6a
24 Column 0 0 14 00
25 Column 1 0 15 00
26 Column 1 1 16 00
27 ResultRow 14 3 0 00
28 Next 0 19 0 01
29 Next 1 18 0 01
30 Close 0 0 0 00
31 Halt 0 0 0 00
32 Transaction 0 0 0 00
33 VerifyCookie 0 3 0 00
34 TableLock 0 2 0 entry 00
35 Goto 0 5 0 00
And is even more efficient if there is an appropriate index available:
SELECT item[0] = {0:0} AS bankdate
item[1] = {1:0} AS begindate
item[2] = {1:1} AS enddate
FROM {0,*} = entry
{1,*} = SELECT item[0] = FUNCTION:date(item[0] = 'now'
item[1] = 'start of month') AS
begindate
item[1] = FUNCTION:date(item[0] = 'now'
item[1] = 'start of month'
item[2] = '-1 day') AS enddate
END (tabname=sqlite_subquery_56E2D0_) (AS dates)
WHERE AND(GE({0:0},{1:0}),LT({0:0},{1:1}))
END
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00
1 Integer 15 1 0 00
2 Once 0 15 0 00
3 OpenEphemeral 1 2 0 00
4 Goto 0 34 0 00
5 String8 0 4 0 now 00
6 String8 0 5 0 start of month 00
7 Function 3 4 2 date(-1) 02
8 String8 0 6 0 now 00
9 String8 0 7 0 start of month 00
10 String8 0 8 0 -1 day 00
11 Function 7 6 3 date(-1) 03
12 MakeRecord 2 2 9 00
13 NewRowid 1 10 0 00
14 Insert 1 9 10 08
15 Return 1 0 0 00
16 OpenRead 2 3 0 keyinfo(1,BINARY) 00
17 Rewind 1 32 0 00
18 Column 1 0 11 00
19 IsNull 11 31 0 00
20 SeekGe 2 31 11 1 00
21 Column 1 1 11 00
22 IsNull 11 31 0 00
23 IdxGE 2 31 11 1 00
24 Column 2 0 12 00
25 IsNull 12 30 0 00
26 Column 2 0 13 00
27 Column 1 0 14 00
28 Column 1 1 15 00
29 ResultRow 13 3 0 00
30 Next 2 23 0 00
31 Next 1 18 0 01
32 Close 2 0 0 00
33 Halt 0 0 0 00
34 Transaction 0 0 0 00
35 VerifyCookie 0 4 0 00
36 TableLock 0 2 0 entry 00
37 Goto 0 5 0 00
Plus, it allows the optimizer to optimize nicely -- even for complicated
queries -- and no matter whether the date range is tested in an outer or
innermost loop ...
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
