Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone

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.

https://www.postgresql.org/message-id/flat/20120125152225.GA979%40depesz.com

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

On Wed, Dec 30, 2020 at 4:29 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dana Burd <djburd(at)gmail(dot)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
>