Skip site navigation (1) Skip section navigation (2)

Revisiting extract(epoch from timestamp)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Revisiting extract(epoch from timestamp)
Date: 2012-04-09 17:30:43
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
A long time ago, we had this bug report:
in consequence of which, I changed timestamp_part() so that it would
rotate a timestamp-without-timezone from the local timezone to GMT
before extracting the epoch offset (commit

Recent discussion makes it seem like this was a bad idea:
The big problem is that timestamp_part() is marked as immutable, which
is a correct statement for every other field type that it can extract,
but wrong for epoch if that depends on the setting of the timezone GUC.
So if we leave this behavior alone, we're going to have to downgrade
timestamp_part() to stable, which is quite likely to break applications
using it in index expressions.  Furthermore, while you could still get
the current behavior by explicitly casting the timestamp to timestamptz
before extracting the epoch, there is currently no convenient way to get
a non-timezone-aware epoch value from a timestamp.  Which seems rather
silly given that one point of the timestamp type is to not be timezone

So I'm kind of inclined to revert that old change.  Back in the day
we thought it was a relatively insignificant bug fix and applied it in a
minor release, but I think now our standards are higher and we'd want to
treat this as a release-notable incompatibility.

The above-linked discussion also brings up a different point, which is
that extracting the epoch from a timestamptz is an immutable operation,
but because it's provided in the context of timestamptz_part we can only
mark it stable.  (That is correct because the other cases depend on the
timezone setting ... but epoch doesn't.)  It seems like it might be
worth providing a single-purpose function equivalent to extract(epoch),
so that we could mark it immutable.  On the other hand, it's not
entirely apparent why people would need to create indexes on the epoch
value rather than just indexing the timestamp itself, so I'm a tad less
excited about this angle of it.


			regards, tom lane


pgsql-hackers by date

Next:From: Robert HaasDate: 2012-04-09 17:32:39
Subject: Re: bug in fast-path locking
Previous:From: Atri SharmaDate: 2012-04-09 17:25:58
Subject: Re: [JDBC] Regarding GSoc Application

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group