select
datediff(minute, a, b) diff_minutes
from (
select
cast('2017-03-26T02:00:00' as datetime2) a, cast('2017-03-26T03:00:00' as datetime2) b
is displayed like this in IntelliJ (in fact, this was how I spotted the problem in the first place).
Note that jtds works. This is because their getObject()
returns a String and not a Timestamp.
Note that Oracles SqlDeveloper also works (for oracle). They might have implemented the workaround.
I understand that the problem here is bigger then the mssql-jdbc and that it comes down to JDBC and its use of the old legacy classes Calendar and Timestamp. However, maybe mssql-jdbc can work with date times (and such types) in an internal format so that e.g. getTimestamp()
keeps its old behavior and that getObject()
returns a LocalDateTime
?
Hi @jedvardsson, thank you for raising this issue this us. We investigated your problem as above, but it doesn't look an ideal solution to provide different values when retrieved by different functions: getTimestamp()
vs getObject()
As per JDBC specs (since 2.0), getObject()
method is expected to return value of column type TIMESTAMP in the form of java.sql.Timestamp
object, i.e. same object retrieved from getTimestamp()
method. And as you are aware java.sql.Timestamp
class object always holds its Timezone information in it, which means it will always give you the result with the DST settings.
The workaround mentioned by you is intended for advanced globalization uses only and is not recommended. As the function .toLocalDateTime()
used below is from ChronoZonedDateTime interface which returns date-time with a time-zone in an arbitrary chronology (non-real).
LocalDateTime localDateTime = r.getTimestamp(1, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).toInstant().atZone(ZoneOffset.UTC).toLocalDateTime();
From driver's perspective, we shall continue to follow JDBC Specs such that getTimestamp()
, getString()
and getObject()
methods return exactly same value when fetched from the resultset.
Let us know if you have any further queries.
Hi @cheenamalhotra ,
Thank you for taking your time and looking into this issue. I understand your point about not updating getObject()
. Too bad that JDBC is flawed. However, do you agree with my points that the DATETIME
on the SQL Server side is infact timezone-less and in a perfect world should be treated as such? I just want to make sure that I am under standing things correctly.
Secondly, I don't understand your point about toLocalDateTime()
and ChronoZonedDateTime
. Yes, the API states that the interface ChronoZonedDateTime is intended for advanced globalization issues. For example, if you need to implement your own type of chronology. The documentation recommends using ZonedDateTime which implements this interface.
And that is what I am doing: calling ZonedDateTime.toLocalDateTime()
. Follow the call chain and you'll see that this is what happens. There is nothing wrong about it. In fact is simply reversion the bug: that local datetimes are wrongly passed as zoned datetimes. What the fix does is that it converts it back to a local datetime agin.
Let me know if I understood you correctly. Thanks.
Hi @jedvardsson,
Thanks for writing back. I agree with you for the workaround, calling ZonedDateTime.toLocalDateTime()
. There is nothing wrong about it. It's just defined for advanced usage which is fine if it gets you the desired results. It is true that DATETIME and DATETIME2 datatypes in SQL Server contain timezone information and unfortunately JDBC Timestamp would always contain Daylight Savings Time and we can't remove support for that from the driver.
I am glad to know we both are on the same page and you agree we must not update getObject()
method to return timezone without DST. I am closing this issue as I believe there is no change needed from the driver and workaround mentioned by you would solve your query.