On 2017/03/08 10:40 PM, Paul Sanderson wrote:
The vast majority of dates I see in SQLite databases are unix epoch integer
times (seconds since 1/1/1980) with unix milli seconds a close second.
Efficient to store, sort and do date arithmetic on but need to be converted
to display.
I also see unix nano seconds, 100 nano seconds, windows filetimes, chrome
dates and NSDates/MacAbsolute very regularly.
Interestingly I rarely see dates stored in ISO8601 format/text
Because every programmer is a self-proclaimed optimization genius!
When speed and space counts, I too opt for the Unix epochs or nano
seconds, but the luxury of being able to understand a human-readable
date and time exactly right from viewing it in your favourite DB manager
or even CLI, is priceless. It saves so much time when debugging or
looking for a problem or fixing a client's mistake some time after it
all gone live and you have forgotten all about the internals of the system.
If speed/space isn't critical, I always advise ISO8601 dates, typically
stored (in SQLite anyway) in a NUMERIC typed column.
NUMERIC is suggested by the documentation for dates, but I am unsure if
it gains any actual optimized nature over a STRING.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
