From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dana Burd <djburd@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local
 timezone
In-reply-to: 
 <CADYUPHNmh120OVhgkr897+SAyHdPm8=nj+mnwcY9d-fyWMJLSw@mail.gmail.com>
References: <16797-f264b0b980b53b8b@postgresql.org>
 <2396654.1609354902@sss.pgh.pa.us>
 <CADYUPHNmh120OVhgkr897+SAyHdPm8=nj+mnwcY9d-fyWMJLSw@mail.gmail.com>
Comments: In-reply-to Dana Burd <djburd@gmail.com>
	message dated "Wed, 30 Dec 2020 16:02:30 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2405484.1609363754.1@sss.pgh.pa.us>
Date: Wed, 30 Dec 2020 16:29:14 -0500
Message-ID: <2405485.1609363754@sss.pgh.pa.us>
Precedence: bulk

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