Keith Medcalf wrote:
>> 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:
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 :-|.
[...]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
