funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone
Date: 2010-09-03 18:54:03
Message-ID: AANLkTinhmBW6mCQM3jPt3SqaR2bhXKsNf2H3CyGCzr9P@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
timestamps_test.sql text/x-sql 3.4 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-09-03 18:58:55 Re: funkiness with '1999-12-31 19:00:00-05'::timestamp with time zone
Previous Message John Adams 2010-09-03 18:45:38 How can I use parameters in plain sql