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
Show quoted text
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