> On Thu, 12 Sep 2013 14:01:04 +0100 > Simon Davies <simon.james.dav...@gmail.com> 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users