Overview

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.

Database types.

Besides the usual DATE and TIME types, which represent part of a complete time value, the usual types are TIMESTAMP and TIMESTAMP WITH TIMEZONE. DATE and TIME are handled quite well by the Day and TimeOfDay types.

Complete - or near complete - time values like TIMESTAMP and TIMESTAMP WITH TIMEZONE are a bit more complicated.

TIMESTAMP without a timezone

The normal mapping for a TIMESTAMP is 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.

TIMESTAMP WITH TIMEZONE

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.

Best Practices

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.

TIMESTAMP WITH TIMEZONE

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.

TIMESTAMP without a 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 TIMESTAMP and UTCTime works very well.

A note on intervals

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.