[Please don't top-post. It makes the discussion difficult to follow.]
On Jun 7, 2007, at 12:49 , Campbell, Lance wrote:
1) I have a timestamp field, "some_timestamp", in table "some_table".
2) I want to compare field "some_timestamp" to the current date - 1
day.
I need to ignore hours, minutes and seconds.
Possible options:
A) SELECT * FROM some_table WHERE some_timestamp::date >
(CURRENT_DATE -
INTERVAL '1 day')::date
Casting to date as you are will work. You can also use date_trunc:
SELECT *
FROM some_table
WHERE date_trunc('day', some_timestamp) > date_trunc('day',
(CURRENT_DATE - INTERVAL '1 day'));
Note the differences in the results:
SELECT CURRENT_TIMESTAMP, date_trunc('day', CURRENT_TIMESTAMP),
CURRENT_TIMESTAMP::date, CURRENT_DATE;
now | date_trunc | now
| date
-------------------------------+------------------------+------------
+------------
2007-06-07 13:21:28.186958-05 | 2007-06-07 00:00:00-05 | 2007-06-07 |
2007-06-07
date_trunc will return a timestamp.
B) SELECT * FROM some_table WHERE to_char(some_timestamp,
'YYYYMMDD') >
to_char((now() - interval '1 day'), 'YYYYMMDD');
I'd never use to_char to compare dates. The built-in comparison
operators work just fine.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
