Re: TIMESTAMP with a timezone offset

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: TIMESTAMP with a timezone offset
Дата
Msg-id 18273.1229474834@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: TIMESTAMP with a timezone offset  (Madison Kelly <linux@alteeve.com>)
Список pgsql-general
Madison Kelly <linux@alteeve.com> writes:
> I was reading that before posting, but have not been able to get it to
> work. After looking at it again I think it's because I've cast the
> column I restricted in the SELECT as a 'TIMESTAMP WITHOUT TIME ZONE'. So
> I ALTERed the column to be 'TIMESTAMP WITH TIME ZONE' and tried again.
> However, it looks like it cast the time zone on each column to my
> current time zone instead of UTC.

Yeah, the default conversion from TS-without-TZ to TS-with-TZ assumes
that the TS-without-TZ values are in your current timezone.  You might
be able to get what you want by setting timezone to UTC temporarily
while doing the ALTER.  However, that approach might give you headaches
with inserting more data --- you might find yourself needing to keep
timezone = UTC all the time, which might create troubles elsewhere.

Another way to do it is, if you're starting from TS-without-TZ data
that you want to consider as being in UTC, is

    (ts_value AT TIME ZONE 'UTC') AT TIME ZONE 'EST'

The first conversion says "this TS-without-TZ data is in UTC, now
produce a correct TS-with-TZ from it".  And then the second one
rotates that back to local time in EST.

            regards, tom lane

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Isolating a record column from a PL-Pgsql function call ?
Следующее
От: Phillip Berry
Дата:
Сообщение: Maximum reasonable free space map