a sqlite date trick
This text discusses an alternative way to store sqlite date-times that offers two desirable features:
- sorting date-times in a true universal order (UTC order)
- allowing sqlite calculations to work with local date-times when needed
From sqilte's documentation, sqlite accepts date-times in a few different formats. For this trick, we'll use the most complete format on offer: the format that contains the most data, the highest time precision, and most time-zone offset information. Using this format, sqlite suggests that date-times can be stored like so:
<yyyy-mm-dd> <hh:mm:ss.mmm> <+/-hh:mm>
where the <yyyy-mm-dd> <hh:mm:ss.mmm> is the local time (the time that appears on local clocks) and the <+/-hh:mm> is the offset from UTC to the local time. That is, it's the hours and minutes you need to add or subtract to UTC to get the local time.
For example: 2013-10-07 04:23:19.120 -04:00 means that 2013-10-07 04:23:19.120 is exactly what it says on the local clocks for the place and time that date was written. And to get to UTC, you must add four hours to the local time. (Even though the offset says minus 4 hours: -04:00)
Notice that when storing date-times, in theory, you have the option of storing them using 4 different permutations of the date-time and offset:
1. <utc date-time> <utc-to-local offset>
2. <utc date-time> <local-to-utc offset>
3. <local date-time> <utc-to-local offset>
4. <local date-time> <local-to-utc offset>
Next, I will explain each way the above date-times can be represented in theory, and include notes about where each is used in various standards, databases and programming languages and the strengths and weaknesses of each. For all date-time examples below I'll use: August 1, 2024 at 8:00am in the America/New_York time zone.
style #1 - useful and logical
<utc date-time> <utc-to-local offset>
2024-08-01 12:00:00.000 -04:00
This is the most logical in my opinion because it has two excellent properties: First, if there is a list of these date-times, they can be sorted in the correct chronological utc-based order. Second, the raw format can be easilly read like so "the local time is this utc time minus 4 hours". Unfortunately this very logical and useful format is not used anywhere in standards, databases, or programming languages.
style #2 - the least logical, but has a sqlite advantage (the sqlite date trick)
<utc date-time> <local-to-utc offset>
2024-08-01 12:00:00.000 +04:00
Although this still has the sorting benefits of #1, this is quite illogical because the raw data must be read as: "the utc time shown is 4 hours ahead of the local time, so take this utc time and subtract 4 hours". However the advantage is that sqlite will automatically convert this time to the "local time" when using its built-in date-time functions. That is, it will simply "apply" the offset part to the date-time part and return the result. This means that strftime('%F %T','2024-08-01 12:00:00.000 +04:00') will equal 2024-08-01 08:00:00. Note that a down-side to using this sqlite trick will break the 'localtime' parameter that could normally be able to be passed to strftime(). strftime('...','2024-08-01 12:00:00.000 +04:00','localtime') will return an incorrect time which has no relation to reality.
style #3 - the standard way - illogical as it may be
<local date-time> <utc-to-local offset>
2024-08-01 08:00:00.000 -04:00
The advantage to this is that when looking at the raw data, one can easily see the local time and not have to do any math unless the utc is desired. It is illogical because it must be read like so: "the local time shown is 4 hours behind utc, so to get utc, add 4 hours to the local time shown". Sqlite expects date-times to be in this format, such that strftime('%F %T','2024-08-01 08:00:00.000 -04:00', 'localtime') in America/New_York will equal 2024-08-01 08:00:00. Given that this is the worst of all formats, it's unfortunate that all standards, databases and programming languages use it. The most famous time format standard is ISO 8601, which also uses this basic format (although in a less readable form without spaces: 2024-08-01T08:00:00.000-04:00)
style #4 - both friendly and logical
<local date-time> <local-to-utc offset>
2024-08-01 08:00:00.000 +04:00
This has the advantage of seeing the local time directly in the raw data, and the conversion to utc is also very logical. It can be read as "the local time is shown, and to get utc, add 4 hours". Unfortunately this friendly and logical format is not used anywhere in standards, databases, or programming languages.
the trick
although sqlite officially supports dates in format style #3: <local date-time> <utc-to-local offset>, you can also use style #2: <utc date-time> <local-to-utc offset> and in most cases, it simply works as desired.
sorting
Style #2: <utc date-time> <local-to-utc offset> allows the data to be sorted in UTC order.
searching
Style #2: <utc date-time> <local-to-utc offset> allows all dates in your database to be searched for using a date range between two utc date-times.
Here's an example from the enteredDate column of a table called transfer with dates around August 1, 2024 in America/New_York. The dates are stored as format style #2: <utc date-time> <local-to-utc offset>:
enteredDate // (local date-time)
2024-07-31 12:00:00.000 +04:00 // 2024-07-31 08:00:00.000
2024-08-01 13:00:00.000 +04:00 // 2024-08-01 09:00:00.000
2024-08-01 16:00:00.000 +04:00 // 2024-08-01 12:00:00.000
2024-08-02 02:00:00.000 +04:00 // 2024-08-01 22:00:00.000
2024-08-02 13:00:00.000 +04:00 // 2024-08-02 09:00:00.000
For this example query, we want any date-times that fall exactly on the local date of August 1st, 2024 in America/New_York:
select * from
transfer
where
enteredDate between '2024-08-01 04:00:00.000 +00:00' and '2024-08-02 03:59:59.999 -99:99'
The above query returns:
enteredDate // (local time)
2024-08-01 13:00:00.000 +04:00 // 2024-08-01 09:00:00.000
2024-08-01 16:00:00.000 +04:00 // 2024-08-01 12:00:00.000
2024-08-02 02:00:00.000 +04:00 // 2024-08-01 22:00:00.000
grouping
Take the same transfer table as above:
enteredDate // (local date-time)
2024-07-31 12:00:00.000 +04:00 // 2024-07-31 08:00:00.000
2024-08-01 13:00:00.000 +04:00 // 2024-08-01 09:00:00.000
2024-08-01 16:00:00.000 +04:00 // 2024-08-01 12:00:00.000
2024-08-02 02:00:00.000 +04:00 // 2024-08-01 22:00:00.000
2024-08-02 13:00:00.000 +04:00 // 2024-08-02 09:00:00.000
Let's say you wanted to group the data by the local day, you would need sqlite to calculate the local date-time from the stored date formatted with style #2: <utc date-time> <local-to-utc offset>. A quirk of the sqlite functions like strftime() is that it will return local times if given date strings using dates formatted using style #2: <utc date-time><local-to-utc offset>. To group by the local day, use this:
select
strftime('%F', enteredDate) as localDay,
count(*) as ct
from
transfer
group by
localDay
The results would be:
localDay ct
2024-07-31 1
2024-08-01 3
2024-08-02 1
If for some reason you wanted to group by the utc day, you would need to extract it yourself. To group by the utc day, you could do this:
select
substr(enteredDate,1,10) as utcDay,
count(*) as ct
from
transfer
group by
localDay
The results would be:
utcDay ct
2024-07-31 1
2024-08-01 2
2024-08-02 2
one caveat
functions like strftime() ordinarily take the date-time in format style #3: <local date-time> <utc-to-local offset> and return its result in utc. For example:
strftime('%F %T', '2024-08-01 09:00:00.000 -04:00')
returns
'2024-08-01 13:00:00'
When using strftime() in the "normal" way (with style #3), you have the option to pass a 'localtime' parameter to it in order to make strftime() convert the time to the local date-time. For example:
strftime('%F %T', '2024-08-01 09:00:00.000 -04:00', 'localtime')
returns
2024-08-01 09:00:00
if the OS is in the America/New_York time zone. Just know that when using strftime() with style #2: <utc date-time> <local-to-utc offset>, the 'localtime' parameter will cause strftime() to return a crazy meaningless date-time, and therefore cannot be used directly.
If for some reason you really need the date-time functions to return the local time (the local time of the OS that is…). You can still do it using date style #2 <utc date-time> <local-to-utc offset> by simply removing the offset:
strftime('%F %T', substr('2024-08-01 13:00:00.000 +04:00',1,23),'localtime'))
returns
2024-08-01 09:00:00
if run on an OS in the America/New_York time zone.
conclusion
Sqlite expects date-times in the format: <local date-time> <utc-to-local offset>, but this leaves dates sorted by the local time in the database. Instead we can choose to store date-times in the format: <utc date-time> <local-to-utc offset>, which improves sorting. If we want the local time for sql calculations (like for where clauses and group-bys), we can pass dates formatted in the <utc date-time> <local-to-utc offset> style to sqlite functions like strftime() and it will return date-times in local time.