java.sql.Time to OffsetTime

Question

Hi in DB i have four columns to store a time window. This would allow user to store 9:00 to 5:00 EST.

Now i need to parse this information in java.

java.sql.Time startTS = rs.getTime("begin_TIME ");
LocalTime localTime = startTS.toLocalTime();

offset could be made with: OffsetTime of(LocalTime time, ZoneOffset offset)

from what i see we cant convert zoneid to zoneoffset, so how do i 9:00 est(stored in time and zone columns) from sqlserver to java.

DB Table:
 begin_TIME time NOT NULL,
 begin_TIME_ZONE varchar(5) NOT NULL,
 end_TIME time NOT NULL,
 end_TIME_ZONE varchar(5) NOT NULL,

In the back-end I need to check that the request is in the window, request time is converted to ZonedDateTime and start and end need to come from DB:

public boolean compare(ZonedDateTime dateTime, OffsetTime startTime, OffsetTime endTime) {
    OffsetTime offsetTime = dateTime.toOffsetDateTime().toOffsetTime();
    int start = offsetTime.compareTo(startTime);
    int end = offsetTime.compareTo(endTime);
    return start >= 0 && end <= 0;
}

When you say EST, you mean Eastern Standard Time? Would daylight savings time be marked by `begin_TIME_ZONE` or `end_TIME_ZONE` being `EDT` instead of `EST`? — Ole V.V., Mar 15 '17 at 18:27
no its not expected that user will go and change time zones for daylight savings. user needs to define 9-5 Toronto time(EST) or 9-5 pacfic time. in the back end, java need to adjust for daylight savings. Database schema is open to change if that helps. — tsingh, Mar 15 '17 at 19:21
Three-letter time zone IDs are going out of use. It may not make the big difference for your question, but I might consider using for example `America/Toronto`. Something that feeds directly into `ZoneId.of()`. Well, this was probably an aside. — Ole V.V., Mar 15 '17 at 19:34
How is the backend going to be able to adjust for daylight savings when there is no date attached? How can it determine whether daylight savings is in effect? — Ole V.V., Mar 15 '17 at 19:36
sure, but even if we have zoneId from America/Toronto, we don't have ZoneOffset — tsingh, Mar 15 '17 at 19:38
In the back end i need to check request is in window, request time is converted to ZonedDateTime and start and end need to come from DB `public boolean compare(ZonedDateTime dateTime, OffsetTime startTime, OffsetTime endTime) { OffsetTime offsetTime = dateTime.toOffsetDateTime().toOffsetTime(); int start = offsetTime.compareTo(startTime); int end = offsetTime.compareTo(endTime); return start >= 0 && end <= 0; }` — tsingh, Mar 15 '17 at 19:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/138151/discussion-between-ole-v-v-and-tsingh). — Ole V.V., Mar 15 '17 at 19:48
Can your date-times come from all over the world? When in Sydney, Australia (AEDT) it’s Thu, 16 Mar 2017 08:15, it is Wed, 15 Mar 2017 14:15 in San Fransicco, California (PDT). So this would be will inside a 9 to 5 window, but because the dates are not the same, my code in my answer below will fail to recognize that. Is it a requirement to take this situation into account? — Ole V.V., Mar 16 '17 at 10:43

score 0 · Answer 1 · edited May 23 '17 at 12:25

If I understand your situation right, this variant of your compare method should be able to help you:

public boolean compare(ZonedDateTime dateTime, LocalTime start, ZoneId startTz,
        LocalTime end, ZoneId endTz) {
    ZonedDateTime startZonedDateTime = start.atDate(dateTime.toLocalDate()).atZone(startTz);
    ZonedDateTime endZonedDateTime = end.atDate(dateTime.toLocalDate()).atZone(endTz);
    return (! dateTime.isBefore(startZonedDateTime)) && (! dateTime.isAfter(endZonedDateTime));
}

I am using the date part of the supplied ZonedDateTime to determine whether the comparison is to be made on a date where daylight saving time is in effect. I chose so because Java needs a date to be able to convert a local time to a zoned or offset time when the time zone may have daylight savings time.

It is possible to compare OffsetTime (ignoring dates) objects as you do in your version, but I found it somewhat simpler to compare ZonedDateTime objects instead. What it really does is compare the corresponding instants, so it works well even though the time zones differ.

I trust you to supply the two zone ID objects to the method. If you end up storing time zones as America/Toronto in your database, this can be fed directly into ZoneId.of().

I really don’t think you should stick to EST and PST. It’s getting too complicated and errorprone. Java can translate EST to -05:00; this leaves you with no adjustment for daylight savings time (it does understand PST as America/Los_Angeles though). Use the modern forms America/Toronto etc. See the answers to this question: How to tackle daylight savings using Timezone in java.

java.sql.Time to OffsetTime

1 Answers1