Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id B7A119FC0FB for ; Thu, 7 Jun 2007 18:07:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 94352-05 for ; Thu, 7 Jun 2007 18:07:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from polaris.pinpointresearch.com (66-7-238-130.static-ip.telepacific.net [66.7.238.130]) by postgresql.org (Postfix) with ESMTP id 84E9C9FB4E4 for ; Thu, 7 Jun 2007 18:07:36 -0300 (ADT) Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 3DAE1E00EC84; Thu, 7 Jun 2007 14:07:34 -0700 (PDT) Message-ID: <46687395.80707@pinpointresearch.com> Date: Thu, 07 Jun 2007 14:07:33 -0700 From: Steve Crawford User-Agent: Thunderbird 1.5.0.10 (X11/20060911) MIME-Version: 1.0 To: Michael Glaesemann CC: Scott Marlowe , "Campbell, Lance" , pgsql-sql@postgresql.org Subject: Re: subtract a day from the NOW function References: <9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net> <46684A70.4080903@g2switchworks.com> <4668555E.20003@pinpointresearch.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/68 X-Sequence-Number: 28558 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