Is this the correct way to obtain a java.sql.Timestamp at UTC from a Date?

Question

I develop a SonarQube plugin and for one of my needs I need to store the analysis date of a project as an SQL TIMESTAMP (Please note: a TIMESTAMP, and not a TIMESTAMP WITH TIMEZONE).

Here is how I currently do it:

// In the SonarQube Sensor
// .getAnalysisDate() returns a java.util.Date
final Instant instant = module.getAnalysisDate().toInstant();

// Timestamp at UTC from the Instant
final LocalDateTime dt = LocalDateTime.frominstant(instant, ZoneOffset.UTC);
final Timestampt ts = Timestamp.valueOf(dt);

I have a little trouble grasping the concept of an Instant, there is also ZonedDateTime etc...

Anyway, this seems to do what I want, but is it the correct way?

Answer 1

To store a UTC TIMESTAMP in your DB, you need to create a Java Timestamp that represents the date of your report (say 8th November 7pm UTC), but in the local time zone without conversion (say 8th November 7pm CET). So your approach is correct: get the LocalDateTime of the analysis date in UTC (8th November 7pm) and create a Timestamp in your local time zone at that LocalDateTime.

I don't think there is a shorter/better way to do it. If you used a sql TIMESTAMP WITH TIME ZONE field you would not have to do any manipulations and Date.from(Instant) would produce the correct result.

---

Clarification of the concepts involved, using the time at which you posted your question as an example (Sunday 8th November 2015 at 7pm UTC) and assuming your local time zone is CET (Central European Time = UTC+1):

• the Java Timestamp will be the number of milliseconds since the epoch, i.e. it represents the unique instant on the time line at which you posted your question and does not have any time zone information
• when storing that Timestamp into a TIMESTAMP (i.e. without time zone) field, the jdbc driver will calculate the date/time corresponding to your Timestamp in the default time zone (unless a Calendar is explicitly provided) - so your DB will show Sunday 8th November at 8pm
• a java.time.Instant is similar to a Java Timestamp: it represents a unique point in time, without time zone information
• a LocalDateTime is like a sql TIMESTAMP, it says, for example, Sunday 8th November 8pm, but you don't know what point in time that is without additional time zone information
• a ZonedDateTime is essentially a LocalDateTime + a time zone. For example Sunday 8th November 8pm [Europe/Paris] - that generally identifies a unique instant but not necessarily (think of when clocks change backward for DST and the same hour is repeated twice).
• an OffsetDateTime is essentially a LocalDateTime + an offset vs. UTC. For example Sunday 8th November 8pm +01:00. That identifies a unique instant in time.

The standard approach is generally to store an instant as a sql TIMESTAMP WITH TIME ZONE and use either a Timestamp or an OffsetDateTime on the Java side of things.