# JDBC and MSSQL Datetimeoffset **URL:** \ **Category:** Ignition\ **Created:** [June 24, 2025, 6:26pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729 "2025-06-24T18:26:50Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [June 24, 2025, 6:26pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/1 "2025-06-24T18:26:51Z")
Hi all, Long time reader, first time poster. ๐Ÿ™‚ I was looking into some discussions here about the JDBC driver for SQL Server and Ignition and I'm a little confused as to why everything doesn't play nicely together. I had read most of the posts on this topic across the forums but many of them were a few years old. For those who haven't tried it, there will be an error generated when attempting to read a SQL Server resultset that contains a datetimeoffset type (unknown SQL type : -155). I've done a bit of trial and error, and from what I can tell on the SQL server side, you can convert the datetimeoffset to datetime2 or varchar or a unix timestamp before selecting it in a resultset and it will read appropriately, but it means any time you pass dates back to Ignition those columns need to be converted. If you are passing dates from Ignition into stored procedures or functions, you can specify an OffsetDateTime and pass it as a string, and that seems to convert okay. From what I can tell, the JDBC driver has its own DateTimeOffset class which can be converted to a timestamp or other Java types, and other uses of the JDBC driver use that intermediate class to read those columns. For whatever reason, that doesn't seem to be an option with Ignition (as far as I can tell). There's also no connection settings that seem to modify how the dates get returned (again, as far as I can tell). The conversion option will work, but it seems very strange when it seems like everything is speaking the same language already. Has anyone run into a solution for this? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 24, 2025, 7:56pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/2 "2025-06-24T19:56:21Z")
Microsoft, as usual, didn't implement their JDBC driver using standard JDBC data types. Ignition supports the JDBC standard so that it can talk to many brands of database. For technical reasons (connection pooling in particular), the true JDBC connection is not exposed elsewhere in Ignition, so brand-specific datatypes and methods are not supported. Brand-specific layers in such drivers are used to lock users into a particular brand. Also the usual behavior from Microsoft. The short term solution is as you've identified--convert your column types in views or procedures. The long term solution is to fire Microsoft and deploy a competent, standards-compliant database technology. I generally recommend PostgreSQL. ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [June 24, 2025, 9:12pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/3 "2025-06-24T21:12:02Z")
Hi pturmel! I've certainly read a few of your posts on this topic. ๐Ÿ™‚ I would not expect anything beyond the JDBC layer to know about anything beyond the standard Java classes. My confusion is that the Microsoft-specific class can be converted to the standard Java classes, and so nothing beyond the parts of ignition that handle JDBC connections need to know anything about Microsoft and their database classes. Like if I can convert to a timestamp or UTC or whatever in SQL, surely the Ignition database methods can handle that conversion instead? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 24, 2025, 9:15pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/4 "2025-06-24T21:15:52Z")
No. Only the JDBC vanilla methods are available on the JDBC layer in Ignition. **Nothing** in Ignition is "Microsoft-aware" (or Oracle-aware or PosgreSQL-aware), so cannot invoke the conversions if it doesn't automatically happen in the resultsets. And it doesn't, because Microsoft. You should fire them if at all possible. ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [June 25, 2025, 1:20pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/5 "2025-06-25T13:20:49Z")
Aha, I think I understand. Basically, the JDBC driver for Microsoft doesn't make that conversion available in a standard Java way, and Ignition isn't going to make the extra step to convert a Microsoft-specific class, so there's a logical impasse which I see as an exception. When I'm reading folks talking about extending Java ORMs to handle datetimeoffset with the Microsoft JDBC driver, they're basically bridging that gap for themselves by handling the conversion. That said, presumably I could do the same thing even if it's not a standard Ignition feature. Hmm... As I mentioned I read your posts before so I have considered Postgres - it isn't _not_ an option but given the state of the project I'm working on, I don't think I could justify swapping database technologies for convenience in datetime types. Sometimes you've got to make do with what you have. ๐Ÿ™‚ I forgot to say thank you for the help earlier, but thank you for the information - you've given me plenty to think about. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 25, 2025, 1:27pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/6 "2025-06-25T13:27:03Z")
> [@nicole.c](#): > > justify swapping database technologies for convenience No, probably not. But these: - Standards compliance (identifier quotes, limit clauses, on conflict clauses, et cetera) - Life cycle cost (particularly no need for CALs, nor for $$ extra functionality) - Non-windows deployment with full features (MS's SQL Server for Linux is not) so you can avoid [Windows in production environments](https://forum.inductiveautomation.com/search?q=engineering%20malpractice%20order%3Alatest) You should fire them if at all possible. ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [June 25, 2025, 6:41pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/7 "2025-06-25T18:41:29Z")
> [@pturmel](#): > > You should fire them if at all possible. For sake of argument, I'll say it's not possible, and I'm definitely going to agree to disagree on the topic of what constitutes engineering malpractice ๐Ÿ™‚ ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 25, 2025, 6:54pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/8 "2025-06-25T18:54:54Z")
> [@nicole.c](#): > > agree to disagree Heh. Everyone has opinions. They can even be _ **wrong** _. ๐Ÿคทโ€โ™‚๏ธ ๐Ÿคฃ ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [June 25, 2025, 9:01pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/9 "2025-06-25T21:01:44Z")
Anyhow, from my tinkering, I can use the gateway database connections to query a datetimeoffset, extract it in the resultset, and do things with it (convert to timestamp, etc). It looks like running it through the conversion from a JDBC resultset to the internal Ignition class(es?) like BasicStreamingDataset is where Ignition gets grumpy with it, which I guess tracks with the above discussion. I was hoping there might be some way to get the data into an agreeable object or fall back to a string conversion - ie, if there are easy approaches to add to the list of types to map. I did see Ignition's TypeMappings class which doesn't look to be modifiable, but is definitely missing the -155 SQL type. I imagine there are some brute force options if I really wanted to make it work, but it would be way more effort than using a convert to datetime2 ๐Ÿ™‚ ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [August 8, 2026, 4:43pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/10 "2026-08-08T16:43:48Z")
Hi all! Sorry for the thread necromancy, wasn't sure if it was best to drop in a new thread or add to the same discussion. I noticed there's a preview version of the JDBC SQL Server driver 13.5 that includes a change that might be relevant: [Deprecate microsoft.sql.DateTimeOffset in Favor of java.time.OffsetDateTime ยท Issue #2905 ยท microsoft/mssql-jdbc](https://github.com/microsoft/mssql-jdbc/issues/2905) I'm wondering if that changes the supportability of this from the Ignition side? I dont know enough of the Ignition guts to figure out how to tie all the pieces together - I gave it a whirl and it still looks like the driver is returning the microsoft type by default. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [August 8, 2026, 7:38pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/11 "2026-08-08T19:38:09Z")
I don't think this changes anything - support on the Ignition side has always hinged on adding an explicit mapping from the MSSQL vendor-specific type to e.g. `java.util.Date` and then implementing the conversion from `microsoft.sql.DateTimeOffset` to `Date` where appropriate. ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [August 10, 2026, 2:01pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/12 "2026-08-10T14:01:21Z")
Thanks for the answer. I admit I'm still very confused. I understand the mapping would have to be added but there's implemented conversions to standard Java types. Eg, in a gateway scope I can do something like: ```python from com.inductiveautomation.ignition.common.util import LoggerEx from com.inductiveautomation.ignition.gateway import IgnitionGateway from java.time import OffsetDateTime gateway = IgnitionGateway.get() connection = gateway.getDatasourceManager().getDatasource("MSSQL").getConnection() builder = LoggerEx.newBuilder() logger = builder.build('sql.test') statement = connection.prepareStatement("select sysdatetimeoffset()") rs = statement.executeQuery() rs.next() logger.info(str(rs.getObject(1, OffsetDateTime))) connection.close() ``` or even ```python logger.info(str(rs.getTimestamp(1))) ``` and I would be presumably able to utilize the data from there. I wouldn't exactly want to implement my own dataset handling for a variety of reasons but...what more would the driver need to do to be supportable from Ignition? Always return an OffsetDateTime? ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [August 10, 2026, 2:27pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/13 "2026-08-10T14:27:51Z")
It doesn't need anything else. Somebody just needs to do the work in Ignition. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [August 10, 2026, 4:16pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/14 "2026-08-10T16:16:59Z")
> [@nicole.c](#): > > `from java.time import OffsetDateTime` Approximately none of Ignition's codebase uses the `java.time` API, we're basically stuck with `java.util.Date`, so even if we started handing you `OffsetDateTimes` automatically interoperability with everything else in Ignition is going to be painful. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [August 10, 2026, 4:26pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/15 "2026-08-10T16:26:48Z")
Presumably we would just map/convert it to `java.util.Date` if we added support though, rather than preserving it as `OffsetDateTime`. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 10, 2026, 4:33pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/16 "2026-08-10T16:33:14Z")
When you get there, consider mapping to `java.sql.Timestamp`, to retain sub-millisecond precision where present. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [August 10, 2026, 4:46pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/17 "2026-08-10T16:46:10Z")
Yeah, `java.sql.Timestamp` is what we're doing with some of the Oracle vendor-specific types. ---
**Author:** ![nicole.c](https://avatars.discourse-cdn.com/v4/letter/n/43a26b/32.png) [@nicole.c](https://forum.inductiveautomation.com/u/nicole.c)\ **Post date:** [August 10, 2026, 5:03pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/18 "2026-08-10T17:03:23Z")
Yeah, I wasn't sure how well Ignition supported TZ-aware times in general, but however Ignition handles other non MSSQL database TZ-aware times would be suitable. If that's a Timestamp, that's probably fine. In any case it's at least better than blowing up. ๐Ÿ™‚ Is this something I should put in a feature request and hope it might make its way in someday? (maybe it's already in there) ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [August 10, 2026, 5:05pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/19 "2026-08-10T17:05:10Z")
I just created a ticket for this. Feature requests or support tickets are the "official" way to make that happen if you don't want to rely on the whims of a random employee seeing your forum post and making a ticket from it. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 10, 2026, 5:10pm UTC](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729/20 "2026-08-10T17:10:39Z")
> [@nicole.c](#): > > well Ignition supported TZ-aware times in general Perfectly, in the case of PostgreSQL. [Next page](https://forum.inductiveautomation.com/t/jdbc-and-mssql-datetimeoffset/107729.md?page=2)