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?

Let's say your analysis date is Sunday 8th November 2015 7pm UTC and your computer's local time zone is CET (UTC+1 in winter time), i.e. it's 8pm. What do you want to store in your `TIMESTAMP` in the DB? 7pm or 8pm? By the look of your code you want 8pm - but then unless the machine retrieving the data is in the same time zone (CET) you won't get the same instant back... Maybe you should give a little more background... (ps: it is often a bad idea to use a timestamp without time zone, unless you always use the same TZ for that column, typically UTC). — assylias, Nov 08 '15 at 19:05
This may also be of interest: http://stackoverflow.com/a/14070771/829571 — assylias, Nov 08 '15 at 19:08
@assylias from my code it appears, on the contrary and given your code, that the result will be 7pm -- and that is exactly what is asked for. And yes, I know this is a bad idea, but this is what I've been asked for... Were it only for me I'd have stored a `TIMESTAMP WITH TIME ZONE` and not a pure `TIMESTAMP`. — fge, Nov 08 '15 at 19:14

score 4 · Accepted Answer · answered Nov 09 '15 at 09:42

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.

See http://stackoverflow.com/a/26129713/14731 for a complementary answer. — Gili, May 08 '17 at 15:30

score 3 · Answer 2 · answered Oct 24 '17 at 05:10

3

If performance matters, I would use the following:

final long timeAtLocal = module.getAnalysisDate(); // or System.currentTimeMillis(); or new Date().getTime(); etc. 
final long offset = TimeZone.getDefault().getOffset(timeAtLocal);
final Timestamp timeAtUTC = new Timestamp(timeAtLocal - offset);

answered Oct 24 '17 at 05:10

Lopotun

611
7
15

1

This is the only thing that worked for me. I don't know who decided to store date/timestamp data (specifically the conversion of `date.getTime()` into a timestamp object that then somehow loses it's value from the epoch even though you literally just gave it a long from the epoch, but I hope they found a new career. – David Kamer Jul 31 '20 at 05:57

zapl · Answer 3 · 2015-11-08T14:46:18.777

2

Timestamp.from(instant) is all you should need.

Neither java.sql.Timestamp nor java.time.Instant have a timezone so you don't need to convert to UTC.

Alternatively directly from java.util.Date

long millisSinceEpoch = module.getAnalysisDate().getTime();
Timestamp timestamp = new Timestamp(time);

edited Nov 08 '15 at 14:46

answered Nov 08 '15 at 14:33

zapl

63,179
10
123
154

4

Well, if I try and print `new TimeStamp(new Date().getTime()), it returns the timestamp in the current time zone, so... – fge Nov 08 '15 at 15:01
3

@fge that's because `TimeStamp#toString()` uses your local timezone: http://stackoverflow.com/questions/14070572/is-java-sql-timestamp-timezone-specific - what your version does while converting to/from `LocalDateTime` is to set the time so it prints the utc time while actually being formatted as local time. `LocalDateTime dt` is assumed to be given in local time while you actually constuct it to be as UTC - here you "break" it. – zapl Nov 08 '15 at 15:19
Well, yes, but then my timezone is precisely _not_ always UTC and I need the UTC value! I know about the local timezone, and I do need a representation at UTC... Also, the type stored in the database is a `TIMESTAMP`, not a `TIMESTAMP WITH TIME ZONE` (I have edited my question to clarify this fact) – fge Nov 08 '15 at 15:22
1

@fge I'm not very familar with SQL servers & config but like in http://stackoverflow.com/questions/15206194/jdbc-mysql-save-timestamp-always-using-utc I'd say it's not the `Timestamp` you have to fix, it's how your database / jdbc driver / mapper stores the values. – zapl Nov 08 '15 at 15:34
That is irrelevant here. Again: _whatever the timezone of the user's JVM_, I need to be able to obtain a `Timestamp` at UTC. – fge Nov 08 '15 at 18:54
@fge You already have. The `Timestamp` instance itself is a zoneless absolute point in time. It's just `toString` that defaults to a `DateFormat` using local time. The clean solution would be to adjust the formatter. http://ideone.com/R5njAo - what you're doing otherwise is to create a timestamp that merely prints correctly but could cause trouble if used for other computation because it represents a different point in time. – zapl Nov 08 '15 at 20:33
1

*sigh* I'm lost here. My goal here is only to create a `Timestamp` which "fakes" a UTC timezone. Note that I won't be reusing this instance anywhere else, it is just for insertion into the database. – fge Nov 08 '15 at 21:24
@fge http://ideone.com/evRCQj is the only "concern" I have with what you got. I haven't seen much documentation about which zone offset the converions use: the current one (which is bad when you're formatting a timestamp in DST time and that's no longer the case) or dynamically the time at that point. `ZoneId.systemDefault().getRules().getOffset(instant)` can make that explicit though and should be as good as it gets. – zapl Nov 08 '15 at 21:52
I think I see your point... Maybe a `ZonedDateTime` can help here but I can't really wrap my head around it :/ – fge Nov 08 '15 at 22:22
@fge https://docs.oracle.com/javase/8/docs/api/java/time/ZonedDateTime.html puts it nicely *"Any method that converts directly or implicitly from a local date-time to an instant by obtaining the offset has the potential to be complicated."* :) And isn't MySQL also an available backend? *"Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval."* https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html - there you wouldn't want to mess with `Timestamp`. – zapl Nov 08 '15 at 22:55
For some reason it won't store the Timestamp object in mysql as a timestamp, but it instead sends something else? A string maybe. So if you Timestamp is at the correct number from `getTime()` you pass it to the timestamp object only for it to turn it into the date and time as a string without the millisecond epoch value. This means it's put into a database as the datetime string of whatever timezone it was submitted from. It literally takes in an input with the exact right value and spits out a mutated value missing data. Great design. – David Kamer Jul 31 '20 at 06:06

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

3 Answers3