From: | Stuart Brooks <stuartb(at)cat(dot)co(dot)za> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | TIMESTAMP comparison problem |
Date: | 2008-01-22 08:05:04 |
Message-ID: | 4795A3B0.3010505@cat.co.za |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a problem in comparing a TIMESTAMP field with a timestamp
literal. I presume it is to do with the floating point representation of
the timestamp but I was wondering if there is an easy work around
without having to recompile postgres to use integer datetimes.
Basically if I issue a "SELECT * FROM T WHERE tstamp>'xxxxx';" I get
xxxxx as my first field.
If I reduce the precision to 3 for the timestamps it appears to work
although it makes me nervous.
I am running postgresql 8.2.5 on NetBSD 3.
Should I just recompile to use integer datetimes? I would like to have
at least microsecond precision.
Thanks
Stuart
Table definition:
------------------------------------------------
db=> \d+ Transactions;
Table "test.transactions"
Column | Type | Modifiers transaction_key | bigint | not null default nextval('transactions_transaction_key_seq'::regclass) |
time | timestamp(6) without time zone | not null
Indexes:
"transactions_pkey" PRIMARY KEY, btree (transaction_key)
"transactions_time_index" btree ("time", transaction_key)
Has OIDs: no
Table contents:
------------------------------------------------
db=> select transaction_key,time from Transactions;
transaction_key | time
-----------------+----------------------------
1 | 2008-01-22 09:33:34.681693
2 | 2008-01-22 09:33:34.98421
3 | 2008-01-22 09:33:36.270745
4 | 2008-01-22 09:33:38.573363
5 | 2008-01-22 09:33:38.496988
6 | 2008-01-22 09:33:39.995707
7 | 2008-01-22 09:33:40.111784
8 | 2008-01-22 09:33:41.415505
9 | 2008-01-22 09:33:42.328298
10 | 2008-01-22 09:33:42.025126
11 | 2008-01-22 09:33:44.802205
12 | 2008-01-22 09:33:45.257675
13 | 2008-01-22 09:33:46.746349
14 | 2008-01-22 09:33:46.513937
15 | 2008-01-22 09:33:46.735079
16 | 2008-01-22 09:33:47.528806
17 | 2008-01-22 09:33:49.20255
18 | 2008-01-22 09:33:51.724916
19 | 2008-01-22 09:33:52.550102
20 | 2008-01-22 09:33:54.698312
(20 rows)
Query with problem:
------------------------------------------------
metadb=> select transaction_key,time from Transactions where time>'2008-01-22 09:33:46.746349';
transaction_key | time
-----------------+----------------------------
13 | 2008-01-22 09:33:46.746349 *** THIS SHOULDN'T BE HERE ****
16 | 2008-01-22 09:33:47.528806
17 | 2008-01-22 09:33:49.20255
18 | 2008-01-22 09:33:51.724916
19 | 2008-01-22 09:33:52.550102
20 | 2008-01-22 09:33:54.698312
(6 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2008-01-22 09:13:26 | Re: currval() within one statement |
Previous Message | sad | 2008-01-22 07:54:58 | Re: currval() within one statement |