The Default Start Time defines the start time for the historic result set, but is only used when there is no start time defined in the SQL Query. If the SQL Query includes a start time, the Default Start Time of the Historic View is ignored.
The Default Length for historic views defines the default duration of the historic result set. For example, if the Default Length is 2D, the historic result set begins at the start time defined in the SQL Query or by the Default Start Time setting and ends after 2 days. The Default Length setting is only used if an end time is not specified in the SQL Query.
You should use the OPC standard time formats when entering the Default Start Time and Default Length. For example, a Default Start Time of D-1W sets the default start time for the historic result set as 1 week ago and a Default Length of 1W sets the duration of the historic result set to 1 week. For more information, see Using OPC Time Formats.
Examples:
Example 1 - SQL Query includes Start Time and End Time Constraints
An SQL Query is to read the values calculated by ClearSCADA for a Historic View. The SQL Query specifies which points’ data is to be read and also specifies a time range for the historic result set (a start time and an end time).
SELECT
T1.ID, T1.NAME, T1.FULLNAME, T2.SUMMARY, T2.VALUE, T2.RECORDTIME, T2.QUALITY, T2.QUALITYDESC
FROM
CDBPOINT AS T1 INNER JOIN AVEHIS AS T2 ON T1.ID = T2.ID
WHERE
( T1.FULLNAME = 'New Analog Point' ) AND T2.RECORDTIME BETWEEN { TS '2008-08-01 00:00:00.00' } AND { TS '2008-09-01 00:00:00.00' }
This SQL Query specifies start and end times that cover the month of August and is used with a Historic View that has the following configuration:
- Name—AVEHIS
- Algorithm—Average. This defines the type of calculation that is performed.
- Reason Filter—Every reason enabled. This sets the Query to include the point’s historic records for the defined time range.
- Calculation Base Time—H. This sets the start time to be the beginning of an hour unit.
- Resample Interval—1H. This defines how often the algorithm value is calculated, in this case, every hour.
- Default Start Time—This is ignored as the SQL Query specifies a start time.
- Default Length—This is ignored as the SQL Query specifies an end time.
- Timezone—UTC. This sets the Historic View to use Coordinated Universal Time (UTC)(see Time Zone Support in ClearSCADA).
The Historic View is used to calculate the average hourly values for the point specified in the SQL Query. The time constraints included in the SQL Query are used to define the start time and end time for the historic result set. This means that the result set covers the month of August.
Example 2 - SQL Query includes Start Time Constraint Only
A third-party application uses an SQL Query to read the values calculated by ClearSCADA for a Historic View. The SQL Query specifies which points’ data is to be read and also specifies a start time for the historic result set. It does not specify an end time.
SELECT
T1.ID, T1.NAME, T1.FULLNAME, T2.SUMMARY, T2.VALUE, T2.RECORDTIME, T2.QUALITY, T2.QUALITYDESC
FROM
CDBPOINT AS T1 INNER JOIN AVEHIS AS T2 ON T1.ID = T2.ID
WHERE
( T1.FULLNAME = 'New Analog Point' ) AND T2.RECORDTIME >= { TS '2008-08-01 00:00:00.00' }
This SQL Query only specifies a start time: 2008-08-01 00:00:00.00. This means that the Default Length of the Historic View is used to determine the end time. The Historic View has the following configuration:
- Name—AVEHIS
- Algorithm—Average. This defines the type of calculation that is performed.
- Reason Filter—Every reason enabled. This sets the Query to include the point’s historic records for the defined time range.
- Calculation Base Time—H. This sets the start time to be the beginning of an hour unit.
- Resample Interval—1H. This defines how often the algorithm value is calculated, in this case, every hour.
- Default Start Time—D-1D. This is ignored as the SQL Query specifies a start time.
- Default Length—1D. This sets the end time of the historic result set to 1 day after the start time (which is specified as 2008-08-01 00:00:00.00 in the SQL Query).
- Timezone—UTC. This sets the Historic View to use Coordinated Universal Time (UTC) (see Time Zone Support in ClearSCADA).
The Historic View is used to calculate the average hourly values for the point specified in the SQL Query. As the SQL Query includes a start time constraint, the Default Start Time of the Historic View is ignored. However, the Default Length of the Historic View is used to determine the duration of the historic result set as the SQL Query does not include an end time. So the SQL Query reads the result set values from 2008-08-01 00:00:00.00 to 2008-08-02 00:00:00.00 (1 day after the specified start time).
Example 3 - SQL Query does not include Time Constraints
A third-party application uses an SQL Query to read the values calculated by ClearSCADA for a Historic View. The SQL Query specifies which points’ data is to be read but does not specify a time range for the historic result set (a start time and a length).
SELECT
T1.ID, T1.NAME, T1.FULLNAME, T2.SUMMARY, T2.VALUE, T2.RECORDTIME, T2.QUALITY, T2.QUALITYDESC
FROM
CDBPOINT AS T1 INNER JOIN AVEHIS AS T2 ON T1.ID = T2.ID
WHERE
( T1.FULLNAME = 'New Analog Point' )
This SQL Query does not include a start time or an end time. This means that the Default Start Time and Default Length settings of the Historic View are used to determine the time range for the historic result set. The Historic View has the following configuration:
- Name—AveHis
- Algorithm—Average. This defines the type of calculation that is performed.
- Reason Filter—Every reason enabled. This sets the Query to include the point’s historic records for the defined time range.
- Calculation Base Time—H. This sets the start time to be the beginning of an hour unit.
- Resample Interval—1H. This defines how often the algorithm value is calculated, in this case, every hour.
- Default Start Time—D-1D. This sets the default start time for the historic result set as 00:00 yesterday.
- Default Length—1D. This sets the default duration of the historic result set as 1 day.
- Timezone—UTC. This sets the Historic View to use Coordinated Universal Time (UTC) (see Time Zone Support in ClearSCADA).
The Historic View is used to calculate the average hourly values for the point specified in the SQL Query. As the SQL Query does not include any time constraints, the Default Start Time and Default Length settings of the Historic View are used. This means that the average time is calculated for the time period beginning at the start of yesterday (D-1D) and ending at the start of today (1D).