Re: subtract a day from the NOW function

Michael Glaesemann wrote:
>
> On Jun 7, 2007, at 13:58 , Steve Crawford wrote:
>
>> Beware in the "or something like that category" that PostgreSQL
>> considers "1 day" to be "24 hours"
>
> Actually, recent versions of PostgreSQL take into account daylight
> saving time in accordance with the current PostgreSQL time zone setting,
> so '1 day' in the context of timestamptz +/- interval may be 23, 24, or
> 25 hours....

Interesting - thanks. That's one more thing I need to check when
upgrading my server. If my reading is correct, there are some subtle
gotchas here.

If I go back and try on a 7.4 machine it appears that interval makes a
DST correction if the interval includes a unit of "month" or greater but
does not make a correction for "week" or "day" intervals.

On 8.2 I'm seeing an adjustment if the DST adjustment includes units of
"day" or greater (ie. '1 day' and '1 day 01:00' get adjusted but '24
hours' and '25 hours' do not).

But PG doesn't follow the same rules in subtracting timestamptz values
so operations involving timestamps and intervals are (sometimes) not
reversible:

select timestamptz '2007-11-05' - timestamptz '2007-11-04';

?column?
----------------
1 day 01:00:00

select timestamptz '2007-11-04' + interval '1 day 01:00:00';
?column?
------------------------
2007-11-05 01:00:00-08

Cheers,
Steve