Technical FAQs

Ask a Question

ClearSCADA: How to use timestampadd in an SQL query

ClearSCADA 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 9:52am 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 525.  The original query returns the time and text of the note using the following code:

SELECT
    NOTETIME, NOTETEXT
FROM
    CDBOBJECT
WHERE
    ID = 525

This query results in the original time of 9:52:42.385 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 = 525

Here the timestampadd clause is used to display the notetime two hours earlier at 7:52:42.385


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 ClearSCADA historian at 9:55:49.713.  The first query using the following code to display the data as it is:

SELECT
    RECORDTIME, VALUE
FROM
    CDBHISTORIC
WHERE
    ID = 525

The result is a list with the original timestamp of 9:55:49.713 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 = 525

The result is a timestamp that displays as 7:55:49.713.
Was this helpful?
What can we do to improve the information ?