Forum OpenACS Q&A: Comparing timestamps in Postgres

Collapse
1: Comparing timestamps in Postgres
Posted by Andrew Zeon on 10/05/02 10:29 AM
Hi...
Is there a way you can compare two timestamps to the nearest minute
in Postgres?

Thanks.

Collapse
2: Response to Comparing timestamps in Postgres (response to 1)
Posted by Andrei Popov on 10/05/02 02:27 PM
how about to_char(time_stamp_field,'YYYYMMDD 24HHMM') and then compare?
Collapse
3: Response to Comparing timestamps in Postgres (response to 1)
Posted by Roberto Mello on 10/05/02 04:24 PM
What response are you looking for? 

foobar=# select now() - '2002-10-05 08:52:38.278458-06'::timestamptz;
    ?column?     
-----------------
 00:00:23.901656
Collapse
4: Response to Comparing timestamps in Postgres (response to 1)
Posted by Don Baccus on 10/05/02 04:25 PM
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).