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. Possible options: A) SELECT * FROM some_table WHERE some_timestamp::date > (CURRENT_DATE - INTERVAL '1 day')::date Or B) SELECT * FROM some_table WHERE to_char(some_timestamp, 'YYYYMMDD') > to_char((now() - interval '1 day'), 'YYYYMMDD'); I am just guessing but A does seem like it would be a better option. Option A is at least cleaner to read. 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: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 12:27 PM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] subtract a day from the NOW function > From: [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] On Behalf Of Campbell, Lance > Sent: Thursday, June 07, 2007 11:09 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] subtract a day from the NOW function > SELECT some_timestamp WHERE to_char(some_timestamp, 'YYYYMMDD') > > (to_char(now(), 'YYYYMMDD') - 1 day); On Jun 7, 2007, at 11:36 , Campbell, Lance wrote: > select to_char((now() - interval '1 day'), 'YYYYMMDD'); Why are you using to_char? Timestamps and dates support comparisons just fine. SELECT CURRENT_TIMESTAMP > (CURRENT_TIMESTAMP - INTERVAL '1 day'); ?column? ---------- t (1 row) CURRENT_TIMESTAMP is SQL-spec for now(). If you're specifically looking to compare dates rather than timestamps, you can cast timestamp to date: SELECT CURRENT_DATE > (CURRENT_DATE - INTERVAL '1 day')::date; ?column? ---------- t (1 row) You could also use the age function: SELECT age(CURRENT_TIMESTAMP) < INTERVAL '1 day'; SELECT age(CURRENT_TIMESTAMP) < INTERVAL '1 day'; ?column? ---------- t (1 row) Hope that helps. Michael Glaesemann grzm seespotcode net ---------------------------(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
