From: | "Campbell, Lance" <lance(at)uiuc(dot)edu> |
---|---|
To: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: subtract a day from the NOW function |
Date: | 2007-06-07 17:49:19 |
Message-ID: | A3AC4FA47DC0B1458C3E5396E685E63302395E27@SAB-DC1.sab.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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:grzm(at)seespotcode(dot)net]
Sent: Thursday, June 07, 2007 12:27 PM
To: Campbell, Lance
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] subtract a day from the NOW function
> 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
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Anton Melser | 2007-06-07 18:00:06 | should the postgres user have a password? |
Previous Message | Lonni J Friedman | 2007-06-07 17:47:34 | querying the age of a row |
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2007-06-07 18:12:00 | Re: subtract a day from the NOW function |
Previous Message | Andrew Sullivan | 2007-06-07 17:46:39 | Re: the right time to vacuum database? |