Calculating Items per Hour from Timestamps

Grafana
1

I have an sql database with timestamps every time an item is made, and there would be periods of time where no items are made.

I need to calculate the items/per throughput of this database. Right now I am using the following but I’m not entirely sure how accurate it is:

WITH time_gaps AS (
  SELECT 
    date_time,
    TIMESTAMPDIFF(SECOND, LAG(date_time) OVER (ORDER BY date_time), date_time) AS gap_seconds,
    EXTRACT(HOUR FROM date_time) as hour_of_day
  FROM mydb.machine_01
),
filtered_gaps AS (
  SELECT 
    date_time,
    gap_seconds,
    CASE 
      WHEN hour_of_day >= 7 AND hour_of_day <= 22 
      AND gap_seconds < 100
      THEN gap_seconds
      ELSE 0 
    END as operational_gap
  FROM time_gaps
)
SELECT 
	DATE(date_time) as day,
    SUM(operational_gap)/3600 as uptime_hours
FROM filtered_gaps
GROUP BY DATE(date_time)

This basically takes the timestamp difference between all timestamps, filters out any where the time is after 10pm and before 7am (our operating hours) and also filters out timestamp differences of 100 seconds (I wanted to get rid of any large gaps in output since technically the machine is not operating if the timestamp differences exceed 100 seconds).

Is there a more accurate way of calculating this? I have this in Grafana and use an additional WHERE clause at the end for the time filter, so in Grafana it looks like:

WITH time_gaps AS (
  SELECT 
    date_time,
    TIMESTAMPDIFF(SECOND, LAG(date_time) OVER (ORDER BY date_time), date_time) AS gap_seconds,
    EXTRACT(HOUR FROM date_time) as hour_of_day
  FROM mydb.machine_01
),
filtered_gaps AS (
  SELECT 
    date_time,
    gap_seconds,
    CASE 
      WHEN hour_of_day >= 7 AND hour_of_day <= 22 
      AND gap_seconds < 100
      THEN gap_seconds
      ELSE 0 
    END as operational_gap
  FROM time_gaps
)
SELECT 
	DATE(date_time) as day,
    SUM(operational_gap)/3600 as uptime_hours
FROM filtered_gaps
WHERE $__timeFilter(date_time)
GROUP BY DATE(date_time)
2

welcome @pmclean

I think this forum might be best to ask what seems like a very SQL Server specific question.

3

Actually I figured out an easier and actually 100% accurate way to do it, I just need some Grafana specific assistance:

SELECT COUNT(*) FROM mydb.machine_06 WHERE $__timeFilter(date_time);

This is my new query which is basically just counting all the values between whatever time period is selected in Grafana. However, I need to then divide this result by the # of hours selected in the $__timeFilter to get the “Items/hour” value. Can you help with that last part?

4 
SELECT TIMESTAMPDIFF(hour,$__timeFrom(),$__timeTo()) as hours
 from mydb.machine_06 
where $__timeFilter(date_time);