From: Michael Glaesemann <grzm@seespotcode.net>
To: Campbell, Lance <lance@uiuc.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: subtract a day from the NOW function
Date: Thu, 7 Jun 2007 12:27:03 -0500
Message-ID: <9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net> (raw)
In-Reply-To: <A3AC4FA47DC0B1458C3E5396E685E63302395E25@SAB-DC1.sab.uiuc.edu>
References: <A3AC4FA47DC0B1458C3E5396E685E63302395E24@SAB-DC1.sab.uiuc.edu>
<A3AC4FA47DC0B1458C3E5396E685E63302395E25@SAB-DC1.sab.uiuc.edu>
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, lance@uiuc.edu
Subject: Re: subtract a day from the NOW function
In-Reply-To: <9938A77A-6E63-413D-8AC9-7CB741D075E6@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox