TIMESTAMP comparison problem

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)

On Jan 22, 2008, at 3:05 , Stuart Brooks wrote:

> If I reduce the precision to 3 for the timestamps it appears to
> work although it makes me nervous.

With float timestamps, you're fooling yourself if you think those
numbers past the decimal are reliable.

> Should I just recompile to use integer datetimes? I would like to
> have at least microsecond precision.

Well, you can't get better than microsecond precision with timestamps
in Postgres. And the only way you can rely on that level of precision
is to compile with --enable-integer-datetimes.

Michael Glaesemann
grzm seespotcode net

>> If I reduce the precision to 3 for the timestamps it appears to work
>> although it makes me nervous.
>
> With float timestamps, you're fooling yourself if you think those
> numbers past the decimal are reliable.
>
>> Should I just recompile to use integer datetimes? I would like to
>> have at least microsecond precision.
>
> Well, you can't get better than microsecond precision with timestamps
> in Postgres. And the only way you can rely on that level of precision
> is to compile with --enable-integer-datetimes.
>
> Michael Glaesemann
I thought that might be the case, thanks for the help,

Stuart

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> Well, you can't get better than microsecond precision with timestamps
> in Postgres. And the only way you can rely on that level of precision
> is to compile with --enable-integer-datetimes.

There is more precision in there, but the output routine won't show it
to you. I think the real issue in Stuart's example is that what's being
shown as .746349 is actually .7463494 or something like that. Doing an
extract(epoch) on the stored values might be instructive.

If you don't want to deal with these sorts of issues then yeah, you want
integer timestamps.

regards, tom lane