Use DateTime functions
- Last UpdatedMar 20, 2025
- 2 minute read
Date functions perform an operation on a date and time input value and return either a string, numeric, or date and time value.
The following query returns the date/time stamp and value for the SysTimeSec tag for the last 10 minutes.
SELECT DateTime, TagName, Value, Quality
FROM History
WHERE TagName = 'SysTimeSec'
AND DateTime >= dateadd(Minute, -10, GetDate())
AND DateTime <= GetDate()
AND wwRetrievalMode = 'Cyclic'
The results are:
|
DateTime |
TagName |
Value |
Quality |
|
2001-12-15 13:00:00.000 |
SysTimeSec |
0.0 |
0 |
|
2001-12-15 13:00:06.060 |
SysTimeSec |
6.0 |
0 |
|
2001-12-15 13:00:12.120 |
SysTimeSec |
12.0 |
0 |
|
2001-12-15 13:00:18.180 |
SysTimeSec |
18.0 |
0 |
|
2001-12-15 13:00:24.240 |
SysTimeSec |
24.0 |
0 |
|
2001-12-15 13:00:30.300 |
SysTimeSec |
30.0 |
0 |
|
2001-12-15 13:00:36.360 |
SysTimeSec |
36.0 |
0 |
|
2001-12-15 13:00:42.420 |
SysTimeSec |
42.0 |
0 |
|
... |
For any query, the SQL Server performs all date/time computations in local server time, reformulates the query with specific dates, and sends it on to the AVEVA Historian OLE DB provider. The AVEVA Historian OLE DB provider then applies the wwTimeZone parameter in determining the result set.
For example, the following query requests the last 30 minutes of data, expressed in Eastern Daylight Time (EDT). The server is located in the Pacific Daylight Time (PDT) zone.
SELECT DateTime, TagName, Value FROM History
WHERE TagName IN ('SysTimeHour', 'SysTimeMin', 'SysTimeSec')
AND DateTime > DateAdd(mi, -30, GetDate())
AND wwTimeZone = 'eastern daylight time'
If it is currently 14:00:00 in the Pacific Daylight Time zone, then it is 17:00:00 in the Eastern Daylight Time zone. You would expect the query to return data from 16:30:00 to 17:00:00 EDT, representing the last 30 minutes in the Eastern Daylight Time zone.
However, the data that is returned is from 13:30:00 to 17:00:00 EDT. This is because the SQL Server computes the "DateAdd(mi, -30, GetDate())" part of the query assuming the local server time zone (in this example, PDT). It then passes the AVEVA Historian OLE DB provider a query similar to the following:
SELECT DateTime, TagName, Value FROM History
WHERE TagName IN ('SysTimeHour', 'SysTimeMin', 'SysTimeSec')
AND DateTime > 'YYYY-MM-DD 13:30:00.000'
AND wwTimeZone = 'eastern daylight time'
Because the OLE DB provider is not provided an end date, it assumes the end date to be the current time in the specified time zone, which is 17:00:00 EDT.