Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 740BB9FBB7B for ; Thu, 7 Jun 2007 18:58:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 19359-03 for ; Thu, 7 Jun 2007 18:58:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mtao05.charter.net (mtao05.charter.net [209.225.8.179]) by postgresql.org (Postfix) with ESMTP id 31A519FB73D for ; Thu, 7 Jun 2007 18:58:07 -0300 (ADT) Received: from aa04.charter.net ([10.20.200.156]) by mtao05.charter.net (InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP id <20070607215805.KGWF1417.mtao05.charter.net@aa04.charter.net>; Thu, 7 Jun 2007 17:58:05 -0400 Received: from [10.0.1.3] (really [66.188.164.45]) by aa04.charter.net with ESMTP id <20070607215805.LVJT26958.aa04.charter.net@[10.0.1.3]>; Thu, 7 Jun 2007 17:58:05 -0400 In-Reply-To: <46687395.80707@pinpointresearch.com> References: <9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net> <46684A70.4080903@g2switchworks.com> <4668555E.20003@pinpointresearch.com> <46687395.80707@pinpointresearch.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Scott Marlowe , "Campbell, Lance" , pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: subtract a day from the NOW function Date: Thu, 7 Jun 2007 16:58:03 -0500 To: Steve Crawford X-Mailer: Apple Mail (2.752.2) X-Chzlrs: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/71 X-Sequence-Number: 28561 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