The datetime() function takes an argument which represents a date and time string. The magic string 'now' equates to the computers concept of the current GMT time. This string, unless an additional modification is applied via the 'localtime' modifier, is always returned as a timestring in GMT (offset +0000). Floating Point numbers are recognized as Julian Day and fraction of a day. The datetime function does not return any indication of localization. If you forget what the localization is, then you will be hoopered.
The "localized" representation, 2010-01-01 01:01:01-04:00, if fed to "datetime" will return a timestring with precision of one second which DOES NOT CONTAIN anything to indicate that it is GMT. Similarly, applying the 'localtime' modifier will return the computers idea of the local time, again WITH NO INDICATION AS TO THE GMT OFFSET. When you apply the datetime function to a timestring (or julianday floating number), the string returned will be a GMT timestring with a precision to the second only. If the input timestring is naive (has no offset from GMT) it will be assumed to represent a GMT datetime string. If it is localized (that is, it contains a Z or offset from GMT), then it will be converted to a GMT timestring. https://www.sqlite.org/datatype3.html Does not anywhere that I can see refer to a datetime datatype. The only datatypes are NULL, INTEGER, REAL, TEXT, and BLOB. It says that right at the very top of the page. Date/time in a database should be handled by storing either unixepoch integers, julianday numbers, or text ISO-8601. In the case of text ISO-8601, you will need to make sure that they are always localized (contain an indication of the timezone). Only if all your ISO-8601 formatted datestrings are stored with THE SAME LOCALIZATION can they be compared directly (ie, using <>= string comparisons). unixepoch or julianday values can be compared directly. Handling of date/time in databases is the place where people who only live in "localtime" usually fall apart, usually twice a year, at each switch into and outof Daylight time. If you want to avoid such problems, then all date/time information should be stored as GMT values -- that is, GMT/+0000/Z ISO-8501 strings, unixepoch integers, or julianday day+offset real values. Conversion to 'localtime' for display should occur *only* in the interface for presentation to the user. User (or externally supplied) data that is not GMT should be converted to GMT immediately. No processing should EVER use a localized date/time unless you are using an entirely localization aware library to do so (which means Windows is completely unsuitable for handling date/time data, without third party library assistance). The SQLite julianday function will return julianday day + offset floating point numbers from either a special modifier ('now'), or an input localized iso-8601 string. If the input timestring is naive (contains no offset from GMT) this it is assumed to be GMT. julianday values are comparable with < > = !=. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of J Decker >Sent: Friday, 2 January, 2015 17:12 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Is this date supposed to be less than or more than >the other? > >Okay... >https://www.sqlite.org/lang_datefunc.html > >https://www.sqlite.org/datatype3.html /* lists DateTime as a distinct >type >*/ > >I understand it's kept as a string... and there's no internal functions >for >this... but wasn't there a discussion to add hex and octal etc support >for >number conversions? 1.2Meg of stuff and you can't have a few 30 line >functions to convert to gregorian calander day/second of day for >calculation(julian day and second tick); amazing. > >---------- >Since on the datefunc page > >Formats 2 through 10 may be optionally followed by a timezone indicator >of >the form "*[+-]HH:MM*" or just "*Z*". The date and time functions use UTC >or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero >"HH:MM" suffix is subtracted from the indicated date and time in order to >compute zulu time. For example, all of the following time strings are >equivalent: > >2013-10-07 08:23:19.120 >2013-10-07T08:23:19.120Z >2013-10-07 04:23:19.120-04:00 >2456572.84952685 > >Defines equivalency... I would have assumed that inequalities could also >be >done. > > >And since 'formats supported' are specified, one would assume that >datetime >columns with supported strings would work with at least =, >= <=, <, >, >!= > >------------ >I'll add a user function or something instead I guess... > > >On Fri, Jan 2, 2015 at 3:21 PM, Clemens Ladisch <clem...@ladisch.de> >wrote: > >> J Decker wrote: >> > is this... 2015-01-02 20:47:18 (this is datetime( 'now', '-3600 >second' >> ) >> > >> > received = 2015-01-02 13:46:23.818-0800 this is a DATETIME column >> recorded >> > in the database >> >> SQLite has no DATETIME datatype. This is just a string. >> >> > select * from messages where received < datetime( 'now', '-3600' ) >> >> This compares two strings. >> >> > delete from messages where datetime(received) < datetime( 'now', >> '-3600' ) >> >> Something like this is needed, but you need to use a string containing >a >> date >> value in a supported format: >> >> $ sqlite3 >> sqlite> select '2015-01-02 13:46:23.818-0800' < datetime('2015-01-02 >> 20:47:18'); >> 1 >> sqlite> select datetime('2015-01-02 13:46:23.818-0800') < >> datetime('2015-01-02 20:47:18'); >> >> sqlite> select datetime('2015-01-02 13:46:23.818-08:00') < >> datetime('2015-01-02 20:47:18'); >> 0 >> >> > to apply the function to the column again? >> >> Why are you using the word "again"? >> >> >> Regards, >> Clemens >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
