[database-devel] UTCTime and Timestamp Without Time Zone

Leon Smith leon.p.smith at gmail.com
Sat Jun 2 19:39:50 CEST 2012

I've been reworking postgresql-simple's out-of-box support for time types,
 and as I've come to a better understanding of the issues involved,  I've
come to realize that I've made a small blunder.   Namely,  PostgreSQL's
"timestamp without time zone" should not be automatically convertible to

The "timestamp with time zone" type represents a clear, unambiguous point
in time.   It corresponds quite nicely to Data.Time.ZonedTime and
Data.Time.UTCTime.     By contrast,  timestamp without time zone can only
be understood in some larger context,  specific to the application and
perhaps even the particular instance of the application.    And even with
that context,  it may turn out to be difficult (or impossible) to decide
what point in time such a timestamp actually represents.   This corresponds
quite nicely to Data.Time.LocalTime.      So,  in essence, this conversion
assumes that LocalTime are always UTCTime,  and promoting an ambiguous time
to an unambiguous type by assuming one particular time zone.

So, do I plan on removing this conversion from postgresql-simple sometime
in the near future.   I do know that this impacts persistent-postgreql and
may well impact others,   but I think it's something that needs to be done.
  I do see that persistent-postgresql does have an upper bound set,  so I
will increment postgresql-simple's version to 0.2  when this functionality
is removed.

Part of the rework involves writing my own parsers and printers.  These
should prove to be significantly faster than those provided by Data.Time,
 but I don't have numbers yet. (and indeed, the printers aren't yet done.)
  Also,  it introduces types to handle 'infinity' and '-infinity'
timestamps and dates.    And in the process of "reverse engineering"
PostgreSQL's time format,   I ended up doing a binary search by hand and
discovered the date 1883-Nov-18,  which has a particular significance to
time geeks.    So I've definitely learned some things in this process.

Anyone who is interested in reviewing the work I've done so far should
check out my "time" branch on github.   I would appreciate feedback.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.haskell.org/pipermail/database-devel/attachments/20120602/e29511ec/attachment.htm>

More information about the database-devel mailing list