Postgres Pro
Downloads
Home   >   mailing lists

Thread: using dates in pgsql

using dates in pgsql

From
JohnH
Date:
G'Day to all those pgsql novices out there
 
I am trying to isolate those records where the active date is at least six months old
 
I have no problem if I use
 
SELECT * from newbuyers
WHERE activedate < '01/01/2003'
ORDER BY last_name
But I want to use a variable in the WHERE option 
 
WHERE  activedate < (current date less 188 days) 
 
active date is a database date field
 
I would like to include the calculation in the WHERE statement, but if I can't  should I create the (current date less 188 days) outside of the sql and bring it in as a <dtml-sqlvar ...> variable?  and if so how?
 
 
Thanks to anyone who can help

John Haley

 

Re: using dates in pgsql

From
Tom Lane
Date:
JohnH <JohnH@atkgib.com.au> writes:
> But I want to use a variable in the WHERE option
> WHERE  activedate < (current date less 188 days)

You're going to be embarrassed...

WHERE  activedate < (CURRENT_DATE - 188)

            regards, tom lane

Re: using dates in pgsql

From
Ron Johnson
Date:
On Wed, 2003-08-27 at 23:28, Tom Lane wrote:
> JohnH <JohnH@atkgib.com.au> writes:
> > But I want to use a variable in the WHERE option
> > WHERE  activedate < (current date less 188 days)
>
> You're going to be embarrassed...
>
> WHERE  activedate < (CURRENT_DATE - 188)

How does pg know that that's 188 days, and not months or years?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter

Re: using dates in pgsql

From
Tom Lane
Date:
Ron Johnson <ron.l.johnson@cox.net> writes:
> On Wed, 2003-08-27 at 23:28, Tom Lane wrote:
>> WHERE  activedate < (CURRENT_DATE - 188)

> How does pg know that that's 188 days, and not months or years?

<shrug> ... that's what the "date - integer" operator is defined to do.
If you want some other behavior, create another operator.

            regards, tom lane

Re: using dates in pgsql

From
Ron Johnson
Date:
On Thu, 2003-08-28 at 08:41, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
> > On Wed, 2003-08-27 at 23:28, Tom Lane wrote:
> >> WHERE  activedate < (CURRENT_DATE - 188)
>
> > How does pg know that that's 188 days, and not months or years?
>
> <shrug> ... that's what the "date - integer" operator is defined to do.
> If you want some other behavior, create another operator.

Oh, ok.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Object-oriented programming is an exceptionally bad idea which
could only have originated in California."
Edsger Dijkstra

Re: using dates in pgsql

From
"Tyler Colbert"
Date:
"Ron Johnson" <ron.l.johnson@cox.net> wrote in message
news:1062080015.25751.319.camel@haggis...
> On Thu, 2003-08-28 at 08:41, Tom Lane wrote:
> > <shrug> ... that's what the "date - integer" operator is defined to do.
> > If you want some other behavior, create another operator.
>

I have used something like this:
    now() - interval '3 hours'

so I supposed you could do
    CURRENT_DATE - interval '188 days'

this would probably work more accurately (due to leap year)
    CURRENT_DATE - interval '6 months'

or if you want to take time into account
    now() - interval '6 months'

Tyler Colbert