Thread: 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.
ThanksStuart
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-2209: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-2209: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@seespotcode.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