Обсуждение: Comparing times to "now + 45 seconds"

I have this table:

# select * from live_stats;
  times
----------
 17:30:00
 18:30:00
 20:11:00
 10:11:00
(4 rows)

I want to compare these times to the actual (current) time + 45
seconds. If the current time + 45 seconds is higher than the time in
the table, I want "true". First, I tried this:

# select times, times > now()::time AS time_compare from live_stats;
  times   | time_compare
----------+--------------
 17:30:00 | t
 18:30:00 | t
 20:11:00 | t
 10:11:00 | f
(4 rows)

Which works, but I don't know how to apply the "45 seconds":

# select times, times > (now()::time + '45 seconds') AS time_compare
from live_stats;
ERROR:  operator is not unique: time without time zone + unknown
LINE 1: select times, times > (now()::time + '45 seconds') AS time_c...
                                           ^
HINT:  Could not choose a best candidate operator. You might need to
add explicit type casts.

# select times, times > (now()::time + '00:00:45') AS time_compare
from live_stats;
ERROR:  operator is not unique: time without time zone + unknown
LINE 1: select times, times > (now()::time + '00:00:45') AS time_com...
                                           ^
HINT:  Could not choose a best candidate operator. You might need to
add explicit type casts.

# select times, times > (now()::time + '00:00:45'::time) AS
time_compare from live_stats;
ERROR:  operator is not unique: time without time zone + time without time zone
LINE 1: select times, times > (now()::time + '00:00:45'::time) AS ti...
                                           ^
HINT:  Could not choose a best candidate operator. You might need to
add explicit type casts.

How am I supposed to do this comparison?

What I'm doing is that I have an application that queries the time and
if 45 seconds have passed, then it's supposed to update the table (to
reduce server load).


--
- Rikard - http://bos.hack.org/cv/

Rikard Bosnjakovic a écrit :
...
>
> Which works, but I don't know how to apply the "45 seconds":

SELECT now() + interval '45 seconds';

--
Lysistrata had a good idea.

Rikard Bosnjakovic wrote on 24.01.2010 11:08:
> I want to compare these times to the actual (current) time + 45
> seconds. If the current time + 45 seconds is higher than the time in
> the table, I want "true". First, I tried this:
>
> # select times, times>  now()::time AS time_compare from live_stats;
[...]

> Which works, but I don't know how to apply the "45 seconds":
>
> # select times, times>  (now()::time + '45 seconds') AS time_compare
> from live_stats;

You need to add an interval.

This should work:

SELECT times, times > (now()::time + interval '45' second) as time_compare
FROM live_stats;


Regards
Thomas

On Sun, Jan 24, 2010 at 11:28, Thomas Kellerer <spam_eater@gmx.net> wrote:

[...]
> You need to add an interval.

Thanks Thomas and Jean-Yves.


--
- Rikard - http://bos.hack.org/cv/