Relational Database(rdbms via odbc) Interface

Timestamp Format

Even though the timestamp data type implementation is not consistent among various RDB vendors, the ODBC specification nicely hides these inconsistencies. For an ODBC client, the timestamp (DateTime) data type is always unified (the ODBC data type marker for a timestamp column is SQL_TIMESTAMP). Thanks to this unification, the generic ODBC clients can easily work with many data sources without worrying about the data type implementation details.

The RDBMSPI interface recognizes two places where a timestamp data type can appear (depending on which kind of query it executes):

• 
  Input timestamps (those used in the SELECT's column lists, which are, along with the value and status, sent to PI)
  
• 
  Timestamps used as query parameters (through placeholders).
  

This chapter briefly describes both of them.

Timestamp in SELECT’s List as Numeric Data Type – Support for Sub-milliseconds

The interface by default expects that the input timestamps are the native timestamps (SQL_TIMESTAMP). However, in the RDBMSPI Interface version 3.14 and greater, it also allows for the numeric representation of a timestamp. For example, in an RDB table, the timestamp column can be in the numeric form: Double or Integer. It is assumed that such a numeric timestamps represent the number of seconds since 01-Jan-1970 UTC). One of the advantages/reasons why the numeric timestamps are implemented is that the double timestamp can go behind the millisecond precision (while the ODBC's SQL_TIMESTAMP can only store milliseconds). An example of a SELECT with a numeric timestamp can as follows:

SELECT timestamp-as-number AS PI_TIMESTAMP, value AS PI_VALUE, 0 AS PI_STATUS FROM table WHERE …;

The interface automatically detects that the timestamp-as-number column is not SQL_TIMESTAMP and transforms the number to the PI timestamp accordingly.

Note: The timestamp-as-number can only be used in the aliased mode (see chapter Data Acquisition Strategies – Option 2: Arbitrary Position of Fields in a SELECT Statement – Aliases). That is, the numeric column needs to be aliased using the PI_TIMESTAMP keyword.

CAUTION! The numeric timestamps can also only be used in the SELECT lists and not as placeholders. The following query will therefore NOT be accepted:

SELECT Timestamp-as-number AS PI_TIMESTAMP, Value AS PI_VALUE, 0 AS PI_STATUS FROM Table WHERE Time-as-number > ?; P1=TS

To overcome this, the numeric timestamp must be converted to the appropriate timestamp data type explicitly. Following are two examples that show how to convert the timestamp-as-number column to the native timestamp. The first example uses the ODBC extension function TimestampAdd(), the second is an example that uses the Oracle’s built in function To_date().
SELECT Time-as-number AS PI_TIMESTAMP, Value AS PI_VALUE, 0 AS PI_STATUS FROM table WHERE {fn TIMESTAMPADD(SQL_TSI_SECOND,Time-as-number, '1970-01-01 00:00:00')} > ?; P1=TS

SELECT Time-as-number AS PI_TIMESTAMP, Value AS PI_VALUE, 0 AS PI_STATUS FROM Table WHERE (to_date('01-Jan-1970') + Time-as-number/(24*3600)) > ?; P1=TS

Both examples only convert numbers that represent whole seconds since 01-Jan-1970. That is, the millisecond part is truncated in the conversion!

Timestamps as Query Parameters – Placeholders

The following tables list all the time-related placeholders’ definitions supported by the interface. Because there are implementation differences between input and output points the first table describes keywords used with input points.

Timestamp Placeholders – Input Points

Keyword	Time Used
Input:
TS	TimeStamp (Internal Interface snapshot) Example: Interface scans the RDB table for only the newly INSERTed rows: SELECT Timestamp,Value,0 WHERE Timestamp > ? ORDER BY Timestamp ASC; P1=TS Note: due to the exception reporting mechanism, this placeholder does not always correspond with the visible PI Snapshot. In reality, the placeholder represents the latest value of a timestamp arrived from a query and this timestamp is then kept in the interface internally; throughout this document we reference it as Internal Interface Snapshot. It is therefore highly recommended to ORDER the SELECTed time-series by the timestamp column! With the above query the Snapshot and placeholder timestamps can be as follows: Current PI Snapshot: 20-Oct-2008 08:00:00 Latest timestamp in the result set: 20-Oct-2008 08:01:10 Placeholder P1 is populated with: 20-Oct-2008 08:01:10 Since PI accepts only snapshot times that are no further than 10 min ahead of the PI Server current time, users should be aware of a situation that timestamps retrieved from RDB can violate this limitation. It is therefore recommended to construct a query with a safeguard, which out-filters the future data entries: SELECT Timestamp,Value,0 FROM Table WHERE Timestamp > ? AND Timestamp < sysdate+10*60/86400 ORDER BY Timestamp; P1=TS Note: In the above query – the sysdate is Oracle's current time and '10*60/86400' is an expression for 10 minutes. For other thanOracle RDBMSs the query will of course look different. Another prerequisite is having the PI Server and RDB times synchronized.
TE	TimeStamp End. During input history recovery, this timestamp is automatically populated with TS + the recovery step, see chapter RDBMSPI – Input Recovery Modes for more details. In on-line mode, the TE is populated with current time.
LST	Last Scan Time Can be used to limit the amount of data obtained by executing the SELECT query to only newly inserted rows since the last scan. The amount of selected rows is therefore DEPENDENT on the scan frequency (allows longer scan periods at the cost of potentially bigger result-sets). Example: SELECT Timestamp,Value,0 WHERE Timestamp > ? ORDER BY Timestamp ASC; P1=LST Note: LST is always updated, even if the query fails.
ST	Scan Time. Time when a given scan class is scheduled. A good example is to use this time to avoid transfer of future data from a table Example: SELECT Timestamp,Value,0 WHERE Timestamp > ? AND Timestamp < ? ORDER BY Timestamp ASC; P1=TS P2=ST
LET	Last Execution Time Time when the previous tag execution has finished. Queries can take some time to execute and LET thus differs from LST. When there are more statements defined (that is, a batch of SQL statements is executed), LET is the time when the last statement finished execution. That also means that LET is different for each query. Note: LET is not updated if a query fails. On multi-statement query files LET is updated until the first query fails (no further queries are executed in the batch).
ANN_TS	PI Annotation in the form of DateTime. If the tag’s snapshot does not have any annotation, the value is undefined (NULL).

The output points (points that do have the SourceTag attribute populated) direction interprets the placeholders as follows:

Timestamp Placeholders – Output Points

Keyword	Time Used
Output:
TS	Snapshot TimeStamp of a source tag (for an output tag), or any foreign tag pointed to by its name ('tag name'/TS) Example: INSERT INTO Table (Timestamp,Value) VALUES (?,?); P1=TS P2=VL Note: The first question mark will be populated by the Source Tag's snapshot. That is, it is not necessary to define P1 as P1='sourcetag'/TS
ST	At interface startup: ST=Snapshot Time, from that time on: ST=event time
ANN_TS	PI Annotation in the form of DateTime. If the tag’s snapshot does not have any annotation, the value is undefined (NULL).

Important Considerations Related to Timestamps

• 
  All Timestamp Placeholders are populated with Snapshot TimeStamp at Interface Start-up.
  At interface startup, all timestamp placeholders are preset with the PI Snapshot timestamps. This, for example, allows for the temporary stops of the interface in case the input query is like: SELECT … WHERE Timestamp > ?; P1=TS
  You can stop the interface for a while, let the data buffer in an RDB tables and the first query execution after the interface start will get all the rows since the last one retrieved; that is, since the Snapshot timestamp. If the ANN_TS placeholder is used and the snapshot of the corresponding PI tag is not annotated, the value of this placeholder is undefined (NULL).
  
• 
  Internal Interface Snapshot.
  For input tags – the TS will be taken from the Internal Interface Snapshot. See the table above for more details on this term.
  
• 
  SELECT Statement without Timestamp Column.
  The interface offers the execution time for the input points when the RDB table does not have the timestamp column available. If the interface runs on an API node, the employed execution time is synchronized with the PI Server. An example of the timestamp-less query can be as follows:
  SELECT Value,0 FROM Table WHERE …;

  Another alternative is to use the timestamp provided by the RDB. Either use the ODBC function {Fn NOW()} or use the appropriate (database specific) built-in function. The second query uses the Oracle's sysdate function:

  
  SELECT {Fn NOW()},Value,0 FROM Table WHERE …;
  SELECT sysdate,Value,0 FROM Table WHERE …;
  
• 
  Timestamps have to contain Both – Time and Date
  The interface always expects the full timestamp (date+time). It does not implement any automatic date completion in case there is just the time column available in RDB.