Retrieving averages of time (minutes, hours, and days) from an SQL Server database.

In this article, we will show you how to retrieve the averages from the table fields in an SQL Server database of three different time units: minutes, hours, and days. To do so, you must isolate all values from the field DateTime – E3TimeStamp – in the desired time units, and then group all data by using this same unit. This means that in order to generate the average of the values grouped by minute, you must manipulate E3TimeStamp so that any bit of information smaller than one minute is omitted, (in this case, information regarding seconds and milliseconds). This can be accomplished by using CAST command (which converts data types) and by performing some calculations. As for these calculations, you must remember that one unit of this date value is equal to one day. To calculate this average, you can use AVG command.

– Averages per minute: to obtain the averages per minute, you must retrieve E3TimeStamp’s seconds and milliseconds. To do so, multiply this field by 24 (to convert the time base into hours), and then by 60 (to convert it into minutes). The result must then be handled to isolate the integer, which corresponds to the minutes. Finally, divide everything by the same vale (1440 = 24 x 60) so that the data is converted again to date format. The result of these calculations will be used to group the data. 

SELECT CAST(CAST(CAST(CAST(E3TimeStamp as float)*1440 as bigint) as float)/1440 as datetime) as Minute, AVG(Field) as Average
FROM Table
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*1440 as bigint) as float)/1440 as datetime)

– Averages per hour:

SELECT CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime) as Hour, AVG(Field) as Average
FROM Table
GROUP BY CAST(CAST(CAST(CAST(E3TimeStamp as float)*24 as bigint) as float)/24 as datetime)

– Averages per day:

SELECT CAST(FLOOR(CAST(E3TimeStamp as float)) as datetime) as Dia, AVG(Field) as Average
FROM Tabela
GROUP BY CAST(FLOOR(CAST(E3TimeStamp as float)) as datetime)