In-Reply-To: <A3AC4FA47DC0B1458C3E5396E685E63302395E27@SAB-DC1.sab.uiuc.edu>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E24@SAB-DC1.sab.uiuc.edu>
	<A3AC4FA47DC0B1458C3E5396E685E63302395E25@SAB-DC1.sab.uiuc.edu>
	<9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net>
	<A3AC4FA47DC0B1458C3E5396E685E63302395E27@SAB-DC1.sab.uiuc.edu>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <D005E810-0FC2-42EC-98D6-5CA2B9B7B20C@seespotcode.net>
Cc: <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: subtract a day from the NOW function
Date: Thu, 7 Jun 2007 13:22:05 -0500
To: "Campbell, Lance" <lance@uiuc.edu>

[Please don't top-post. It makes the discussion difficult to follow.]

On Jun 7, 2007, at 12:49 , Campbell, Lance wrote:

> 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.
>
> Possible options:
>
> A) SELECT * FROM some_table WHERE some_timestamp::date >  
> (CURRENT_DATE -
> INTERVAL '1 day')::date

Casting to date as you are will work. You can also use date_trunc:

SELECT *
FROM some_table
WHERE date_trunc('day', some_timestamp) > date_trunc('day',  
(CURRENT_DATE - INTERVAL '1 day'));

Note the differences in the results:

SELECT CURRENT_TIMESTAMP, date_trunc('day', CURRENT_TIMESTAMP),  
CURRENT_TIMESTAMP::date, CURRENT_DATE;
               now              |       date_trunc       |    now      
|    date
-------------------------------+------------------------+------------ 
+------------
2007-06-07 13:21:28.186958-05 | 2007-06-07 00:00:00-05 | 2007-06-07 |  
2007-06-07

date_trunc will return a timestamp.

> B) SELECT * FROM some_table WHERE to_char(some_timestamp,  
> 'YYYYMMDD') >
> to_char((now() - interval '1 day'), 'YYYYMMDD');

I'd never use to_char to compare dates. The built-in comparison  
operators work just fine.

Michael Glaesemann
grzm seespotcode net