Learn about timestamps

TS placeholders initialization after startup

When the interface starts, all TS placeholders are populated with the current snapshot timestamps of individual PI points.

Interface internal snapshot

To ensure that all values added to a table since the last successful scan are returned, use the TS placeholder in combination with ordering the result-set by the timestamp column (ASCending). The majority of SELECT query examples in this manual have it so. Periodical execution of such queries enables reading data from RDBMS tables in ordered chunks and makes sure the interface can be temporarily stopped without losing data, because the very first execution of such SELECTs "recovers" all rows since the last successful scan.

Note: The interface internal snapshot represent the timestamp of the last row returned in a result-set. It does not have to be the PI snapshot, because due to exception reporting, not all rows from a result-set are guaranteed to be forwarded to PI.

Binding to ODBC SQL_TIMESTAMP data type

The implementation of the timestamp type varies among the database vendors. Fortunately, the ODBC layer nicely hides these implementation variances and offers the SQL_TIMESTAMP data type, which enables the interface handles timestamps consistently without regard for the underlying RDBMS. All timestamp related placeholders, that is: TS, ST, LET, as well as columns in the SELECT list, which end up as PI timestamps, are bound to this type. That means that there is no need to cast anything to text and deal with various timestamp formats.

UTC timestamps

The interface can handle UTC timestamps from both the RDBMS and the PI System. If the timestamps in the RDBMS are stored as UTC data, enable UTC handling using PI ICU: go to the rdbodbc > OptionalParameters tab and check Times are UTC.

Future timestamps

The PI Data Archive rejects timestamps that are more than ten minutes ahead of its system time, unless the new PIPoint attribute future (introduced in PI Server 2015) is set. To prevent this problem, make sure that the system time on the PI Server and RDBMS host computers are synchronized and, if necessary, define queries that return only rows that are timestamped less than ten minutes ahead. The following two examples illustrate this approach; sysdate is Oracle's "current time" function, and "10*60./86400." is an expression for ten minutes:

SELECT timestamp, value, status
FROM table1
WHERE timestamp BETWEEN ? AND
sysdate+10*60./86400. ORDER BY timestamp ASC;

The same ten-minute safe-guard with the MS SQL Server's getdate() "current time" function:

SELECT timestamp, value, status
FROM table1
WHERE timestamp BETWEEN ? AND
getdate()+10*60./86400. ORDER BY timestamp ASC;

Since PI Server 2015, the points with future attribute set to one can accept future timestamps and the considerations described above do not apply. The interface does not have any restrictions towards points with future=1.

Timestamp precision

The PI System can handle timestamps with precision up to 100 microseconds. For example, 20-Mar-2015 11:10:01.1234 is a valid PI timestamp. However, the maximum precision of timestamps obtained through ODBC calls is one millisecond. To work-around this, the interface recognizes the "numeric timestamps"; that is, accepts a column containing the number of seconds since 01-Jan-1970 (Unix timestamps) where the decimal part of this number represents the 100 microseconds and translates it to PI timestamp. Note that such numeric timestamp column must be explicitly aliased by the PI_TIMESTAMP keyword:

SELECT timestamp_double AS PI_TIMESTAMP, value AS PI_VALUE, 0 AS PI_STATUS
FROM table1
WHERE timestamp_double > DATEDIFF(s,'01-Jan-1970 00:00:00',?) +
timestamp_double - CAST(timestamp_double AS int);