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.
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.
Thoughts?
regards, tom lane
