KB-13180: SQL query returning values every 10 minutes.

Question:

How can I have an SQL query return values every ten minutes, even when the Historic keeps recording these values every second?

Example:
4h 30min – tag1 = 1 – tag2 = 20
4h 40min – tag1 = 5 – tag2 = 23
4h 50min – tag1 = 3 – tag2 = 15

Solution:

To do so, you must use the following SQL query with the proper commands of the database you are using:

Example of query for Access databases:

SELECT Historic.E3TimeStamp,Historic.Internal1,Historic.Internal2,Historic.Internal3
FROM Historic
WHERE Second(E3TimeStamp) = 0 and Minute(E3TimeStamp) Mod 10 = 0
ORDER BY Historic.E3TimeStamp ASC

Example of query for SQL databases:

SELECT *
FROM TABLENAME
Where DATEPART(mi, E3TimeStamp)  % 10 = 0 AND DATEPART(ss, E3TimeStamp)  = 00
ORDER BY TABLENAME.E3TimeStamp ASC

Or, not using function “% 10”, thus:

SELECT *
FROM TABLENAME
WHERE (DATEPART(ss, E3TimeStamp) = 0) AND (DATEPART(mi, E3TimeStamp) = 0) OR (DATEPART(ss, E3TimeStamp) = 0) AND (DATEPART(mi, E3TimeStamp) = 10) OR (DATEPART(ss, E3TimeStamp) = 0) AND (DATEPART(mi, E3TimeStamp) = 20) OR (DATEPART(ss, E3TimeStamp) = 0) AND (DATEPART(mi, E3TimeStamp) = 30) OR (DATEPART(ss, E3TimeStamp) = 0) AND (DATEPART(mi, E3TimeStamp) = 40) OR (DATEPART(ss, E3TimeStamp) = 0) AND (DATEPART(mi, E3TimeStamp) = 50)
ORDER BY TABLENAME.E3TimeStamp  ASC

Obs.: Attached to this article is a demo application.

Attachments:

Sample.zip

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

Leave a Reply

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