Re: Revisiting extract(epoch from timestamp)

On Mon, Apr 9, 2012 at 1:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> A long time ago, we had this bug report:
> http://archives.postgresql.org/pgsql-bugs/2003-02/msg00069.php
> 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
> 191ef2b407f065544ceed5700e42400857d9270f).
>
> Recent discussion makes it seem like this was a bad idea:
> http://archives.postgresql.org/pgsql-general/2012-01/msg00649.php
> 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
> sensitive.
>
> 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.

+1 to all the above.

> 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.

If somebody needs it I'd probably be in favor of doing it. I'm not
sure I'd do it on spec.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company