Re: subtract a day from the NOW function

Scott Marlowe wrote:
> Campbell, Lance wrote:
>> Michael,
>> So based on your feedback would it be better to do option A or B below?
>>
>> 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.
>>
> You might want to use date_trunc then:
>
> select * from sometable where date_trunc('day',tiemstampfield) >
> date_trunc('day',now() - interval '1 day');
>
> or something like that.

Beware in the "or something like that category" that PostgreSQL
considers "1 day" to be "24 hours" thus depending on whether the
timestampfield is with or without TZ and where you do your truncation
(before or after subtracting), you can end up with unexpected results in
the vicinity of DST changes:

select '2007-03-12'::timestamptz - '1 day'::interval;
?column?
------------------------
2007-03-10 23:00:00-08

select '2007-03-12'::timestamp - '1 day'::interval;
?column?
---------------------
2007-03-11 00:00:00

Especially note that truncating a timestamptz preserves the timezone
info so you will very likely need to address issues on the days that
Daylight Saving starts or ends:

select date_trunc('day',current_timestamp);
date_trunc
------------------------
2007-06-07 00:00:00-07

Cheers,
Steve