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 16:58:03 -0500
Message-ID: <C6AE494E-146A-443E-9E54-AE95F2943B1F@seespotcode.net> (raw)
In-Reply-To: <46687395.80707@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>
<D3436F83-5008-4CA3-B056-E792654B4F73@seespotcode.net>
<46687395.80707@pinpointresearch.com>
On Jun 7, 2007, at 16:07 , Steve Crawford wrote:
> 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:
Right. It's only for timestamptz +/i interval.
> select timestamptz '2007-11-05' - timestamptz '2007-11-04';
>
> ?column?
> ----------------
> 1 day 01:00:00
It is a bit tricky. Datetime math is inherently so.
> select timestamptz '2007-11-04' + interval '1 day 01:00:00';
> ?column?
> ------------------------
> 2007-11-05 01:00:00-08
What PostgreSQL is doing behind the scenes is incrementing the date
2007-11-04 ahead 1 day and 1 hour. It treats months (and years),
days, and time separately.
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: <C6AE494E-146A-443E-9E54-AE95F2943B1F@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