Thread: subtratcing dates

Hello all,

I have a table that has two columns, tstamp of type timestamp, and limit
of type int.

I want to have a query that tells me whether or not the timestamp is
within 'limit' minutes of the current time.

so, something like:

select tstamp > now() + '-60 minute' from log;

except i need the 60 to be the value of the 'limit' column instead so...

select tstamp > now() + '-limit minute' from log;

which of course doesn't work.  I can't seem to get it right no matter
what I try.  Any ideas?  Thanks!

-Fran

Fran Fabrizio <ffabrizio@exchange.webmd.net> writes:
> I want to have a query that tells me whether or not the timestamp is
> within 'limit' minutes of the current time.

A poorly documented fact is that you can coerce an integer number of
seconds into a reltime, which can then be added to or subtracted from
a timestamp.  So:

    select tstamp > now() - reltime(limit*60) from ...

should do it.

            regards, tom lane

Thanks for the tip Tom!

At first, I accidentally tried to do reltime(limit*60) where limit was
accidentally an interval instead of an integer, and my database crashed
and exited horribly.  Perhaps that's why it's a poorly documented
function!  ;-)

-Fran

> Fran Fabrizio <ffabrizio@exchange.webmd.net> writes:
> > I want to have a query that tells me whether or not the timestamp is
> > within 'limit' minutes of the current time.
>
> A poorly documented fact is that you can coerce an integer number of
> seconds into a reltime, which can then be added to or subtracted from
> a timestamp.  So:
>
>         select tstamp > now() - reltime(limit*60) from ...
>
> should do it.
>
>                         regards, tom lane

Fran Fabrizio <ffabrizio@exchange.webmd.net> writes:
> At first, I accidentally tried to do reltime(limit*60) where limit was
> accidentally an interval instead of an integer, and my database crashed
> and exited horribly.

Yeah?  Did you have any nulls in the limit column?
    select reltime(null::interval);
bombs in 7.0 (but is fine in 7.1).  interval_reltime is far from the
only function that's not NULL-proof in pre-7.1 releases :-(

            regards, tom lane