sqlpostgresqldatetimedate-arithmetichaving-clause

Posrgresql: Need query to check if last record for a group of two columns is older than 30 mins or not

Below is what I have written:

select column1, column2 
where time::time between "07:00:00: and "17:00:00: and time - now() > "-00:30:00" 
group by column1, column2;

If I run this query every 30 mins, will it fetch me the desired output?

Solution

  • I think you want a having clause:

    select col1, col1
from mytable 
group by col1, col2
having max(time) >= now() - interval '30 minutes'

    This returns all (col1, col2) tuples whose latest time is less than 30 minutes ago.