> On Saturday, 14 September, 2013 07:19, Yuriy Kaminskiy said:
> Keith Medcalf wrote:
> >> 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:
>
> And there are another pitfall in this query: date('now') is *RACY*. That
> is, if you execute this query *exactly* at midnight at month boundary, first
> and second DATE() invocation may refer to *different* months.
> And if your query return several rows, and there will be month boundary
> between sqlite3_step(), your query also can return data from two month.
> You can easily reproduce this problem if you switch unit from month to
> millisecond, e.g.
> SELECT * FROM t WHERE strftime('%f') <> strftime('%f');
> will non-deterministically return rows.
> IMO, correct [= least surprise] behavior should be "timestamp used for
> 'now' should cached on first row step, and reused in all following calls
> [until SQLITE_OK/SQLITE_DONE returned]", but fixing that won't be easy :-|.
That would require a change to SQLite itself. Without doing that I believe
this will solve those problems by using only one 'now', and also only running
each date() call once (even my previous solutions could suffer from race
conditions since they retrieved 'now' multiple times):
sqlite> explain select *
...> from entry,
...> (select bdate, (select date(bdate, '+1 month')) as edate
...> From (select date('now', 'localtime', 'start of
month') as bdate) as ttemp limit 1) as tstamp
...> where bankdate >= bdate
...> and bankdate < edate;
SELECT item[0] = {0:0} AS bankdate
item[1] = {1:0} AS bdate
item[2] = {1:1} AS edate
FROM {0,*} = entry
{1,*} = SELECT item[0] = {2:0}
item[1] = (SELECT FUNCTION:date(item[0] = {2:0}
item[1] = '+1 month')
LIMIT 1
END) AS edate
FROM {2,*} = SELECT FUNCTION:date(item[0] = 'now'
item[1] = 'localtime'
item[2] = 'start of month')
END (tabname=sqlite_subquery_56DA50_) (AS ttemp)
LIMIT 1
END (tabname=sqlite_subquery_56F8D0_) (AS tstamp)
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 33 1 0 00
2 Once 0 33 0 00
3 Goto 0 50 0 00
4 Goto 0 14 0 00
5 OpenPseudo 2 4 1 01
6 Integer 0 3 0 01
7 String8 0 5 0 now 00
8 String8 0 6 0 localtime 00
9 String8 0 7 0 start of month 00
10 Function 7 5 4 date(-1) 03
11 Yield 2 0 0 00
12 Integer 1 3 0 00
13 Yield 2 0 0 00
14 OpenEphemeral 1 2 0 00
15 Integer 1 8 0 00
16 Integer 4 2 0 00
17 Yield 2 0 0 00
18 If 3 33 0 00
19 Column 2 0 9 00
20 Null 0 11 0 00
21 Integer 1 12 0 00
22 Column 2 0 14 00
23 String8 0 15 0 +1 month 00
24 Function 2 14 13 date(-1) 02
25 Move 13 11 0 00
26 IfZero 12 27 -1 00
27 SCopy 11 10 0 00
28 MakeRecord 9 2 16 00
29 NewRowid 1 17 0 00
30 Insert 1 16 17 08
31 IfZero 8 33 -1 00
32 Goto 0 17 0 00
33 Return 1 0 0 00
34 OpenRead 0 2 0 1 00
35 Rewind 1 48 0 00
36 Rewind 0 47 0 00
37 Column 0 0 18 00
38 Column 1 0 19 00
39 Lt 19 46 18 collseq(BINARY) 6a
40 Column 1 1 20 00
41 Ge 20 46 18 collseq(BINARY) 6a
42 Column 0 0 21 00
43 Column 1 0 22 00
44 Column 1 1 23 00
45 ResultRow 21 3 0 00
46 Next 0 37 0 01
47 Next 1 36 0 01
48 Close 0 0 0 00
49 Halt 0 0 0 00
50 Transaction 0 0 0 00
51 VerifyCookie 0 1 0 00
52 TableLock 0 2 0 entry 00
53 Goto 0 4 0 00
sqlite>
It is a bit complicated though. It would be much simpler to do the date
calculation in the host language and pass the boundary values as parameters to
the query.
Of course, you could also do the following:
Select * from entry where bankdate like (select substr(date('now',
'localtime'), 7) + '%');
But of course this will always do a table/index scan that cannot be optimized
away ...
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
