Provide additional time formatting options for RDBMS Queries

Background:

Today we are collecting data from our AspenTech IP21 Server thorugh the PI Adapter for OPC UA and the related AspenTech OPC UA Server. This works perfectly fine for analogue data but not for data which is providing us equipment status information as enumerated strings (0=On/1=Off etc.). This is just exposed as Int32 variable by the OPC UA Server but the enumeration strings are not exposed according to the OPC UA standards so that it is not being collected by PI Adapter for OPC UA natively. To get around that, we can use the PI Adapter for RDBMS and collect the data through AspenTech SQL+ as a workaround.

Challenge:

Currently, the PI Adapter for RDBMS is missing some maturity in order to specify how the timestamp placeholder for the query is passed over to the corresponding ODBC data source. As the data source might not be fully SQL92 compliant or does not understand ISO formatted time, it would be helpful to allow more options on how the timestamp is being formatted to be passed over to the placeholders used in the query.

Here a debug output example where this was tested against PI ODBC 2016:

When using a fixed date string like "t" or a specific date "1-Oct-23", the query works perfectly fine, but as soon when using the ?LST? query parameter it fails.

2023-09-30 12:20:12.048 +00:00 [Debug] Unable to run query, may retry: SELECT tag, time, DIGSTRING(CAST(value as int32)) as digstring, CAST(value as int32) as value FROM piarchive..picomp2 WHERE tag IN (SELECT tag FROM pipoint..pipoint WHERE pointtypex = 'digital') AND time > { ts '2023-09-30 12:18:01.535' } ORDER BY time ASC. Error: ERROR [HY000] [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'ts'. (DB_E_ERRORSINCOMMAND) (80040e14)

2023-09-30 12:20:15.059 +00:00 [Error] Failed to run query, query `pitags` will be skipped. ERROR: "ERROR [HY000] [PIOLEDB] [SQL Parser] [Line 1] Syntax error near 'ts'. (DB_E_ERRORSINCOMMAND) (80040e14)"

Solution:

Provide more options for timestamp query parameters and allow formatting options for the datetime/timestamp format to also allow non-fully-SQL92 compliant ODBC sources properly.