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 |
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 |