Timestamps are stored in db as strings (as far as I can tell), and include the date time offset from UTC.
The Problem
Timestamps were formerly displayed as output of sqlite date() and time() functions, which causes the times to be displayed as UTC regardless of server timezone.
The Solution
Given the following behaviour of sqlite3:
All sqlite date/time functions (without modifiers) return time as UTC time.
Sqlite date/time functions accept various modifiers including:
The localtime modifier converts a UTC time to the text repr of the same moment in local time.
The utc modifier converts a local time to the text repr of the same moment in UTC.
The +HH:MM or -HH:MM modifier adjusts the time by the offset specified.
PR Scope
This PR supports the sqlite3 +/-HH:MM and localtime modifiers.
The default is localtime - which lets sqlite adjust the time by the the system timezone (ie, will correctly reflect daylight savings time if applicable).
The user may also set an explicit offset from UTC - useful if the server is in a different time zone to the user.
Default value rationale:
It is a assumed that typically, the admin user, and site visitors, and server location are (mostly) all in the same timezone.
The exception to this would be if the server has many intl users.
Use case for fixed offset from UTC:
If the user wants times displayed as UTC, then offset +00:00 my be set.
Note that using the utc modifier would not be correct as that would treat the UTC time output by the datetime() function as local time then adjust by the system offset.
Implementation
When displaying timestamps, first call datetime(date) to get the time in UTC.
As the stored date time strings include a fixed offset, this always produces the same moment in time as UTC text repr
Next, call date or time with the user configured modifier.
If offset supplied, the time is adjusted by the offset
If localtime supplied, the time is adjusted by the current system offset
Any calls to date or time in the GROUP BY clause are similarly adjusted so that bucket counts accurately reflect the displayed values.
This resolves the feature request, by allowing the user to set a "home" timezone (well, fixed offset rather than a zone due to sqlite limitations).
Further, the user may set to localtime, which is the true server time zone which will automatically adjust for daylight saving time if applicable.
Further improvements
Q. What if true timezone support is required - not just fixed UTC offset?
A. Due to limitations in sqlite3 builtin date/time methods, we can not adjust timestamps by arbitrary timezone, only by fixed UTC offset or by the system timezone. However, if we were to pass the timestamps as UTC to the front end without calling date or time functions, then we could split the timestamp into date only and time only parts in JavaScript and apply arbitrary timezone adjustments and not just fixed offset.
Fixes #28
Summary
Timestamps are stored in db as strings (as far as I can tell), and include the date time offset from UTC.
The Problem
Timestamps were formerly displayed as output of sqlite
date()andtime()functions, which causes the times to be displayed as UTC regardless of server timezone.The Solution
Given the following behaviour of sqlite3:
From https://www.sqlite.org/lang_datefunc.html:
localtimemodifier converts a UTC time to the text repr of the same moment in local time.utcmodifier converts a local time to the text repr of the same moment in UTC.+HH:MMor-HH:MMmodifier adjusts the time by the offset specified.PR Scope
This PR supports the sqlite3
+/-HH:MMandlocaltimemodifiers.localtime- which lets sqlite adjust the time by the the system timezone (ie, will correctly reflect daylight savings time if applicable).Default value rationale: It is a assumed that typically, the admin user, and site visitors, and server location are (mostly) all in the same timezone.
Use case for fixed offset from UTC: If the user wants times displayed as UTC, then offset +00:00 my be set.
utcmodifier would not be correct as that would treat the UTC time output by thedatetime()function as local time then adjust by the system offset.Implementation
When displaying timestamps, first call
datetime(date)to get the time in UTC.Next, call
dateortimewith the user configured modifier.Any calls to
dateortimein the GROUP BY clause are similarly adjusted so that bucket counts accurately reflect the displayed values.This resolves the feature request, by allowing the user to set a "home" timezone (well, fixed offset rather than a zone due to sqlite limitations).
Further, the user may set to localtime, which is the true server time zone which will automatically adjust for daylight saving time if applicable.
Further improvements
Q. What if true timezone support is required - not just fixed UTC offset? A. Due to limitations in sqlite3 builtin date/time methods, we can not adjust timestamps by arbitrary timezone, only by fixed UTC offset or by the system timezone. However, if we were to pass the timestamps as UTC to the front end without calling
dateortimefunctions, then we could split the timestamp into date only and time only parts in JavaScript and apply arbitrary timezone adjustments and not just fixed offset.