Inconsistent behavior with TIMESTAMP WITHOUT and epoch
От | Josh Berkus |
---|---|
Тема | Inconsistent behavior with TIMESTAMP WITHOUT and epoch |
Дата | |
Msg-id | 200501241631.41982.josh@agliodbs.com обсуждение исходный текст |
Ответы |
Re: Inconsistent behavior with TIMESTAMP WITHOUT and epoch
|
Список | pgsql-bugs |
Summary: "epoch" does not produce a consistent behavior when cast as TIMESTAMP WITHOUT TIMEZONE Severity: Annoyance Tested On: 7.4.6, 8.0b4 Example: test=> select extract(epoch from '2004-12-01 00:00'::TIMESTAMP WITHOUT TIME ZONE); date_part ------------ 1101888000 this value is actually local time, not GMT time, as one might expect from TIMESTAMP WITHOUT TIMEZONE. We see this problem when we try to reverse the process: test=> select timestamp without time zone 'epoch' + ( interval '1 second' * 1101888000 ); ?column? --------------------- 2004-12-01 08:00:00 btw, to reenforce the above: webmergers2=> select extract(epoch from '2004-12-01 00:00 GMT'::TIMESTAMPTZ); date_part ------------ 1101859200 thus, EXTRACT(epoch) as TIMESTAMP-NO-TZ produces local time, and CAST(epoch AS timestamp-no-tz) produces GMT. This is inconsistent; it should do either local time or GMT for both. --Josh Berkus P.S. if anyone is wondering why I'm doing epoch with timestamp-no-tz it's for a calendaring application which exists on 2 servers in two different time zones, and all I really want is the date.
В списке pgsql-bugs по дате отправления: