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: 
 <CADYUPHOhiP0R7BbFwepsfsuD+d0B3jH4zkAZjOogzGtuR_dg9Q@mail.gmail.com>
References: <16797-f264b0b980b53b8b@postgresql.org>
 <2396654.1609354902@sss.pgh.pa.us>
 <CADYUPHNmh120OVhgkr897+SAyHdPm8=nj+mnwcY9d-fyWMJLSw@mail.gmail.com>
 <2405485.1609363754@sss.pgh.pa.us>
 <CADYUPHOhiP0R7BbFwepsfsuD+d0B3jH4zkAZjOogzGtuR_dg9Q@mail.gmail.com>
Comments: In-reply-to Dana Burd <djburd@gmail.com>
	message dated "Thu, 31 Dec 2020 14:27:31 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2527204.1609521492.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 01 Jan 2021 12:18:12 -0500
Message-ID: <2527205.1609521492@sss.pgh.pa.us>
Precedence: bulk

Dana Burd <djburd@gmail.com> writes:
> Can I suggest a slight alteration in the (9.9.1. EXTRACT) epoch
> documentation to help others:

> "For timestamp with time zone values, the number of seconds since
> 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values=
,
> the number of seconds since 1970-01-01 00:00:00 UTC (date and timestamp
> will assume UTC regardless of local timezone in order to maintain
> immutability - one may explicitly cast timestamp to timestamptz to assum=
e a
> different timezone); for interval values, the total number of seconds in
> the interval"

Hmm, that's not really right either; it appears to imply that the epoch
calculation is timezone-aware, which it specifically isn't for date and
timestamp cases.  An example (presuming US DST rules):

regression=3D# select extract(epoch from date '2020-03-09') - extract(epoc=
h from date '2020-03-08');
 ?column? =

----------
    86400
(1 row)

regression=3D# select extract(epoch from timestamp '2020-03-09') - extract=
(epoch from timestamp '2020-03-08');
 ?column? =

----------
    86400
(1 row)

regression=3D# select extract(epoch from timestamptz '2020-03-09') - extra=
ct(epoch from timestamptz '2020-03-08');
 ?column? =

----------
    82800
(1 row)

The last case knows that there was a DST transition in between, the first
two don't take that into account.  (You could argue that this is more a
property of the types' input conversion routines than of extract() itself,
but I think the point is valid anyway.)

Perhaps a better phrasing is "for date and timestamp values, the nominal
number of seconds since 1970-01-01 00:00:00, without regard to timezone
or daylight-savings rules".

			regards, tom lane