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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Bayless Kirtley"
Дата:
Сообщение: Re: Connection question
Следующее
От: Tom Lane
Дата:
Сообщение: Re: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone