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)
Date: Mon, 28 Aug 2017 16:14:34 -0400
Message-ID: <20023.1503951274@sss.pgh.pa.us> (raw)
In-Reply-To: <20170818060506.27369.84309@wrigleys.postgresql.org>
References: <20170818060506.27369.84309@wrigleys.postgresql.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
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 = 'UTC' in postgresql.conf. Confirming with SHOW
> 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=# show timezone;
TimeZone
------------
US/Eastern
(1 row)
regression=# create table foo (f1 timestamptz, f2 timestamp);
CREATE TABLE
regression=# insert into foo values('1970-01-01 00:00+00', '1970-01-01 00:00');
INSERT 0 1
regression=# select * from foo;
f1 | f2
------------------------+---------------------
1969-12-31 19:00:00-05 | 1970-01-01 00:00:00
(1 row)
regression=# select extract(epoch from f1), extract(epoch from f2) from foo;
date_part | date_part
-----------+-----------
0 | 0
(1 row)
regression=# set timezone = utc;
SET
regression=# select * from foo;
f1 | f2
------------------------+---------------------
1970-01-01 00:00:00+00 | 1970-01-01 00:00:00
(1 row)
regression=# select extract(epoch from f1), extract(epoch from f2) from foo;
date_part | date_part
-----------+-----------
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
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-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, ralph.holz@gmail.com
Subject: Re: Requesting clarifying details on extract(epoch from timestamp)
In-Reply-To: <20023.1503951274@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