From: Tom Lane <tgl@sss.pgh.pa.us>
To: ralph.holz@gmail.com
cc: pgsql-docs@postgresql.org
Subject: Re: Requesting clarifying details on extract(epoch from timestamp)
In-reply-to: <20170818060506.27369.84309@wrigleys.postgresql.org>
References: <20170818060506.27369.84309@wrigleys.postgresql.org>
Comments: In-reply-to ralph.holz@gmail.com
	message dated "Fri, 18 Aug 2017 06:05:06 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <20022.1503951274.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 28 Aug 2017 16:14:34 -0400
Message-ID: <20023.1503951274@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

ralph.holz@gmail.com writes:
> Specifically, for the case of 'extract epoch', the docs state:

> "for date and timestamp values, the number of seconds since 1970-01-01
> 00:00:00 local time"

> I ran an experiment, importing a timestamp '2016-06-26 20:01:38' with
> default time zone 'localtime', which is AEST in my case.

> SELECT id, extract(epoch FROM not_before) FROM bla;
> -> 1466971298

> Now I switch to timezone =3D 'UTC' in postgresql.conf. Confirming with SH=
OW
> TIMEZONE that I am now in the default UTC timezone in my session. Same
> query:
> -> 1466971298

> In other words, it seems that the function extract(epoch from timestamp)
> considered the timestamp field to be in UTC in both cases. I find it hard=
 to
> reconcile that with the docs.

I don't see anything particularly wrong there; the misconception I think
you're harboring is not with extract(epoch), but with what "local time"
means.  When you change TimeZone, you're changing the implied origin
for non-tz timestamps.  Here's an example:

regression=3D# show timezone;
  TimeZone=20=20
------------
 US/Eastern
(1 row)

regression=3D# create table foo (f1 timestamptz, f2 timestamp);
CREATE TABLE
regression=3D# insert into foo values('1970-01-01 00:00+00', '1970-01-01 00=
:00');
INSERT 0 1
regression=3D# select * from foo;
           f1           |         f2=20=20=20=20=20=20=20=20=20=20
------------------------+---------------------
 1969-12-31 19:00:00-05 | 1970-01-01 00:00:00
(1 row)

regression=3D# select extract(epoch from f1), extract(epoch from f2) from f=
oo;
 date_part | date_part=20
-----------+-----------
         0 |         0
(1 row)

regression=3D# set timezone =3D utc;
SET
regression=3D# select * from foo;
           f1           |         f2=20=20=20=20=20=20=20=20=20=20
------------------------+---------------------
 1970-01-01 00:00:00+00 | 1970-01-01 00:00:00
(1 row)

regression=3D# select extract(epoch from f1), extract(epoch from f2) from f=
oo;
 date_part | date_part=20
-----------+-----------
         0 |         0
(1 row)

Adjusting "timezone" changed the displayed form of the timestamptz value,
but not its underlying stored value, which was UTC anyway.  It didn't
change the displayed form of the timestamp value, since that's implicitly
relative to the epoch for the current timezone.  In the same way, both
entries were exactly 0 seconds past their respective epochs before
changing "timezone", and they're still exactly 0 seconds past their
respective epochs afterwards.

			regards, tom lane


--=20
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs