On Wednesday, 6 February, 2019 12:55, Ben Asher wrote:
> Hi there! We're having a debate at my company about date storage in
> SQLite. SQLite has builtin support for ISO8601 in its date functions,
> so some folks have started storing dates as ISO8601 SQLite-compatible
> date strings.
> Are there pitfalls to storing dates this way compared to a unix
> timestamp?
Well, the most common pitfalls are the failure to store "instant time strings"
and instead to only store ambiguous data. This is amplified by the fact that
the built-in datetime functions only produce (output) ambiguous timestrings and
then only with limited precision (ie, usually not suitable for putting back
into a database). You can work around this by always storing such strings in
UT1 (UTC).
For example, a full IS8601 "instant time string" looks something like:
2019-02-06T15:45:47.305872603-07:00
This format can be handled on input to the datetime functions producing a
correct UT1 instant time. The input routines are general enough that they can
accept varients such as a space in place of the "T", a space before the + or -
timezone specifier, and an arbitrary number of decimal seconds (though the
internal storage is Julian milliseconds, so the internal representation is
limited to millisecond precision. Z can be used to denote +0:00 and -0:00.
The "output" formats however are somewhat lacking as they do not specify
decimal seconds (unless you use your own strftime format string) and do not
include timezone information (thus making the time ambiguous).
The "localtime" and "utc" modifiers are processed by the underlying OS and are
subject to the vagaries of such handling on the OS, which varies by OS. For
example, Windows conversions are only accurate for the current daylight rules.
Linux works properly. I do not know about other OSes. "localtime" means the
timezone of the computer.
Storing a "Unix Timestamp" or other numeric format of course is not subject to
these vagaries since it is an offset in POSIX seconds (or days) from a fixed
epoch and that epoch is almost always UT1 (UTC). Note that you can store
floating point unix timestamps, you just need to tell the internal datetime
functions that they are working with unix epochs.
Storing the stamps as ISO8601 strings has the advantage that anyone can
recognize them and use the standard SQLite3 shell tool to work with them.
Numeric epoch offsets are not so "human readable" and you need to be more
knowledgeable to see and work with them using the shell tools, but they are
harder to screw up.
> I'm curious to know if anyone has experience and would highly recommend
> sticking to one or the other for a particular reason. I'd also be
> grateful if anyone could point me to any articles exploring this subject.
I prefer working with numeric epoch dates myself because they are (a) more
compact and (b) inherently sortable. ISO8601 strings are "mostly sortable" so
long as they all have the same offset from UT1 -- the timestring format though
does require about 31 bytes to store the same information as can be stored in
an 8-byte float.
I have some patches that modify the datetime function library to always output
full instant timestrings and that can use the Olsen database (which needs to be
loaded into the database) to do timezone conversions inside SQLite3 and to
maintain the offsets within the datetime objects. (Unixtime uses the VDBE
current statement time and simply convert the internal Julian Milliseconds into
Unix Epoch Seconds as a double. UnixInstant calls the
GetSystemTimePreciseAsFileTime (windows) API and returns the current machine
time as a unix epoch float (it does the Precise variant gets the currrent time,
not the time as of the last tick).
>sqlite tz.db
SQLite version 3.27.0 2019-02-06 01:18:36
Enter ".help" for usage hints.
sqlite> select datetime('now', 'Canada/Mountain');
2019-02-06 16:09:08.039 -07:00
sqlite> select datetime('now', 'Europe/Moscow');
2019-02-07 02:09:23.943 +03:00
sqlite> select unixtime(), unixinstant();
1549496112.409|1549496112.40904
sqlite> select datetime(unixinstant(), 'unixepoch', 'America/Regina');
2019-02-06 17:35:46.849 -06:00
sqlite>
Or using the geopoly extension find the Olsen timezone for a given lat/long:
>gettz -110 50
3.7.2 (tags/v3.7.2:9a3ffc0492, Dec 23 2018, 23:09:28) [MSC v.1916 64 bit
(AMD64)]
SQlite3 Source ID 2019-02-06 01:18:36
3087a0c31e9fbfaedb7cf10a2fda59bc22318ff178614aefcc00ac83d9a7alt2
Find timezone at Location -110.000000 50.000000
Exact Row(tzid='America/Regina') 00:00:00.004773
st = time.time()
for row in db.cursor().execute("""select tzid
from tz_geopoly
where geopoly_overlap(_shape,
geopoly_regular(?1, ?2, 0.0002699785, 8))
and geopoly_contains_point(_shape, ?1, ?2)
order by abs(geopoly_area(_shape)),
instr(lower(tzid), 'etc/'), tzid
limit 1;""", (lo, la)):
print('Exact', row, (datetime.datetime.min +
datetime.timedelta(seconds=time.time() - st)).time().isoformat())
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
