This documentation supports releases of BMC Helix Continuous Optimization up to December 31, 2021. To view the latest version, select the version from the Product version menu.

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