From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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 14:01:42 -0500
Message-ID: <2396654.1609354902@sss.pgh.pa.us> (raw)
In-Reply-To: <16797-f264b0b980b53b8b@postgresql.org>
References: <16797-f264b0b980b53b8b@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> EXTRACT(EPOCH FROM timestamp) should be using the local timezone
No, type timestamp is explicitly *not* timezone aware. If you use
timestamptz (a/k/a timestamp with time zone) you will get the
answer you want.
> -- Expected results (seen from PostgreSQL 9.1.11):
> # SET TIME ZONE 'EST5EDT'; select extract(epoch from ('01/01/1970
> 00:00:00'::timestamp));
This was a bug, cf 9.2.0 release notes [1]:
Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch
from local midnight, not UTC midnight (Tom Lane)
This change reverts an ill-considered change made in release 7.3.
Measuring from UTC midnight was inconsistent because it made the
result dependent on the timezone setting, which computations for
timestamp without time zone should not be. The previous behavior
remains available by casting the input value to timestamp with time
zone.
regards, tom lane
[1] https://www.postgresql.org/docs/release/9.2.0/
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: <2396654.1609354902@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