Re: [sqlite-dev] SQLite 3.16.2-3 - Incomplete support for ISO8601 strings with time zone

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Re: [sqlite-dev] SQLite 3.16.2-3 - Incomplete support for ISO8601 strings with time zone

Keith Medcalf

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 59.999999999999999.

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 [mailto:[hidden email]]
>On Behalf Of François Lepage
>Sent: Sunday, 5 November, 2017 07:37
>To: [hidden email]
>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
[hidden email]