Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E31152E3265 for ; Tue, 22 Jan 2008 04:08:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 38866-03 for ; Tue, 22 Jan 2008 04:08:27 -0400 (AST) Received: from cat.co.za (mailer.cat.co.za [196.34.125.51]) by postgresql.org (Postfix) with SMTP id 6D4B82E377F for ; Tue, 22 Jan 2008 04:08:20 -0400 (AST) Received: (qmail 4390 invoked by uid 1002); 22 Jan 2008 08:08:41 -0000 Received: from stuartb@cat.co.za by mail.cat.co.za by uid 807 with qmail-scanner-1.21 (clamscan: 0.70. Clear:RC:1(196.34.125.52):. Processed in 4.060801 secs); 22 Jan 2008 08:08:41 -0000 Received: from unknown (HELO ?192.168.34.201?) (196.34.125.52) by 0 with SMTP; 22 Jan 2008 08:08:37 -0000 Message-ID: <4795A3B0.3010505@cat.co.za> Date: Tue, 22 Jan 2008 10:05:04 +0200 From: Stuart Brooks User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: TIMESTAMP comparison problem Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/165 X-Sequence-Number: 30018 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)