Time values can be stored in databases in a confusing variety of formats. These get mapped to a smaller set of Haskell types when you use the
persistent database interface. This is a short tutorial to look at the various possibilities, and recommend some best practices.
Besides the usual
TIME types, which represent part of a complete time value, the usual types are
TIMESTAMP WITH TIMEZONE.
TIME are handled quite well by the
Complete - or near complete - time values like
TIMESTAMP WITH TIMEZONE are a bit more complicated.
The normal mapping for a
UTCTime. This is a timestamp with an implicit timezone of UTC. There is no implicit timezone on the SQL side. If anything, it's assumed to be local time. However, that information isn't available in the database. This mapping is used because it provides a proper two-way mapping, but the semantics on the Haskell side may be wrong. Best practices will prevent that.
These are mapped to
ZonedTime. This is the preferred map if you need to deal with time zones. However, the PostGRESql database actually stores values in UTC and translates to the local time zone on output. The local time zone setting in the library is UTCTime, so these give you the same effect as using UTCTime, and will be translated to that in a later version of the library.
As with any complex data structure, the best practice for storing time is to convert the values to a canonical form on input, and convert back if needed for display.
If the time zones that go with the recorded time are important, then the only choice is to use
TIMESTAMP WITH TIMEZONE and
ZonedTime. Comparing values and calculating intervals requires conversion to a command - and preferably monotonic - timezone.
If you just need to know the order of events that are being timestamped, or intervals between them, the converting all values to UTC and using
UTCTime works very well.
None of the time types here deal properly with leap seconds. If you are dealing with long intervals that need more than one or two second accuracy, these types may not be appropriate for your use.