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: djburd@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Date: 2020-12-30T19:01:42Z
Lists: pgsql-bugs 
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/

Commits

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

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