pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTIMESTAMP comparison problem
4+ messages / 3 participants
[nested] [flat]
* TIMESTAMP comparison problem
@ 2008-01-22 08:05 Stuart Brooks <stuartb@cat.co.za>
0 siblings, 1 reply; 4+ messages in thread
From: Stuart Brooks @ 2008-01-22 08:05 UTC (permalink / raw)
To: 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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TIMESTAMP comparison problem
@ 2008-01-22 12:42 Michael Glaesemann <grzm@seespotcode.net>
parent: Stuart Brooks <stuartb@cat.co.za>
0 siblings, 2 replies; 4+ messages in thread
From: Michael Glaesemann @ 2008-01-22 12:42 UTC (permalink / raw)
To: Stuart Brooks <stuartb@cat.co.za>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TIMESTAMP comparison problem
@ 2008-01-22 12:52 Stuart Brooks <stuartb@cat.co.za>
parent: Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 4+ messages in thread
From: Stuart Brooks @ 2008-01-22 12:52 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: TIMESTAMP comparison problem
@ 2008-01-22 17:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2008-01-22 17:13 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Stuart Brooks <stuartb@cat.co.za>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-01-22 17:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-22 08:05 TIMESTAMP comparison problem Stuart Brooks <stuartb@cat.co.za>
2008-01-22 12:42 ` Michael Glaesemann <grzm@seespotcode.net>
2008-01-22 12:52 ` Stuart Brooks <stuartb@cat.co.za>
2008-01-22 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
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