Datetime computations 1

[Naoise]

TableA

ID | Date_Time | Status
1 | 2005-06-10 13:00:28.407 | Success
2 | 2005-06-10 13:00:56.877 | Failure
1 | 2005-06-10 13:00:49.063 | Success
1 | 2005-06-10 13:00:51.547 | Success
1 | 2005-06-10 13:00:53.377 | Success


I want to return true or false if for a given ID there have been 3 failures in the last 60 minutes.

I can get the last 3 entries but how do I do the aggregate on the times vs the status? Is this quite a tricky statement?


select top 3 *
from TableA
order by Date_Time desc

Thanks for replies,
Naoise

 

[JarlH]

select distinct id,
(select case when count(*) > 2 then 'true'
else 'false' end
from tableA a2
where a2.id = a1.id
and (current_timestamp - DATE_TIME) minute(8)
< interval '60' minute(2)
and status = 'Failure')
from tableA a1;

 

[Naoise]

I'm getting incorrect syntax near minute? Thanks for replying to this, its a tricky one.

 

[Naoise]

Could you explain this part too especially minute(8) and minute(2) bits.

and (current_timestamp - DATE_TIME) minute(8)
< interval '60' minute(2)

Thanks

 

[JarlH]

If you get syntax error, your DBMS product isn't ANSI compliant. What DBMS product are you using? Maybe you shold ask your question in a vendor specific forum.

"current_timestamp" is used to get current timestamp (e.g '2005-06-13 09:11:43.021')

"(current_timestamp - DATE_TIME) minute(8)" calculates the difference between the current timestamp and a timestamp stored in the DATE_TIME column of your table. The result is returned as minutes. Specifying precision 8 for minutes means that the largest value allowed is 99999999 minutes. (This is done to avoid overflow, since the default precision 2.)

"interval '60' minute(2)" defines a 60 minute interval. (Specifying the precision 2 is actually unnecessary, since thats the default...)

 

[JarlH]

Note: to avoid dealing with timezones use LOCALTIMESTAMP instead of CURRENT_TIMESTAMP.