Postgres Pro
Downloads
Home   >   mailing lists

Thread: < and > for datetime

< and > for datetime

From
Sarah Officer
Date:
Hi all,

I'm trying to find  less than and greater than functions for
datetime types.  It doesn't look the the time interval operators
would be appropriate, but I haven't found anything else in the
docs.  Here's my snippet of plpgsql which fails:

    select count(*) into rescount
    from currentresults cr, masters m
    where cr.ben = new.ben
    and m.mi_id = cr.mi_id
    and m.idate > mydate;

rescount & mydate are variables.  How do I compare dates?  I'd be
glad to read the documentation if I could find the right page.

Thanks,

Sarah
officers@aries.tucson.saic.com

Re: < and > for datetime

From
Martin Schulze
Date:
Sarah Officer wrote:
> Hi all,
>
> I'm trying to find  less than and greater than functions for
> datetime types.  It doesn't look the the time interval operators
> would be appropriate, but I haven't found anything else in the
> docs.  Here's my snippet of plpgsql which fails:
>
>     select count(*) into rescount
>     from currentresults cr, masters m
>     where cr.ben = new.ben
>     and m.mi_id = cr.mi_id
>     and m.idate > mydate;
>
> rescount & mydate are variables.  How do I compare dates?  I'd be
> glad to read the documentation if I could find the right page.

\do shows:

<= |datetime  |datetime  |bool     |less-than-or-equal
<> |datetime  |datetime  |bool     |not equal
=  |datetime  |datetime  |bool     |equal
>  |datetime  |datetime  |bool     |greater-than
<  |datetime  |datetime  |bool     |less-than

Thus, your WHERE-clause should be proper.

Regards,

    Joey

--
This is Linux Country.  On a quiet night, you can hear Windows reboot.

Re: < and > for datetime

From
Sarah Officer
Date:
Thanks.  Oops.  Now I see that I had one datetime and one date.

I can't do \do on my machine.  Many of the psql backslash commands
cause an error.  Does anyone know what could cause this error?  I am
running version 6.5.3 on an IRIX 6.5.
db=> \do
ERROR:  typeidTypeRelid: Invalid type - oid = 0

Thanks,

Sarah Officer
officers@aries.tucson.saic.com

Martin Schulze wrote:
>
> Sarah Officer wrote:
> > Hi all,
> >
> > I'm trying to find  less than and greater than functions for
> > datetime types.  It doesn't look the the time interval operators
> > would be appropriate, but I haven't found anything else in the
> > docs.  Here's my snippet of plpgsql which fails:
> >
> >     select count(*) into rescount
> >     from currentresults cr, masters m
> >     where cr.ben = new.ben
> >     and m.mi_id = cr.mi_id
> >     and m.idate > mydate;
> >
> > rescount & mydate are variables.  How do I compare dates?  I'd be
> > glad to read the documentation if I could find the right page.
>
> \do shows:
>
> <= |datetime  |datetime  |bool     |less-than-or-equal
> <> |datetime  |datetime  |bool     |not equal
> =  |datetime  |datetime  |bool     |equal
> >  |datetime  |datetime  |bool     |greater-than
> <  |datetime  |datetime  |bool     |less-than
>
> Thus, your WHERE-clause should be proper.
>
> Regards,
>
>         Joey
>
> --
> This is Linux Country.  On a quiet night, you can hear Windows reboot.