Osvaldo, Thanks! This is a great solution. It definitely is very easy to read. I like to have my SQL as clean as my java code. I ended up using the following:
SELECT some_timestamp FROM some_table WHERE some_timestamp::date > 'yesterday'::date; Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -----Original Message----- From: Osvaldo Kussama [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 4:41 PM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] subtract a day from the NOW function --- "Campbell, Lance" <[EMAIL PROTECTED]> escreveu: > Table > > Field "some_timestamp" is a timestamp. > > > > In a "WHERE" statement I need to compare a timestamp > field in a table > "some_timestamp" to now() - one day. > > > > Example: > > > > SELECT some_timestamp WHERE to_char(some_timestamp, > 'YYYYMMDD') > > (to_char(now(), 'YYYYMMDD') - 1 day); > > > > The statement "to_char(now(), 'YYYYMMDD') - 1 day)" > is obviously > incorrect. I just need to know how to form this in > a way that will > work. > > > > If there is an entirely different solution I am all > for it. Do note > that I started down this path because I want to > exclude the hour, > minutes and seconds found in the field > "some_timestamp" and in the > function now(). > Try: SELECT some_timestamp WHERE some_timestamp > 'yesterday'::timestamp; Look 8.5.1.5. Special Values at: http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html []s Osvaldo ____________________________________________________________________________________ Novo Yahoo! Cadê? - Experimente uma nova busca. http://yahoo.com.br/oqueeuganhocomisso ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
