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