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
SELECT *
FROM TABLENAME
Where DATEPART(mi, E3TimeStamp) % 10 = 0 AND DATEPART(ss, E3TimeStamp) = 00
ORDER BY TABLENAME.E3TimeStamp ASC
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
Attachments:
