Hello community,
I've been playing around with SQL bridge for a particular need for our customer.
Have 2 questions tho, hope you could provide some feedback:
Is it possible to record the option "store timestamp to" in UTC? (did some tests turning MSSQL service down and it stores its local time internally and when MSSQL service is back again, it will write to DB the exact local time where event got triggered)
In 8.1 docs, there is a caution clause: "If the group errors due to a bad database connection, it will need to be manually restarted once the database connection is brought back" does that mean that there is a risk with SQL bridge that it would need us to restart the whole gateway service and/or the transaction groups if the database server fails for certain amount of time? If so, is there a way to prevent this risk?
Try changing your t_stamp column type to datetime2. MS SQL's datetime column type is not timezone-aware, and is not suitable for use with vanilla JDBC if multiple timezones are involved.
Tried your suggestion but timezone is being truncated if I use datetime2(7).
I would need to use datetimeoffset(7) but seems like SQL Bridge does not send timezone at all?
As an example, changed the timestamp column datatype to varchar and what it writes is something like this:
"2025-02-26 10:50:10.2520000"
I was expecting to write the following format: "yyyy-MM-dd HH:mm:ss[.nnnnnnn] [+|-]hh:mm"
Could this be due to an older driver? I cannot see what version is (JDBC Drivers do not specify what version I am running [Config/Databases/Drivers])
Ignition datetime objects are timezone-aware. Through all of Ignition, including in the SQL Bridge module.
JDBC sends a date object that is fundamentally UTC. The brand-specific JDBC driver converts that however it likes based on the column type it thinks applies. Most DBs will drop the timezone, and use the DB connection's configured (or implied) timezone, unless the target column is timezone-aware.
Possibly. You should look at Microsoft's compatibility chart for your DB and java 11 or 17 (depending on your actual Ignition version).
If you wish to have the least trouble with multi-zone operations, consider discarding Microsoft SQL Server in favor of PostgreSQL. The latter's timestamptz column is perfectly matched to Ignition/Java/JDBC's datetime objects.
Couldn't figure out why I kept losing the timezone value...
Tried updating to the latest MSSQL odbc drivers, uninstalling the whole gateway and installing the latest version published and nothing.
Ended up doing this (which it does work in MSSQL 2019):
Created an expression tag and set it up as string:
dateFormat(now(),"yyyy-MM-dd k:mm:ss XXX")
In SQL Bridge, unchecked the "Store timestamp to" option
Added the custom expression tag string to the group items and mapped to "t_stamp"
On MSSQL, I set the column type for t_stamp as datetimeoffset (MSSQL will do an implicit conversion from text to datetimeoffset).
A T-SQL code example based on my previous comments would be something like:
;WITH result(mydatetime) AS(
SELECT CAST('2025-02-27 14:31:58 -06:00' AS datetimeoffset)
)SELECT *, mydatetime AT TIME ZONE 'UTC' AS UTC_Value
FROM result;