Forum OpenACS Q&A: Comparing timestamps in Postgres

Collapse
Posted by Andrew Zeon on
Hi...
Is there a way you can compare two timestamps to the nearest minute
in Postgres?

Thanks.

Collapse
Posted by Andrei Popov on
how about to_char(time_stamp_field,'YYYYMMDD 24HHMM') and then compare?
Collapse
Posted by Roberto Mello on
What response are you looking for?

foobar=# select now() - '2002-10-05 08:52:38.278458-06'::timestamptz;
    ?column?     
-----------------
 00:00:23.901656
Collapse
Posted by Don Baccus on
Or "date_trunc('minute', your_timestamp)".  This is guaranteed to work for timestamps that have differing timezones.  to_char() doesn't do timezone conversions automatically so won't work unless your timestamps are all scaled to a particular timezone (PG 7.3 will default to timestamps without timezone data but PG 7.2's default timestamp does include timezone data).