Thread: [DOCS] Requesting clarifying details on extract(epoch from timestamp)
[DOCS] Requesting clarifying details on extract(epoch from timestamp)
From
ralph.holz@gmail.com
Date:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.6/static/functions-datetime.html Description: Hi everyone, I would like to request a clarifying statement in the docs: https://www.postgresql.org/account/comments/new/9.6/functions-datetime.html/ 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. Am I misreading something or is that something that should be fixed in the 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