Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dana Burd <djburd@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2020-12-30T21:29:14Z
Lists: pgsql-bugs 
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

Commits

  1. Doc: improve explanation of EXTRACT(EPOCH) for timestamp without tz.

  2. Doc: spell out comparison behaviors for the date/time types.