Configure the Time Horizon Query properties

Configure the time horizon for the tags that you configured. The Time Horizon is the timestamp to which the stream is progressed. This means it is the latest time for which we have data. If the time horizon value stays the same, then no dependent streams (such as reporting point capture or fields) progress.

The time horizon query returns a date time representative of a point in time that you are certain the database is up to.

For example, 5 minutes is subtracted from the latest sample in the database, regardless of Tagname.

select dateadd(minute, -5, Max(DateTime)) from ConnectorTable

Alternatively, call a store procedure that can return a datetime informs Workflow of the database time horizon.

exec usp_GetTimeHorizon

The time horizon query returns the time horizon value for each integration variable. There are a couple of different designs for time horizons:

• The stream for each integration variable is progressed to the timestamp of the last sample. This means that the streams for different integration variable can progress at different rates.

• The streams for integration variables are kept up to the same point. It is the earliest end timestamp of each of the variables.

Examples

Example 1: Each stream progresses at different rates

SELECT ISNULL(Max(DateTime), CAST(N'1/22/2022 12:00 AM' as DateTime)) as TimeHorizon from Example3 where VariableName = @TagName

Example 2: All streams progress at the same rate

SELECT ISNULL(MIN(MaxDateTime), CAST(N'1/1/2003 12:00 AM' as DateTime)) as TimeHorizon From ( SELECT MAX(DateTime) MaxDateTime, VariableName FROM Example3 GROUP BY VariableName ) A

Note: The time horizon query is invoked for every integration variable that you create.

To configure the Time Horizon Query properties

• Under the TimeHorizonQuery category, configure each of the listed properties. For more information on the TimeHorizonQuery properties, see Time Horizon Query properties details.

  

  1. Construct a query to run against the database to determine the timestamps on the last set of data extracted from the database.

     The time horizon sets the border on the current dataset in the historical stream so that the next dataset does not overlap.

     Example:

     select Max(DateTime)+ 1 from ConnectorTable

  2. Specify the time horizon query command type as Text or Stored Procedure.

  3. Define the parameters in the time horizon query using the Collection Editor.

     Specify Time Horizon Query Parameters:

     • In the case of “All streams progress at the same rate”, you can skip this step.

     • In the case of “Each stream progresses at different rates”, you only create one, for the Tag Name.

     

     Configure each of the required parameter properties. For more information on the parameter properties, see Time Horizon Query properties.

Related topics

• Database integration

• File integration

• About Integration Diagnostics