Re: subtract a day from the NOW function

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.

test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5367)
(1 row)

test=# select '2007-03-12'::timestamptz, '2007-03-12'::timestamptz -
interval '1 day';
timestamptz | ?column?
------------------------+------------------------
2007-03-12 00:00:00-05 | 2007-03-11 00:00:00-06
(1 row)

test=# select '2007-11-04'::timestamptz, '2007-11-04'::timestamptz -
interval '1 day';
timestamptz | ?column?
------------------------+------------------------
2007-11-04 00:00:00-05 | 2007-11-03 00:00:00-05
(1 row)

test=# select '2007-11-04'::timestamptz, '2007-11-04'::timestamptz +
interval '1 day';
timestamptz | ?column?
------------------------+------------------------
2007-11-04 00:00:00-05 | 2007-11-05 00:00:00-06
(1 row)

test=# show time zone;
TimeZone
------------
US/Central
(1 row)

Note how the UTC offset changes across the daylight saving time change.

Michael Glaesemann
grzm seespotcode net