How can I take into account the TimeZone of java.sql.Date when I read it from database?

Question

My application reads java.sql.Date from database witch contains these dates in America/New_York time zone (-4 UTC). After a fetching of data Hibernate creates objects java.sql.Date and represents them in my local time zone. So, I need to convert date from database in UTC directly. How can I do that?

I need something like this

Instant.ofEpochMilli(((java.sql.Date) value).getTime()).atOffset(offset);

But offset doesn't do what I want. For example:

time in database: 01-02-2020 22:00 (in America/New_York -> it's UTC-4 and I need to add extra 4 hours)

time in my application: 01-02-2020 22:00 +4 (because my time zone is UTC+4). When I set ZoneOffset.UTC

Instant.ofEpochMilli(((java.sql.Date) value).getTime()).atOffset(ZoneOffset.UTC)

it removes 4 hours ans toString() result = 01-02-2020T16:00Z

How can I add 4 hour to date (java.sql.Date) in database so that it would be 02-02-2020 02:00 UTC ?

Answer 1

For a point in time with a time zone such as 2020-02-01T22:00-04:00[America/New_York], do not use java.sql.Date. For two reasons:

1. java.sql.Date is a poorly designed class, a true hack, indeed, on top if the already poorly designed java.util.Date class. Fortunately both Date classes are also long outdated.
2. java.sql.Date was designed for a date without time of day.

Instead:

• In your SQL database use timestamp with time zone and store times consistently in UTC. So the time stored in your database should be 2020-02-02T02:00Z (Z for UTC).
• In Java retrieve your time into an OffsetDateTime (since JDBC 4.2 we can do that, bypassing java.sql.Date and java.sql.Timestamp completely). Then if needed convert to a ZonedDateTime in your time zone. Use a proper time zone ID in the region/city format (not just what you think the UTC offset is).

For a demonstration:

    ZoneId zone = ZoneId.of("Asia/Tbilisi");

    OffsetDateTime dateTimeFromDatabase
            = OffsetDateTime.of(2020, 2, 2, 2, 0, 0, 0, ZoneOffset.UTC);
    ZonedDateTime dateTimeInYourTimeZone
            = dateTimeFromDatabase.atZoneSameInstant(zone);
    System.out.println(dateTimeInYourTimeZone);

Output:

2020-02-02T06:00+04:00[Asia/Tbilisi]

Edit 1: You said:

I understand that this is bad to use outdated java.sql.Date, but I have no choice. "java.sql.Date was designed for a date without time of day." - but I thought I can anyway get time of day by calling (java.sql.Date) value).getTime() (because it returns timestamp)

From the documentation:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

So it seems to me that you’re breaking the contract. What the consequences are, I don’t know. They probably depend on your JDBC driver. That is, behaviour might change with the next version of that JDBC driver.

Edit 2: I took a closer look at your data. I agree with you that they are wrong; but the problem is not in the code you have presented, it’s in the java.sql.Date object that you seem to have received somehow.

For my investigation I did:

    // time in database: 01-02-2020 22:00
    // (in America/New_York -> it's UTC-4 and I need to add extra 4 hours)
    ZonedDateTime dateTimeInDatebase = ZonedDateTime
            .of(2020, 2, 1, 22, 0, 0, 0, ZoneId.of("America/New_York"));
    System.out.println("In database:     " + dateTimeInDatebase);
    long correctEpochMillis = dateTimeInDatebase.toInstant().toEpochMilli();
    System.out.println("Correct millis:  " + correctEpochMillis);

    // toString() result = 01-02-2020T16:00Z
    OffsetDateTime observedDateTime
            = OffsetDateTime.of(2020, 2, 1, 16, 0, 0, 0, ZoneOffset.UTC);
    long observedEpochMilli = observedDateTime.toInstant().toEpochMilli();
    System.out.println("Observed millis: " + observedEpochMilli);

    Duration error = Duration.between(dateTimeInDatebase, observedDateTime);
    System.out.println("Error:           " + error);

The output is:

In database:     2020-02-01T22:00-05:00[America/New_York]
Correct millis:  1580612400000
Observed millis: 1580572800000
Error:           PT-11H

Observations:

1. The UTC offset in New York in February is not -04:00 but -05:00 (-04:00 is the correct offset during summer time/DST).
2. The millisecond value that you have retrieved from your java.sql.Date does not denote the point in time that it should. There is nothing in your code that changes the point in time. So you are not only getting an incorrect type, you are also getting an incorrect value.
3. Read the error printed in the last output line as a period of time of minus 11 hours. The millisecond value in your java.sql.Date is 11 hours too early.

You have yourself explained some of the discrepancy with the time zone difference, and I believe that this is true. We have not yet verified that this is the whole story. So I also cannot tell you what the solution is. Other than filing a ticket to the provider of your incorrect type and value so you get correct data instead. A possible hack is to add 11 hours, of course, but whether you then should add only 10 hours in the summer time part of the year — I am not the correct person to ask.

Edit 3:

I just came up with an idea to fix twice value of timestamp. Like the first time - add offset of local zone (fix the influence of jdbc driver), and the second - handle offset of dates stored in database.

We can do that if we want:

    Instant observedResult = Instant.parse("2020-02-01T16:00:00Z");
    Object receivedValue = new java.sql.Date(observedResult.toEpochMilli());

    long receivedEpochMillis = ((java.sql.Date) receivedValue).getTime();
    ZonedDateTime adjustedDateTime = Instant.ofEpochMilli(receivedEpochMillis)
            .atZone(ZoneId.systemDefault())
            .withZoneSameLocal(ZoneId.of("America/New_York"));

    System.out.println(adjustedDateTime);

Output when run in Asia/Tbilisi time zone (so this is what ZoneId.systemDefault() returned; it’s at offset +04:00 all year):

2020-02-01T20:00-05:00[America/New_York]

It brings us closer to what you say was in the database, but it’s still a couple of hours too early. I am sorry.

Links

• My answer to a related question: Getting the date from a ResultSet for use with java.time classes
• Documentation of java.sql.Date