Invalid ID for ZoneOffset

Question

I am trying to convert from java.sql.timestamp to OffsetDateTime so that i can return ISO8601 standard string in my rest api. I am using this code to convert from timestamp to OffsetDateTime

public static OffsetDateTime sqlTimetampeToOffsetDateTime(Timestamp ts, String timeZone)
{
    if (ts == null)
    {
        return null;
    }

    Calendar cal = Calendar.getInstance();
    cal.setTime(ts);
    ZoneOffset offset = ZoneOffset.of(timeZone);
    return OffsetDateTime.of(
            cal.get(Calendar.YEAR),
            cal.get(Calendar.MONTH)+1,
            cal.get(Calendar.DAY_OF_MONTH),
            cal.get(Calendar.HOUR_OF_DAY),
            cal.get(Calendar.MINUTE),
            cal.get(Calendar.SECOND),
            cal.get(Calendar.MILLISECOND)*1000000,
            offset);
}

However, the code fails at ZoneOffset offset = ZoneOffset.of(timezone) for value Europe/Copenhagen.

I used following code to print list of all timezones and i do see Europe/Copenhagen in that list

    Set<String> allZones = ZoneId.getAvailableZoneIds();
    LocalDateTime dt = LocalDateTime.now();

    List<String> zoneList = new ArrayList<String>(allZones);
    Collections.sort(zoneList);

    for (String s : zoneList) {
        ZoneId zone = ZoneId.of(s);
        ZonedDateTime zdt = dt.atZone(zone);
        ZoneOffset offset = zdt.getOffset();
        int secondsOfHour = offset.getTotalSeconds() % (60 * 60);
        String out = String.format("%35s %10s%n", zone, offset);
        System.out.printf(out);
    }

Now I don't understand what is going on. How can i convert java.sql.timestamp to ISO8601 string (i don't care if i have to use OffsetDateTime or not. I would prefer not to use any third party library

http://pastebin.com/eHJKWpAv

What is your datatype in your database? timestamp with timezone? Something else? — Tunaki, Apr 29 '16 at 21:39
In database i have timestam e.g., `2016-05-23 15:00:00.0` and i have another column which has timezone e.g., `Europe/Copenhagen`. Thats why i am passing `timezone` as a separate string. — Em Ae, Apr 29 '16 at 21:41
The expression `ZoneOffset.of("Europe/Copenhagen")` must fail because that zone id is NOT just an offset (contains additional informations like daylight saving rules). In this case you have first to construct a `ZonedDateTime` and then derive from this an `OffsetDateTime` or an `Instant`. — Meno Hochschild, Apr 30 '16 at 07:56

score 9 · Answer 1 · answered Jan 18 '18 at 22:31

ZoneOffset only makes sense when dealing with a specific point in time. In Europe/London we currently use either BST or GMT depending on the time of year. However, 100 years ago (give or take), Europe/London didn't have BST. ZoneOffset.of() only retrieves zone offsets from an internal cache which is only populated when ZoneOffset.ofTotalSeconds() is called. This is poorly documented. However, an easy solution exists:

ZoneId.of("Europe/London").getRules().getOffset(Instant.now());

which returns the correct ZoneOffset for Europe/London for right now (e.g. today)

score 2 · Answer 2 · answered Apr 29 '16 at 21:55

2

If you have the ZoneId, it's pretty trivial to use the Instant class to do this:

Timestamp t = new Timestamp(System.currentTimeMillis());

ZoneId zone = ZoneId.of("Europe/Copenhagen");

OffsetDateTime offsetDateTime = ZonedDateTime
    .ofInstant(Instant.ofEpochMilli(t.getTime()), zone)
    .toOffsetDateTime();

answered Apr 29 '16 at 21:55

djmorton

616
4
6

How do you know what zoneIds you have available? Any Constants, without querying them beforehand manually? – ThomasMX Mar 23 '23 at 11:11

score -1 · Answer 3 · answered Apr 29 '16 at 21:43

I did manage to convert it but i am not sure if it is the right way to do so or not. Here is my code for someone else who is looking for the answer

public static OffsetDateTime sqlTimetampeToOffsetDateTime(Timestamp ts, String timeZone)
{
    if (ts == null)
    {
        return null;
    }

    ZoneId zoneId = ZoneId.of(timeZone);

    Calendar cal = Calendar.getInstance();
    cal.setTime(ts);
    ZonedDateTime zdt = ZonedDateTime.of(
            cal.get(Calendar.YEAR),
            cal.get(Calendar.MONTH) + 1,
            cal.get(Calendar.DAY_OF_MONTH),
            cal.get(Calendar.HOUR_OF_DAY),
            cal.get(Calendar.MINUTE),
            cal.get(Calendar.SECOND),
            cal.get(Calendar.MILLISECOND) * 1000000,
            zoneId);
    return zdt.toOffsetDateTime();
}

Where timeZone is in the form of Europe/Copenhagen ... you can see the full list that is supported by Java8 in my pastebin url posted in the question

I think `return ts.toInstant().atZone(ZoneId.of(timeZone)).toOffsetDateTime();` is all that is needed. — Tunaki, Apr 29 '16 at 21:54
your code is adding offset to the resulting time. Just wanted to write it here so that if someone else is reading he knows the difference. — Em Ae, Apr 29 '16 at 21:57
Okay, then it is `ts.toLocalDateTime().atZone(ZoneId.of(timeZone)).toOffsetDateTime();`. — Tunaki, Apr 29 '16 at 22:01

Invalid ID for ZoneOffset

3 Answers3