KB-111510: Grouping data from a Query within a time interval.

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:

How to group data in a given time interval

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:


Este artigo foi útil? Was this post helpful?
Yes0
No0

Leave a Reply

Your email address will not be published.Required fields are marked *