Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone
Дата
Msg-id 2405485.1609363754@sss.pgh.pa.us
обсуждение исходный текст
Ответ на BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone  (PG Bug reporting form <noreply@postgresql.org>)
Ответы Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone
Список pgsql-bugs
Dana Burd <djburd@gmail.com> writes:
> Wondering then, when local timezone is set to anything other than UTC, why
> does:
> '01/01/1970 00:00:00'::timestamp =
> '01/01/1970 00:00:00'::timestamptz

> To compare these datetime values, postgres is making an implicit cast of
> some kind - and if they are equal then their epoch values should be equal
> as well.

For comparison purposes, the timestamp value is taken as being in your
local zone (the one specified by the timezone GUC).  The timestamptz
value is just an absolute UTC instant.  The above example is a bit
confusing since '01/01/1970 00:00:00'::timestamptz is *also* read as
being in your local zone --- but that happens when the literal constant
is parsed, rather than during execution of the comparison.  Presuming
EST5EDT zone, '01/01/1970 00:00:00'::timestamptz really means
'1970-01-01 00:00:00-05'::timestamptz which is equivalent to
'1970-01-01 05:00:00+00'::timestamptz, and then we have to convert
the timezone at runtime to do a meaningful comparison.

I'd thought this was adequately documented already, but perhaps not.
There are a couple of passing references to timestamp<->timestamptz
conversions in section 8.5, but really section 9.9 ought to cover
datetime comparison behavior, and it doesn't say anything about this.

            regards, tom lane



В списке pgsql-bugs по дате отправления:

Предыдущее
От: Dana Burd
Дата:
Сообщение: Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #16798: SQL Error