I found the original thread that led to the change in 9.2, which comes down to maintaining immutability when executing the extract epoch function - timestamp_part() is marked immutable, yet the input 'timestamp' was changing based on the local timezone setting. Your notes above alluded to that, but detail from the thread was helpful.
Can I suggest a slight alteration in the (9.9.1. EXTRACT) epoch documentation to help others:
"For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (date and timestamp will assume UTC regardless of local timezone in order to maintain immutability - one may explicitly cast timestamp to timestamptz to assume a different timezone); for interval values, the total number of seconds in the interval"
And include in the second position of the example code box:
SELECT EXTRACT(EPOCH FROM '2001-02-16 20:38:40.12'::TIMESTAMP AT TIME ZONE 'PST8PDT');
Result: 982384720.12
Thanks for the information and maintaining communication history
-dana
Dana Burd <djburd@gmail.com> writes:
> Wondering then, when local timezone is set to anything other than UTC, why
> does:
> '01/01/1970 00:00:00'::timestamp =
> '01/01/1970 00:00:00'::timestamptz
> To compare these datetime values, postgres is making an implicit cast of
> some kind - and if they are equal then their epoch values should be equal
> as well.
For comparison purposes, the timestamp value is taken as being in your
local zone (the one specified by the timezone GUC). The timestamptz
value is just an absolute UTC instant. The above example is a bit
confusing since '01/01/1970 00:00:00'::timestamptz is *also* read as
being in your local zone --- but that happens when the literal constant
is parsed, rather than during execution of the comparison. Presuming
EST5EDT zone, '01/01/1970 00:00:00'::timestamptz really means
'1970-01-01 00:00:00-05'::timestamptz which is equivalent to
'1970-01-01 05:00:00+00'::timestamptz, and then we have to convert
the timezone at runtime to do a meaningful comparison.
I'd thought this was adequately documented already, but perhaps not.
There are a couple of passing references to timestamp<->timestamptz
conversions in section 8.5, but really section 9.9 ought to cover
datetime comparison behavior, and it doesn't say anything about this.
regards, tom lane
