Question:
How can I group data from an Elipse E3 Query within certain time interval? For example, what can I do when I need the sampling time to show data from every 45 minutes? See the line below:
15:00, 15:45, 16:30, 17:15, etc.
Solution:
First of all, a Query is the object that helps defining queries in the application’s Database. Every time an E3Browser or a Report search for data in a DB, you will need to send a command; this command informs which data are necessary for that object. So, whenever an application needs to record or retrieve data from the Database, it will send SQL commands. A Query displays a friendly interface, which allows not only creating queries graphically but also immediately following the generated SQL syntax.
In order to group data in a Query within a given time interval, you can use an algorithm that generates a rounded up date respecting the 45-minute interval.
By using SQL Server functions, we are able to apply the algorithm as follows:
SELECT DATEADD(MINUTE,(DATEDIFF(MINUTE, 0 , E3TimeStamp)/45)*45,0) AS E3TimeStamp, SUM(Campo1) AS Campo1, COUNT(*) AS Total FROM TemperaturasCamara001 GROUP BY DATEADD(MINUTE,(DATEDIFF(MINUTE, 0 , E3TimeStamp)/45)*45,0) ORDER BY E3TimeStamp ASC
According to the image below, you can see that all data were displayed within the desired time interval, which is 45 minutes:
Since records are grouped every 45 minutes, the other selected fields must employ an aggregation function; these functions are AVG, COUNT, MAX, MIN, and SUM.
The related article, KB-13180: SQL query returning values every 10 minutes, illustrates another possible alternative for returning data within a certain time interval.
Related articles:
