Re: subtract a day from the NOW function

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
Cc: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, "Campbell, Lance" <lance(at)uiuc(dot)edu>, pgsql-sql(at)postgresql(dot)org
Subject: Re: subtract a day from the NOW function
Date: 2007-06-07 21:58:03
Message-ID: C6AE494E-146A-443E-9E54-AE95F2943B1F@seespotcode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql


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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-06-07 22:01:51 Re: index vs. seq scan choice?
Previous Message George Pavlov 2007-06-07 21:56:06 Re: index vs. seq scan choice?

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2007-06-07 22:08:20 Re: subtract a day from the NOW function
Previous Message Michael Glaesemann 2007-06-07 21:53:03 Re: subtract a day from the NOW function