Geo SCADA: How to use timestampadd in an SQL query

Geo SCADA allows a user to query its database using SQL queries.  Where necessary (i.e. across different time zones) it is possible to display the timestamp with an offset.  This article describes how to use the timestampadd clause within an SQL query to change the way the timestamp is displayed.

In the first example below a note was added to a point at 4:14:04pm local time.  Two queries are shown: one which displays the note with it's original timestamp and the second which displays the note with an offset of -2 hours.





The point (that the note is associated with) has an object ID of 20283.  The original query returns the time and text of the note using the following code:

SELECT
NOTETIME, NOTETEXT
FROM
CDBOBJECT
WHERE
ID = 20283

This query results in the original time of 16:14:04.116 being displayed in the embedded list.

The modified query returns the time and text of the note with a -2 hour offset using the following code:

SELECT
{ FN TIMESTAMPADD( SQL_TSI_HOUR, -2, NOTETIME ) } AS NOTE_OFFSET, NOTETEXT
FROM
CDBOBJECT
WHERE
ID = 20283

Here the timestampadd clause is used to display the notetime two hours earlier at 14:14:04.116


In the second example below the timestampadd clause is used to display the timestamp of a historic value with a two hour offset.






The original historic value was logged to the Geo SCADA historian at 4:16:21.055.  The first query using the following code to display the data as it is:

SELECT
RECORDTIME, VALUE
FROM
CDBHISTORIC
WHERE
ID = 20283

The result is a list with the original timestamp of 4:16:21.055 and the value.

The modified query returns the timestamp and value with a -2 hour offset using the following code:

SELECT
{ FN TIMESTAMPADD( SQL_TSI_HOUR, -2, RECORDTIME ) } AS TIME_OFFSET, VALUE
FROM
CDBHISTORIC
WHERE
ID = 20283

The result is a timestamp that displays as 2:16:21.055.

Publicado para: Schneider Electric Mexico