Hi,
Integer unix timestamps are only accurate to one second, where ISO8601
(at least as implemented by SQLite) can go to 1 millisecond. Also you
have to know the epoch to interpret a unix timestamp - not everybody
uses 1970-01-01 00:00:00. Will people be able to figure out what the
field value means when somebody discovers your data in 100 years?
The SQLite implementation is *not* subject to the 2038-01-19 integer
overflow problem. But other systems that might interact with SQLite
are and so that is something to keep in mind as well.
On the other hand, integer timestamps take up less space in the file.
The third option is a fractional julian day number stored as an 8-byte
floating point value.
Do you want to store timestamps from the future (for example, a
calendar-style application) or will all your timestamps always represent
points in the past?
If you want to be able to accurately store future timestamps then the
integer seconds and julian day number representations require some extra
metadata.
Whilst past timestamps can always be deterministically converted from their
local timezone to UTC and back again, this is not the case for timestamps
in the future.
This is because the timezone and daylight saving rules change from
time-to-time. In the integer seconds and julian day number representations,
future timestamps must be stored in local time along with their timezone so
that information is not lost.
In a perfect world you would store local time and location (rather than
timezone) as places do also occasionally move timezone. However, this is
difficult to work with because databases that map from suitably represented
locations to timezones are more difficult to come by than databases that
describe the relationships between timezones.
This is not a hypothetical problem or one where the error is small.
Bangladesh cancelled their daylight savings observances in 2010. In 2011
Russia made their daylight savings time permenant; moving onto it in the
Spring and never moving back.
If you had prematurely converted timestamps from these places to UTC then
your error would be measured on the order of an hour.
In 1994 Eastern Kiribati crossed the International Date Line
(bureaucratically speaking). If you had prematurely converted a timestamp
from there then your error would be on the order of a day!
Doing consistent arithmentic on future dates is left as an exercise for the
reader (sorry)!
Regards,
@ndy
--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
