I have come across inconsistent results of queries using datetime functions and values. I have based everything I tested off of this page: http://www.sqlite.org/lang_datefunc.html
I have a table with a /start_date/ property. The values written are UTC time of the format /YYYY-MM-DDTHH:MM:SS-XX:YY/ (where /-XX:YY/ is the timezone offset; not in the valid formats according to the above page, but is according to http://www.w3.org/TR/NOTE-datetime that is linked to by the above page; for me, all my data ends in /-00:00/ - no timezone offset from UTC). I am going to use a very specific example. Today is 2011-04-01, and I am currently at timezone -06:00. I want to query all records from today, which is really from 2011-04-01 06:00 through 2011-04-02 05:59:59 according to my timezone offset. I have one record with the datetime /start_date/ of /2011-04-01T03:49:32-00:00/. This datetime is not "today"; because of the UTC offset, it is actually yesterday. So it should NOT be included when I search for records from today. The following query works correctly by excluding this record (note the inclusion of the *'T'* literal in the datetimes): SELECT ... where /start_date/ between '2011-04-01T06:00:00' and '2011-04-02T06:00:00' The following query does NOT work correctly and includes the unexpected record (note the removal of the *'T'* literal): SELECT ... where /start_date/ between '2011-04-01 06:00:00' and '2011-04-02 06:00:00' Note that the sqlite functions /datetime('now', 'start of day', 'utc')/ and /datetime('now', 'start of day', '+1 day', 'utc')/ return the two datetimes in the second query (respectively -- for today=2011-04-01), so rewriting the query using these two functions also fails to exclude the record. Also note that instead of using the *'between'* keyword, I also tried to use /start_time >= time1 and start_time <= time2/ and got the same results. So is this a sqlite bug in that inconsistent results are coming from queries that should be functionally equivalent according to the above documentation (/"A time string can be in any of the following formats: ..."/)??? Or is it "bad" data given to correct sqlite functionality??? If it is the latter, why is the /YYYY-MM-DDTHH:MM:SS-XX:YY/ format not supported in sqlite? Anyway, a thousand apologies if I overlooked anything, if this is actually supposed to be a "feature", etc. - Ian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
