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 ...
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*