As explained on the linked page, right near the top:

"The date and time functions use a subset of IS0-8601 date and time formats."

SUBSET means some of, but not all of.  ISO-8601 permits 24 hours, 60 minutes, 
and 61 seconds in a day.  However, this is not part of the "supported subset" 
either.  The supported subset permits hours in the range 00 through 23, minutes 
in the range from 00 through 59, seconds in the range from 00.0 through 

The "acceptable" offset formats are [ ]{+|-}HH:MM

The space is optional, the + or - is required, the hours MUST be two digits 
with a value between 00 and 14, the : is required, and the minutes must be two 
digits between 00 and 59.  +00:00 or -00:00 can be expressed as Z.

The subset of ISO-8601 which omits the ":" and minutes in the offset (or 
leading zero's) is not part of the supported subset.  Nor is using a "named" 
offset, other than the Z alias for +00:00 to indicate GMT.

Note that this is NOT the timezone.  It is the instant offset such that the 
datetime string is always a "proper" and identifiable "instant in time".  You 
can only guess what the timezone/localization is from all the available 
timezones which express the given "instant offset" at the GMT time given.  Even 
then you could be wrong.

Making the ":" or the minutes optional in the instant offset from Zulu time 
would be an enhancement or extension to the supported "subset of ISO-8601".

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-dev []
>On Behalf Of François Lepage
>Sent: Sunday, 5 November, 2017 07:37
>Subject: [sqlite-dev] SQLite 3.16.2-3 - Incomplete support for
>ISO8601 strings with time zone
>[...]the built-in Date And Time Functions
><>  of SQLite are capable of
>storing dates and times as TEXT, REAL, or INTEGER values:
>*      TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
>*      [...]
>According to my tests, there are some acceptable ISO8601 strings that
>can' t be processed with SQLite "Date And Time Functions", at least
>in version 3.16.2-3, when a time zone designator is used in numeric
>Consider the following valid ISO8601 strings :
>*      2017-11-05 14:05:43+00:00 - WORKS
>*      2017-11-05 14:05:43 -0400 - DOES NOT WORKS
>The space between the "ss" and the time zone seems to be tolerated (a
>good thing).
>Acceptable formats
>The fix would probably need to be implemented around here :
>static int getDigits(const char *zDate, const char *zFormat, ...){
>[...] }
>static int parseTimezone(const char *zDate, DateTime *p) { [...] }
>static int parseHhMmSs(const char *zDate, DateTime *p){ [...] }
>My programming experience does not allow me to submit the patch
>myself.  If anyone could fix this it would be great.

sqlite-users mailing list

Reply via email to