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