-1

Everything I've read says a Timestamp is UTC and has no offset or timezone. However, I'm 99% positive that the MS Sql Server JDBC is reading in the value from the DB and setting it in my local timezone.

Update: Please note the referenced possible duplicate question asks how to set a timestamp using UTC datetime. My question is how can I read the UTC timestamp value from a database.

The debugger shows the object held as a GregorianCalender object who's timezone is Denver.

enter image description here

David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • There *is* some time zone information hidden somewhere inside a `Date`, of which `Timestamp` is a subclass, so it goes for that class too. it doesn’t affect any observable behaviour of the `Timestamp`, so I’d say that “has” a time zone for all practical purposes is incorrect. In any case the class is poorly designed and long outdated, so you shouldn’t want to use it and hence need not care whether it has time zone or not. – Ole V.V. Mar 16 '19 at 19:17
  • Possible duplicate of [Is java.util.Date using TimeZone?](https://stackoverflow.com/questions/1516213/is-java-util-date-using-timezone) (since `Timestamp` is a subclass of `java.util.Date`) – Ole V.V. Mar 16 '19 at 19:24
  • @OleV.V. Unfortunately Timestamp is what JDBC returns. We turn that into an OffsetDateTime immediately, but we do have to handle that conversion. – David Thielen Mar 16 '19 at 19:43
  • 1
    Timeline handling is a mess, it depends on database settings, Session timezone and local timezone (and of course the datatype). It varries by driver. It does help to use the `getTimestamp(x, utccal)` variant to specify desired timezone – eckes Mar 16 '19 at 21:05
  • *Timestamp is what JDBC returns.* Is your JDBC driver that old? With JDBC 4.2 do `LocalDateTime dt = yourResultSet.getObject("your_timestamp_col", LocalDateTime.class);`. I thought about everyone was using JDBC 4.2 these days. – Ole V.V. Mar 17 '19 at 05:42
  • I think that you should find [my answer to a related question here](https://stackoverflow.com/a/55125364/5772882) helpful. – Ole V.V. Mar 17 '19 at 05:55
  • 1
    The JDBC specification **requires** that a timestamp is interpreted in the current time zone when using `java.sql.Timestamp`, and on top of that, the implementation of `java.sql.Timestamp.toString()` will **print** the value in the current time zone. – Mark Rotteveel Mar 17 '19 at 08:23
  • According to [Microsoft JDBC Driver for SQL Server Support Matrix](https://learn.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-2017) Microsoft JDBC Driver should support JDBC 4.2 since JDBC Driver version 4.2 (a coincidence in version numbers). Current version is 7.2 as far as I understand. – Ole V.V. Mar 17 '19 at 10:32
  • 1
    @OleV.V. This is a library we provide to customers. So we have to run using older JDBC drivers as some of our customers are still on them. – David Thielen Mar 17 '19 at 12:46
  • If you want to *get* the timestamp in UTC (and you can't use `getObject(..., LocalDateTime.class)`), you need to use [`ResultSet.getTimestamp(int/String, Calendar)`](https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/ResultSet.html#getTimestamp(int,java.util.Calendar)), where the `Calendar` is configured with the appropriate time zone, which I think derives implicitly from the duplicate which explains what you need to do when setting it. – Mark Rotteveel Aug 15 '22 at 13:11

1 Answers1

0

This appears to solve the problem. I'm guessing when it returns a LocalDateTime it does not use the timezone.

LocalDateTime localDT = timestamp.toLocalDateTime();
odt = localDT.atOffset(ZoneOffset.UTC);

updated with suggestion from @Ole V.V.

David Thielen
  • 28,723
  • 34
  • 119
  • 193
  • 2
    Since mssql-jdbc [v7.1.0](https://github.com/Microsoft/mssql-jdbc/releases/tag/v7.1.0) the driver has supported retrieving a LocalDateTime directly using `rs.getObject(n, java.time.LocalDateTime.class)` instead of `rs.getTimestamp(n)`. (I wrote the [PR](https://github.com/Microsoft/mssql-jdbc/pull/749) for it.) – Gord Thompson Mar 17 '19 at 12:18
  • 1
    If you need to continue using getTimestamp for legacy reasons then beware of the "lost hour" problem with time zones that observe Daylight Saving Time (a.k.a. Summer Time). For example, you can't retrieve `2019-03-10T02:00:00` in `America/Denver` because it never happened in that particular time zone. – Gord Thompson Mar 17 '19 at 14:20