TIMESTAMP comparison problem

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)

Responses

Browse pgsql-sql by date

  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