Determining how to aggregate data to a given resolution using SQL

This topic explains how you can truncate a date to 5 minutes in order to perform a grouping before importing data.

If using Oracle, execute the following command:

SELECT TRUNC($tscolumn,'DD') + (FLOOR(TO_CHAR($tscolumn,'SSSSS')/(300))*(300))/86400 FROM ...

If using SQL Server, execute the following command:

select CAST((floor(CAST($tscolumn as float)) + ceiling((floor(((CAST($tscolumn as float) - floor(CAST($tscolumn AS float)))*86400)/$eres)*$eres)/.864)/100000) as datetime) from ...

The following command will return the date in the standard format YYYY-MM-DD HH:MM:SS:

SELECT convert(char,$tscolumn, 120) AS Timestamp from ...
Was this page helpful? Yes No Submitting... Thank you