[sqlite] ISO-8601 date string and '>' comparison buggy

[Thomas Jarosch]

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

[Igor Tandetnik]

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

[Simon Slavin]

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

[Thomas Jarosch]

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