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