Re: subtract a day from the NOW function

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

________________________________

From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:37 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] subtract a day from the NOW function

I just figured it out. The solution is:

select to_char((now() - interval '1 day'), 'YYYYMMDD');

Thanks,

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

________________________________

From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Thursday, June 07, 2007 11:09 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] subtract a day from the NOW function

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().

Thanks,

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu