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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
