How do you save a UTC Timestamp?

Question

How do you convert a ZonedDateTime to an SQL Timestamp and retain the Timezone info? I am trying to take a UTC zoned time, convert it to a Timestamp, and then convert it back, but when I convert to Timestamp, it loses the time zone info and the Timestamp is created using my local time zone:

public static void main(String[] args) {

    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("hh:mm a");

    ZonedDateTime zdtUTC = ZonedDateTime.now(ZoneId.of("UTC"));

    ZonedDateTime zdtNY = zdtUTC.withZoneSameInstant(ZoneId.of("America/New_York"));
    ZonedDateTime zdtAZ = zdtUTC.withZoneSameInstant(ZoneId.of("America/Phoenix"));
    ZonedDateTime zdtUK = zdtUTC.withZoneSameInstant(ZoneId.of("Europe/London"));

    System.out.println(formatter.format(zdtUTC) + " in UTC zone");
    System.out.println(formatter.format(zdtNY) + " in New York, NY");
    System.out.println(formatter.format(zdtAZ) + " in Phoenix, AZ");
    System.out.println(formatter.format(zdtUK) + " in London, UK");

    Timestamp timestamp = Timestamp.from(zdtUTC.toInstant());

    LocalDateTime converted = timestamp.toLocalDateTime();
    ZonedDateTime convertedZdt = ZonedDateTime.of(converted, ZoneId.of("UTC"));

    System.out.println(timestamp);

    System.out.println(formatter.format(convertedZdt) + " in UTC zone");

}

06:33 PM in UTC zone
02:33 PM in New York, NY
11:33 AM in Phoenix, AZ
07:33 PM in London, UK
2017-05-07 14:33:06.745
02:33 PM in UTC zone

What do I need to do to ensure that the Timestamp records using the correct time zone info?

you can used '& server Timezone=UTC&use Legacy Date time Code=false' with success — Fady Saad, May 07 '17 at 18:45
A SQL Timestamp doesn't *have* a time zone - it's just an instant in time. If you need a time zone separately, I'd suggest storing the time zone ID in a separate column. If you only care about the offset at that point in time, you could store that - but you need to be aware that that's *not* the same as storing the time zone. — Jon Skeet, May 07 '17 at 18:46
I understand it's not the same as storing a time zone, but I need to be able to store the absolute time at what ever hour it is at that instant in the UTC zone. I'm not allowed to alter the database table. So If I'm in New York and it's 3PM New York time, it needs to store as 11AM, Likewise if I'm in London and it's 4PM, it needs to store as 3PM. Otherwise I have no point of reference when retrieving the `Timestamp` from the database. — Skywarp, May 07 '17 at 19:36
Right, so you *don't* need to store the offset or the time zone - you just need to store the instant in time, by the sounds of it. It's not clear why you're converting anything to a `LocalDateTime` in your sample code... — Jon Skeet, May 08 '17 at 05:52

score 2 · Answer 1 · answered May 07 '17 at 20:41

when I convert to Timestamp, it loses the time zone info and the Timestamp is created using my local time zone

No. When you convert the original ZonedDateTime zdtUTC to a java.sql.Timestamp you get the same moment in time. This can be verified by formatting and displaying the timestamp value directly:

Timestamp timestamp = Timestamp.from(zdtUTC.toInstant());  // as before
// verify the timestamp value directly
java.text.SimpleDateFormat sdfUTC = new java.text.SimpleDateFormat("hh:mm a z");
sdfUTC.setCalendar(java.util.Calendar.getInstance(java.util.TimeZone.getTimeZone("UTC")));
System.out.printf("timestamp is %s%n", sdfUTC.format(timestamp));

That will print the same value as the first line of your output:

08:30 PM in UTC zone
...
timestamp is 08:30 PM UTC

It's the subsequent conversion to LocalDateTime and then back to ZonedDateTime that "loses" the timezone information.

How do you save a UTC Timestamp?

1 Answers1