Re: using dates in pgsql

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

JohnH <JohnH(at)atkgib(dot)com(dot)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

On Wed, 2003-08-27 at 23:28, Tom Lane wrote:
> JohnH <JohnH(at)atkgib(dot)com(dot)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(dot)l(dot)johnson(at)cox(dot)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

Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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

On Thu, 2003-08-28 at 08:41, Tom Lane wrote:
> Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)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(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

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

"Ron Johnson" <ron(dot)l(dot)johnson(at)cox(dot)net> wrote in message
news:1062080015(dot)25751(dot)319(dot)camel(at)haggis(dot)(dot)(dot)
> 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