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
Date: Wed, 30 Dec 2020 16:29:14 -0500
Message-ID: <2405485.1609363754@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, djburd@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone
In-Reply-To: <2405485.1609363754@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox