We have a database that uses the SQL Server datatype DATETIMEOFFSET in many tables. Any time we query a table that uses it in one of the columns we receive the following message. Is there away around this issue?
GatewayException: java.sql.SQLException: Unknown SQL type: -155
caused by Exception: java.sql.SQLException: Unknown SQL type: -155Ignition v8.0.5 (b2019101516)
Java: Azul Systems, Inc. 11.0.4
I would expect that to mean you need to update your JDBC driver.
I have updated to JDBC driver 7.4.1 and also restarted the gateway with no change.
I found this url that talks about mappings to java, but not sure how or where this would be done.
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver15
I am stuck at the moment because I cannot even get the data into Ignition from the query to even try to change the datatype. Is there some way in Ignition settings or the driver setup I can specify how to interpret the datatimeoffset datatype?
The mappings are done in the database translator tab of the database driver page in the Web config (from memory).
Nope, I think I looked at this a while ago and couldn’t see how to add it in… might be one for the guys and girls at IA!
Anyone from Inductive Automation have any input on this? Is there something we’re missing that would allow use to query a SQL Server datetimeoffset column without getting an Unknown SQL Type error? And if not, are there any plans to support this data type in the future?
In your query, try cast the DATETIMEOFFSET field. Looks like casting as a varchar keeps the datetime plus offset intact in the string. Casting as a datetime type keeps the datetime but loses the offset.
Something like this: SELECT CAST(yourcolumn AS VARCHAR) AS DTO FROM yourtable
Apparently the date time with time zone fields in other databases such as postgre and mysql work with Ignition, so it seems like a bug if they’re not working with sql server as well
That’s an interesting approach. Part of the issue is we have an ASP.NET application which needs to get the same result set from the stored procedures, and we don’t want to be in the position of maintaining twice as many procedures, with one set for ASP.NET and one set for Ignition.
The ASP.NET application was written to expect and handle the datetimeoffset datatype, but it’s possible that it will accept the varchar and know how to interpret it. I’ll try that on a test copy of that and see how it goes.
That said, it would be far preferable for this to just work without requiring a kluge.
I know this is old, but has this been addressed in the latest version 8.0.12? Right now I am stuck at v8.0.6 because that is what Sepasoft v3.80 RC1 supports. But I heard that the next release from Sepasoft will support Ignition 8.0.12.
I just tested and this is still an issue in Ignition v8.0.13
I wouldn’t hold your breath. Microsoft chose to implement timezone support for datetime with an overspecified storage format. That is, any instant in time has multiple possible storage values because it includes the time zone. Other databases implement timezone support as a property of the database connection and just store UTC for the instant in time, converting to/from the connection timezone only as needed. Java’s native Date internally stores UTC milliseconds and converts as needed to/from the local timezone, so it is binary-compatible with the latter family of databases. Not compatible with Microsoft’s DATETIMEOFFSET, and no easy way to make it compatible.
Interesting to know the reason why we’re screwed when using MS SQL! Maybe it’s time to start looking at postgre… Sometimes I’m not sure we can change it out though, because that’s what our customers’ IT know and can support.
I try to convince my clients to run both the database and the Ignition gateway in UTC, specifically to ensure all datetimes in the DB are UTC, with no daylight savings time discontinuities. This allows Ignition to handle all of the timezone conversions. Which it does beautifully.
My clients with first class IT are usually already there.
The IT I deal with mostly are the ones where you tell them 4 weeks in advance that you'll be onsite to commission something, and that they need to have the switches configured to allow PLC connections. They send you an email the week before saying its done. You rock up on site and the switch is still in its new box... Unopened
Hahahaha! My clients aren't anywhere near that challenging.
@pturmel Thanks for the insight on this topic.
At least yours have switches… I’ve been to facilities with hubs, yes hubs not switches, everywhere wondering why things are so bad.
I know this post has been addressed. But I thought I would share this…
So I am now using datetime data type for the timestamp instead of datetimeofffset. I am storing the timestamp as UTC. I then use a feature of Microsoft SQL Server “AT TIME ZONE” to convert UTC to the local timezone with daylight savings calculated. I then convert the calculated datetime with offset to nvarchar(30) so that Ignition will accept it. This feature exists in SQL Server 2016 or greater.
This is an example T-SQL script for the timezone and daylight savings conversion.
declare @preDST datetime = '2018-03-10 7:00:00'
declare @postDST datetime = '2018-03-11 7:00:00'
select cast(@postDST at time zone 'UTC' as nvarchar(30)) as postdstUTC, cast(@postDST at time zone 'UTC' at time zone 'EASTERN STANDARD TIME' as nvarchar(30)) as postdstEST
-- 2018-03-10 07:00:00.000 +00:00, 2018-03-10 02:00:00.000 -05:00
select cast(@postDST at time zone 'UTC' as nvarchar(30)) as postdstUTC, cast(@postDST at time zone 'EASTERN STANDARD TIME' as nvarchar(30)) as postdstEST
-- 2018-03-11 07:00:00.000 +00:00, 2018-03-11 03:00:00.000 -04:00