funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone
От | Josh Kupershmidt |
---|---|
Тема | funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone |
Дата | |
Msg-id | AANLkTinhmBW6mCQM3jPt3SqaR2bhXKsNf2H3CyGCzr9P@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone
|
Список | pgsql-general |
Hi all, I've come across a puzzling situation with a table having a timestamp with time zone column. This column is full of values displaying exactly as '1999-12-31 19:00:00-05', but for some reason Postgres is treating some of these identical-seeming timestamps as being different. If I update all these timestamps by adding an interval of '1 DAYS' to all rows, Postgres recognizes all the values as being the same. If I repeat this experiment using a timestamp without time zone type, Postgres recognizes all the timestamps as being the same. When I pg_dump the timestamps_test table, I see a normal-looking dump: COPY timestamps_test (ts) FROM stdin; 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 … and when I reload this pg_dump file back into the same database, Postgres again recognizes that all the timestamps are the same (i.e. SELECT COUNT(DISTINCT(ts)) returns 1). I've attached a plain-text pg_dump of this table. Here's a log of how I created this timestamps_test table, from a source table full of these '1999-12-31 19:00:00-05' timestamps. Any ideas what might be causing this? test=# CREATE TABLE timestamps_test (ts timestamp with time zone NOT NULL); CREATE TABLE test=# INSERT INTO timestamps_test (ts) SELECT DISTINCT(updated) FROM myschema.strange_table; INSERT 0 119 test=# SELECT COUNT(DISTINCT(ts)) FROM timestamps_test; count ------- 119 (1 row) test=# SELECT DISTINCT(ts) FROM timestamps_test LIMIT 10; ts ------------------------ 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 1999-12-31 19:00:00-05 (10 rows) test=# SELECT MAX(ts) = MIN(ts), MAX(ts) - MIN(ts) FROM timestamps_test; ?column? | ?column? ----------+---------- f | 00:00:00 (1 row) test=# UPDATE timestamps_test SET ts = ts + INTERVAL '1 DAYS'; UPDATE 119 test=# SELECT COUNT(DISTINCT(ts)) FROM timestamps_test; count ------- 1 (1 row) test=# SELECT DISTINCT(ts) FROM timestamps_test LIMIT 10; ts ------------------------ 2000-01-01 19:00:00-05 (1 row) test=# SELECT version(); version -------------------------------------------------------------------------------- ----------------------------------- PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 2 0080704 (Red Hat 4.1.2-46), 64-bit (1 row) test=# SELECT name, setting FROM pg_settings WHERE name IN ('TimeZone', 'lc_collate', 'lc_ctype', 'lc_time', 'DateStyle'); name | setting ------------+------------ DateStyle | ISO, MDY lc_collate | C lc_ctype | C lc_time | C TimeZone | US/Eastern (5 rows) Thanks for any ideas, Josh
Вложения
В списке pgsql-general по дате отправления: