-1

I am working on a solution which will need to compare date and time which are stored in a database with the current time in Java. Stored date and time is in UTC and it is a java.sql.Timestamp. I use java.time.Instant to compare but I need to convert the stored Timestamp to an Instant. I used Timestamp.toInstant() but it is returning the wrong time. Please refer the example below for the code.

Timestamp timeStamp = 2023-08-08 07:52:09.877 // This is in UTC.

timeStamp.toInstant = 2023-08-08T02:22:09.877Z // Converted UTC value.

Tried to find a method that can pass the timezone of the time that we input to the toInstant method but unable to find any.

Anyone aware of a solution to get the same UTC time in to the Instant object?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Shan Chathusanda Jayathilaka
  • 416
  • 3
  • 13
  • Compare `timeStamp.getTime()` and `timeStamp.toInstant().toEpochMilli()`. Are they the same number? If they are, then the result is correct and you are probably formatting the `java.sql.Timestamp` incorrectly. – Sweeper Aug 07 '23 at 08:14
  • ```timeStamp.getTime()``` and ```timeStamp.toInstant().toEpochMilli()``` same. But I am not formatting the ```java.sql.Timestamp``` in any point after getting that from the database. – Shan Chathusanda Jayathilaka Aug 07 '23 at 08:28
  • You are, otherwise you wouldn't have written `2023-08-08 07:52:09.877`. That string is the result of formatting. Try formatting the `Timestamp` with a [`DateFormat`](https://docs.oracle.com/en/java/javase/17/docs/api/java.base/java/text/DateFormat.html) that has its timezone set to UTC. – Sweeper Aug 07 '23 at 08:31
  • In which timezone are you located? – Just another Java programmer Aug 07 '23 at 08:31
  • *Stored date and time is in UTC* Are you *certain* of that? What RDBMS? – g00se Aug 07 '23 at 08:32
  • 1
    *Timestamp timeStamp = 2023-08-08 07:52:09.877 // This is in UTC.* E.g in MySql, not any longer it isn't. It was *stored* in UTC but was converted to your timezone on retrieval – g00se Aug 07 '23 at 08:38
  • I m using MSSQL as my DB and I am storing the time as UTC with the following prepared statement code. ```prepStat.setTimestamp(TimeStamp, Calendar.getInstance(TimeZone.getTimeZone(UTC));``` – Shan Chathusanda Jayathilaka Aug 07 '23 at 10:21
  • 1
    You should probably be trying to do something more like call `setObject` with type `java.time.Instant` but I'm not sure what MSSQL does with its datetimes – g00se Aug 07 '23 at 10:36
  • Post your additional details as edits to the Question, not as Comments. – Basil Bourque Aug 07 '23 at 15:30
  • 1
    What is the *exact* data type of the column in your table? – Basil Bourque Aug 07 '23 at 15:31
  • 1
    Keep away from the ole `java.sql.Timestamp` `Calendar` and `TimeZone` classes. They were troublesome and poorly designed and typically gave you issues such as what you are experiencing (and/or other issues too). Best to use `timestamp with time zone` if MS SQL supports it (which I think) and `OffsetDateTime` in Java. – Ole V.V. Aug 07 '23 at 15:43
  • Assuming you had a `timestamp` without time zone in your database, what happened was that something in your system assumed it was in your local time zone and falsely converted it to a `java.sql.Timestamp` using this assumption. If you cannot change the database data type (which I hope you can), retrieve a `LocalDateTime` from the database and perform your own conversion to UTC so that you control that it is done correctly. – Ole V.V. Aug 07 '23 at 15:46
  • `prepStat.setObject(n, Instant.now(), java.time.Instant.class); // (where 'n' is 1-based column index` is probably worth trying. Inverse op for select – g00se Aug 07 '23 at 16:05
  • @g00se It’s certainly on the right track. Did you try it, though? I didn’t. Asking becuase i suspect that the third argument to [`setObject`](https://docs.oracle.com/en/java/javase/18/docs/api/java.sql/java/sql/PreparedStatement.html#setObject(int,java.lang.Object,java.sql.SQLType)) should perhaps be [JDBCType.TIMESTAMP_WITH_TIMEZONE`](https://docs.oracle.com/en/java/javase/18/docs/api/java.sql/java/sql/JDBCType.html#TIMESTAMP_WITH_TIMEZONE)? – Ole V.V. Aug 07 '23 at 17:15
  • @OleV.V. unfortunately not as I don't have MSSQL. – g00se Aug 07 '23 at 17:36
  • 1
    afaics offsets are supported (`datetimeoffset` type) but not timezones – g00se Aug 07 '23 at 17:42
  • 3
    JDBC does not specify support for `Instant`, so it will not work on all JDBC drivers. JDBC defines support for `java.time.LocalDateTime` for `TIMESTAMP`, and `java.time.OffsetDateTime` for (SQL Standard) `TIMESTAMP WITH TIME ZONE`. – Mark Rotteveel Aug 08 '23 at 15:51
  • 1
    Related: [Is java.sql.Timestamp timezone specific?](https://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific) – Mark Rotteveel Aug 08 '23 at 15:53

1 Answers1

3

Timestamp#toInstant is indeed in UTC

convert the stored Timestamp to an Instant

Calling java.sql.Timestamp#toInstant will indeed give you a date and time as seen with an offset of zero hours-minutes-seconds from UTC. Both classes internally keep a count since the epoch reference of first moment of 1970 in UTC, 1970-01-01T00:00Z.

Instant instant = myJavaSqlTimestamp.toInstant() ;

Avoid legacy date-time classes

The Timestamp class is one of the terribly flawed legacy date-time classes. These were years ago supplanted by the modern java.time classes defined in JSR 310.

Never use Timestamp, Calendar, Date, SimpleDateFormat, etc. Use only java.time classes with JDBC 4.2+.

With zone/offset

If the column in your Microsoft SQL Server database is of the type datetimeoffset, then retrieve the matching type in JDBC: OffsetDateTime.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Writing:

String input = "2023-08-08 07:52:09.877Z" ;  // `Z` on the end indicates an offset of zero hours-minutes-seconds from UTC. Pronounced "Zulu". 
Instant instant = Instant.parse( input ) ;  // Represents a moment as seen with an offset of zero from UTC.
OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;  // Represents a moment as seen with a particular offset from UTC. In this case the offset is zero.
myPreparedStatement.setObject( … , odt ) ;

Without zone/offset

I suspect your problem is due to an incorrect data type on your column. You neglected to indicate the data type, so I can only guess. But I guess the type is the legacy Microsoft SQL Server type datetime or its modern replacement datetime2.

Both of those datetime/datetime2 types represent a date with time-of-day but lack the context of a time zone or an offset-from-UTC. Without that context, such a value does not represent a moment, is not a specific point on the timeline. So comparing to a value in UTC is nonsensical.

The matching JDBC type for those datetime/datetime2 types is LocalDateTime — a class representing a date with time-of-day but without zone/offset.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

Writing:

myPreparedStatement.setObject( … , ldt ) ;

Do not use those datetime/datetime2 types nor LocalDateTime if you are tracking a moment, a specific point on the timeline. That would be like storing an amount of money without noting the currency.

If you did indeed use those datetime/datetime2 types in an attempt to store a moment, then you now have a mess on your hands. You will need to use a datetimeoffset instead. To migrate data, you can assign an offset if you know for certain the intended offset of the stored values, but you will of course ultimately be guessing since you cannot know the context of every row’s insertion/update.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154