From: Stuart Brooks <stuartb@cat.co.za>
To: pgsql-sql@postgresql.org
Subject: TIMESTAMP comparison problem
Date: Tue, 22 Jan 2008 10:05:04 +0200
Message-ID: <4795A3B0.3010505@cat.co.za> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stuartb@cat.co.za
Subject: Re: TIMESTAMP comparison problem
In-Reply-To: <4795A3B0.3010505@cat.co.za>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox