Whilst playing with fossil, I have encountered what I believe to be a
deficiency in strftime() (and friends).
As the Sqlite documentation states, dates supplied to the date
functions may have a timezone suffix i.e. "[+-]HH:MM" or just "Z".
The Sqlite input date parsing is faultless to my testing.
The catch is that there is no way to output what one inputs.
e.g. 2013-10-07 04:23:19 -04:00
There is no date_tzoffset('date') function or modifier to perform this task.
The 'utc', 'localtime' modifiers or no modifier enable one to
calculate an offset for a datetime (this offset is internally
calculated and can be calculated with some lengthy SQL).
The proposal:
1. Supply an additional '%z' substitution for strftime().
Without the 'localtime' modifier this will produce the string "Z".
With the 'localtime' modifier this will produce the string "[+-]HH:MM"
2. For the date(), time() and datetime() functions allow an additional
modifier (say 'tz').
This would change datetime() return value as follows:
select datetime('2019-04-21 08:03:07'); -- '2019-04-21 08:03:07'
select datetime('2019-04-21 08:03:07','tz'); -- '2019-04-21 08:03:07Z'
select datetime('2019-04-21 08:03:07','localtime'); -- '2019-04-21 18:03:07'
select datetime('2019-04-21 08:03:07','localtime','tz'); --
'2019-04-21 18:03:07+10:00'
similarly for date() and time().
This change would allow:
select datetime(datetime('now','localtime','tz'));
To produce a correct UTC datetime.
I have a patch.
Prompted by fossil date/time handling.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
