[sqlite] ISO-8601 date string and '>' comparison buggy
Hi, consider this stripped-down database: -- CREATE TABLE "horde_alarms" ("id" INTEGER PRIMARY KEY, "alarm_end" datetime); INSERT INTO "horde_alarms" VALUES(1, '2013-08-28 22:00:00'); -- These queries work fine: -- sqlite> SELECT * FROM horde_alarms WHERE alarm_end < '2013-08-28T23:08:48'; 1|2013-08-28 22:00:00 sqlite> SELECT * FROM horde_alarms WHERE alarm_end > '2013-08-28 18:08:48'; 1|2013-08-28 22:00:00 -- This query does not work (as used by Horde): -- SELECT * FROM horde_alarms WHERE alarm_end > '2013-08-28T18:08:48'; -- Looks like the handling of ISO-8601 date strings has a problem here. Tested on sqlite 3.7.17. Version 3.6.x is also affected. Best regards, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISO-8601 date string and '>' comparison buggy
On 8/28/2013 5:04 PM, Thomas Jarosch wrote: consider this stripped-down database: -- CREATE TABLE "horde_alarms" ("id" INTEGER PRIMARY KEY, "alarm_end" datetime); INSERT INTO "horde_alarms" VALUES(1, '2013-08-28 22:00:00'); -- These queries work fine: -- sqlite> SELECT * FROM horde_alarms WHERE alarm_end < '2013-08-28T23:08:48'; 1|2013-08-28 22:00:00 sqlite> SELECT * FROM horde_alarms WHERE alarm_end > '2013-08-28 18:08:48'; 1|2013-08-28 22:00:00 -- SQLite doesn't have "datetime" data type. All these values are plain strings, and are compared as such. It just so happens that, if you use a suitable format consistently, usual string comparisons also order dates and times correctly. So, don't mix and match the format with and without T in the middle. Choose one, and stick to it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISO-8601 date string and '>' comparison buggy
On 28 Aug 2013, at 10:04pm, Thomas Jarosch wrote: > INSERT INTO "horde_alarms" VALUES(1, '2013-08-28 22:00:00'); To conform to ISO-8601, the space between the date and time should be a capital T. Though that's not your problem, as Igor explained. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISO-8601 date string and '>' comparison buggy
Zitat von Igor Tandetnik : SQLite doesn't have "datetime" data type. All these values are plain strings, and are compared as such. It just so happens that, if you use a suitable format consistently, usual string comparisons also order dates and times correctly. So, don't mix and match the format with and without T in the middle. Choose one, and stick to it. thanks for the quick explanation. Now that's what I would call a funny issue :) I'll prepare an upstream horde fix. Best regards, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users