Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 279849FBF63 for ; Thu, 7 Jun 2007 15:58:46 -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 58977-08 for ; Thu, 7 Jun 2007 15:58:38 -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 3E2199FC10B for ; Thu, 7 Jun 2007 15:58:41 -0300 (ADT) Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id DECD1E00EC84; Thu, 7 Jun 2007 11:58:38 -0700 (PDT) Message-ID: <4668555E.20003@pinpointresearch.com> Date: Thu, 07 Jun 2007 11:58:38 -0700 From: Steve Crawford User-Agent: Thunderbird 1.5.0.10 (X11/20060911) MIME-Version: 1.0 To: Scott Marlowe CC: "Campbell, Lance" , Michael Glaesemann , 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> In-Reply-To: <46684A70.4080903@g2switchworks.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/65 X-Sequence-Number: 28555 Scott Marlowe wrote: > Campbell, Lance wrote: >> Michael, >> So based on your feedback would it be better to do option A or B below? >> >> 1) I have a timestamp field, "some_timestamp", in table "some_table". >> 2) I want to compare field "some_timestamp" to the current date - 1 day. >> I need to ignore hours, minutes and seconds. >> > You might want to use date_trunc then: > > select * from sometable where date_trunc('day',tiemstampfield) > > date_trunc('day',now() - interval '1 day'); > > or something like that. Beware in the "or something like that category" that PostgreSQL considers "1 day" to be "24 hours" thus depending on whether the timestampfield is with or without TZ and where you do your truncation (before or after subtracting), you can end up with unexpected results in the vicinity of DST changes: select '2007-03-12'::timestamptz - '1 day'::interval; ?column? ------------------------ 2007-03-10 23:00:00-08 select '2007-03-12'::timestamp - '1 day'::interval; ?column? --------------------- 2007-03-11 00:00:00 Especially note that truncating a timestamptz preserves the timezone info so you will very likely need to address issues on the days that Daylight Saving starts or ends: select date_trunc('day',current_timestamp); date_trunc ------------------------ 2007-06-07 00:00:00-07 Cheers, Steve