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

Поиск
Список
Период
Сортировка
От Dana Burd
Тема Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone
Дата
Msg-id CADYUPHNmh120OVhgkr897+SAyHdPm8=nj+mnwcY9d-fyWMJLSw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #16797: EXTRACT(EPOCH FROM timestamp) is not using local timezone  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
+pgsql-bugs

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.  Thus, to be consistent with the extract epoch from timestamp method chosen in 9.2, these should not be equal or perhaps a type-mismatch error. 

Personally, I prefered the previous behavior with the implicit cast to timestamptz when asked to convert timestamp for extracting epoch or other with timezone related purposes - just seems more consistent and expected to me.  And yes - I agree being type explicit is the better route here, vs relying on implicit behaviours that could change - I'll do that, this one just bit me from some two decade old code being moved to a new postgres instance.

kind regards,
-dana

On Wed, Dec 30, 2020 at 2:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
PG Bug reporting form <noreply@postgresql.org> writes:
> EXTRACT(EPOCH FROM timestamp) should be using the local timezone

No, type timestamp is explicitly *not* timezone aware.  If you use
timestamptz (a/k/a timestamp with time zone) you will get the
answer you want.

> -- Expected results (seen from PostgreSQL 9.1.11):

> # SET TIME ZONE 'EST5EDT'; select extract(epoch from ('01/01/1970
> 00:00:00'::timestamp));

This was a bug, cf 9.2.0 release notes [1]:

    Make EXTRACT(EPOCH FROM timestamp without time zone) measure the epoch
    from local midnight, not UTC midnight (Tom Lane)

    This change reverts an ill-considered change made in release 7.3.
    Measuring from UTC midnight was inconsistent because it made the
    result dependent on the timezone setting, which computations for
    timestamp without time zone should not be. The previous behavior
    remains available by casting the input value to timestamp with time
    zone.

                        regards, tom lane

[1] https://www.postgresql.org/docs/release/9.2.0/

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: 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