user3447653 user3447653 - 5 months ago 14
SQL Question

Check whether table gets updated every five minutes

I am checking whether a table in Google Big Query gets updated every 5 minutes. We have a DateTime field in the table that gets a record for every 5 minutes starting from a specified time. I have to get the starting time (last record) of the table and check whether we have a record for the last 10 minutes from the current DateTime. In the result table, for each machine, I should have a "yes" or "no" based on whether table gets updated every 5 mins.

Current Query:

SELECT * FROM [Poll_20160505]
WHERE MachineName = 'P130'
ORDER BY DateTime DESC


Current output:

MachineName DateTime
P130 2016-07-07 15:54:13.407
P130 2016-07-07 15:49:13.438
P130 2016-07-07 15:44:13.437
P130 2016-07-07 15:39:13.426
P130 2016-07-07 15:34:13.419
.
.
P130 2016-07-07 00:09:13.037
P130 2016-07-07 00:04:13.005


Expected Output:

MachineName DateTime
P130 Yes


Edit:

I should have "yes" if the difference between current timestamp and the latest timestamp in the table is greater than 8 minutes (bcz table gets updated every 5 mins, giving a buffer of 3 mins).

From the above table, i have to find the difference between current timestamp and the timestamp of the first record (2016-07-07 15:54:13.407), if it is greater than 8 mins, then i should have a "no", else i should have "yes".

Answer

I hope, it is as simple as below

SELECT 
  MachineName, 
  CASE 
    WHEN TIMESTAMP_TO_SEC(CURRENT_TIMESTAMP()) - TIMESTAMP_TO_SEC(recentDateTime) < 8 * 60 THEN "Yes" 
    ELSE "No"
  END AS Healthy
FROM (
  SELECT MachineName, MAX(DateTime) AS recentDateTime 
  FROM YourTable
  GROUP BY MachineName
)