From: Michael Glaesemann <grzm@seespotcode.net>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: subtract a day from the NOW function
Date: Thu, 7 Jun 2007 14:22:44 -0500
Message-ID: <D3436F83-5008-4CA3-B056-E792654B4F73@seespotcode.net> (raw)
In-Reply-To: <4668555E.20003@pinpointresearch.com>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E24@SAB-DC1.sab.uiuc.edu>
<A3AC4FA47DC0B1458C3E5396E685E63302395E25@SAB-DC1.sab.uiuc.edu>
<9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net>
<A3AC4FA47DC0B1458C3E5396E685E63302395E27@SAB-DC1.sab.uiuc.edu>
<46684A70.4080903@g2switchworks.com>
<4668555E.20003@pinpointresearch.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, scrawford@pinpointresearch.com, smarlowe@g2switchworks.com, lance@uiuc.edu
Subject: Re: subtract a day from the NOW function
In-Reply-To: <D3436F83-5008-4CA3-B056-E792654B4F73@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox