in location

OR

I'm looking for

  • Wholesalers
    • Uncheck All Check All
  • EXPERTISE
    • Expand
    • Expand
    • Expand
    • Uncheck All Check All
  • Switchboard Manufacturers
    • Uncheck All Check All
OR

The best way to see what Clipsal electric products can do for your home is seeing them in action at a Clipsal Powerhouse Display Centre.

Product stock may vary according to location. If you're after a specific product range, please check with your chosen location before visiting.

10 Results
    Load More Load Complete
    Change Location

    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.

    Didn’t find what you were looking for?

    Try Searching Again View Our Categories

    Need further assistance?

    Our Customer Care department provides total customer service solutions for our residential, industrial and commercial applications.

    Get Assistance