Convert UTC java.sql.Time to java.time.localtime with correct DST

Question

I have a problem to convert a java.sql.Time (UTC) which is fetched from a database to a java.time.LocalTime (GMT+1 DST). It is always missing the DST hour. So like a Time of 03:00 is only converted to a LocalTime of 04:00 instead of 05:00.

//Saved UTC time in DB: 03:00
LocalTime.ofInstant(Instant.ofEpochMilli(sqlTime.getTime()), ZoneId.of("Europe/Berlin"));
=> 04:00 //expected 05:00

I guess the problem is that java.sql.Time saves the time with a default date of 1970-01-01 and in 1970 there was no DST in Germany. But of course the time should be shown for today and not for 1970.

So how can I get the correct time for this example?

What value does the method sqlTime.getTime() return? And what value is expected in the LocalTime ? — Ilya, Oct 24 '18 at 10:33
Why pick an old-fashioned `java.sql.Time` out of your database? Can’t you get a `LocalTime`? It will still be in UTC, of course, so you will still need a conversion. — Ole V.V., Oct 24 '18 at 10:36
I have to use a framework which unfortunately forces java.sql.Time. @Ilya Please look at the code example for the expected and real values. As for sqlTime.getTime() it returns: 10800000 — Holger Rattenscheid, Oct 24 '18 at 14:38

Ole V.V. · Answer 1 · 2018-10-24T13:06:42.260

Assuming that you are using at least JDBC 4.2, you should be able to retrieve a LocalTime from your result set:

    LocalTime timeInUtc = yourResultSet.getObject(yourTimeColumn, LocalTime.class);

Then there’s no need bother with the outdated and poorly designed java.sql.Time class. The time you get will still be in UTC, of course. Here’s how to convert:

    LocalTime timeInUtc = LocalTime.of(3, 0);

    ZoneId zone = ZoneId.of("Europe/Berlin");
    LocalTime timeInGermany = OffsetDateTime.now(ZoneOffset.UTC)
            .with(timeInUtc)
            .atZoneSameInstant(zone)
            .toLocalTime();

    System.out.println("Zeit heute in Deutschland: " + timeInGermany);

When I ran the code today I got the output you expected:

Zeit heute in Deutschland: 05:00

Edit: If there’s no way you can avoid getting a java.sql.Time, convert it to LocalTime first. Assuming that the Time is in UTC and we don’t want to rely on a fragile JVM time zone setting for conversion, you are correct that we need the getTime method:

    Time sqlTimeInUtc = // Get from database
    LocalTime timeInUtc 
            = LocalTime.MIDNIGHT.plus(sqlTimeInUtc.getTime(), ChronoUnit.MILLIS);

If you could rely on the JVM time zone setting also being UTC, the following would be nicer:

    LocalTime timeInUtc = sqlTimeInUtc.toLocalTime();

In both cases the rest is as above.

In all cases there are some corner cases around the question whether you want “today in UTC” or “today in Europe/Berlin time zone” when you say “the time should be shown for today”. There’s also a corner case if the time is between 2 and 3 AM and today is the last Sunday in March, where the clocks are turned forward from 2 to 3 to initiate summer time (DST) in Germany. Please think these corner cases through and decide what you want.

By the way your diagnosis is completely correct: Time.getTime returns the time of day on Jan 1, 1970, so when you feed this into an Instant, you are converting the time of day on this date, that is, without summer time.

Use of `LocalTime` directly requires a driver that actually implements it, and `java.sql.Time.toLocalTime()` will use the default JVM timezone for conversion. — Mark Rotteveel, Oct 24 '18 at 11:12
Argh, @MarkRotteveel, you are correct. This means that if I can’t control the JVM time zone setting — and I can’t, someone else may change it under my feet — then the conversion isn’t reliable. — Ole V.V., Oct 24 '18 at 11:29
It is even worse with the inconsistent way `java.sql.Time` and `java.sql.Timestamp` retrieval is implemented between drivers :| — Mark Rotteveel, Oct 24 '18 at 11:30
I have edited after your comments, @MarkRotteveel (not addressing the retrieval problem, I agree it’s there). — Ole V.V., Oct 24 '18 at 13:01
Yap I am forced to use java.sql.Time. But your workaround does the trick, thanks. — Holger Rattenscheid, Oct 24 '18 at 15:08

score 1 · Answer 2 · answered Oct 24 '18 at 11:00

As far as I understand it your question is: Given a time in UTC convert it to local time according to the current time offset. This time offset is different depending of whether DST is in effect or not.

A possible approach is to determine the current offset using TimeZone:

    TimeZone tz = TimeZone.getTimeZone("Europe/Berlin");
    int timeZoneOffsetMillis = tz.getOffset(new Date().getTime());

Now timeZoneOffsetMillis contains the number of milliseconds you have to add to your UTC time to get local time.

You can get a LocalTime like this:

    LocalTime localTime = LocalTime.ofNanoOfDay((sqlTime.getTime() + timeZoneOffsetMillis) * 1000000L);

If your time is only accurate to seconds instead of nanoseconds anyway you might want to use LocalTime.ofSecondOfDay.

Convert UTC java.sql.Time to java.time.localtime with correct DST

2 Answers2

Linked