Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 98A6D9FC18F for ; Thu, 7 Jun 2007 16:23:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 74361-05 for ; Thu, 7 Jun 2007 16:22:54 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mtai04.charter.net (mtai04.charter.net [209.225.8.184]) by postgresql.org (Postfix) with ESMTP id 61FED9FC187 for ; Thu, 7 Jun 2007 16:22:52 -0300 (ADT) Received: from aa09.charter.net ([10.20.200.161]) by mtai04.charter.net (InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP id <20070607192246.KDZV1555.mtai04.charter.net@aa09.charter.net>; Thu, 7 Jun 2007 15:22:46 -0400 Received: from [10.0.1.3] (really [66.188.164.45]) by aa09.charter.net with ESMTP id <20070607192246.VCOS4382.aa09.charter.net@[10.0.1.3]>; Thu, 7 Jun 2007 15:22:46 -0400 In-Reply-To: <4668555E.20003@pinpointresearch.com> References: <9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net> <46684A70.4080903@g2switchworks.com> <4668555E.20003@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 14:22:44 -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/66 X-Sequence-Number: 28556 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