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

Kamakura

What is your datatype in your database? timestamp with timezone? Something else? – Pavla

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. – Kamakura

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. – Deprecatory

Answer 1

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)

Incendiarism

Answer 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();

Schick

How do you know what zoneIds you have available? Any Constants, without querying them beforehand manually? – Ursel

Answer 3

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

Kamakura

I think return ts.toInstant().atZone(ZoneId.of(timeZone)).toOffsetDateTime(); is all that is needed. – Pavla

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. – Kamakura

Okay, then it is ts.toLocalDateTime().atZone(ZoneId.of(timeZone)).toOffsetDateTime();. – Pavla