AVG columns by time frame

Ignition
1

Im using this query to AVG 2 columns:
SELECT
FORMAT(CONVERT(datetime,AVG(CONVERT(FLOAT,(“3A_End_Time” - “3A_Start_Time”)))), ‘HH:mm:ss’) AS AvgDuration
FROM
Misc_Rail_LGV_Call_Time
How can I add a time frame into this, if I wanted the AVG the last 12hrs.

2

You didn’t specify which DB you’re using, but either way you would need to add a WHERE clause to your query. In MySQL it would look something like this:

SELECT FORMAT(CONVERT(datetime,AVG(CONVERT(FLOAT,(“3A_End_Time” - A_Start_Time”)))), ‘HH:mm:ss’) AS AvgDuration
FROM Misc_Rail_LGV_Call_Time
WHERE t_stamp > DATE_SUB(NOW(), INTERVAL 12 HOUR))
3

Alright, I found out that this here works:

WHERE
t_stamp >= DATEADD (HOUR, -12, getdate())

1 Like