Postgres has no such data type as TIMESTAMP. Postgres has two types for date plus time-of-day: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE. These types have very different behavior with regard to time zone information.
- The
WITH type uses any offset or time zone information to adjust the date-time to UTC, then disposes of that offset or time zone; Postgres never saves the offset/zone info.
- This type represents a moment, a specific point on the timeline.
- The
WITHOUT type ignores any offset or zone info that may be present.
- This type does not represent a moment. It represents a vague idea of potential moments along a range of about 26-27 hours (the range of time zones around the globe).
You virtually always want the WITH type, as explained here by expert David E. Wheeler. The WITHOUT only makes sense when you have the vague idea of a date-time rather than a fixed point on the timeline. For example, "Christmas this year starts at 2016-12-25T00:00:00" would be stored in the WITHOUT as it applies to any time zone, not yet having been applied to any one single time zone to get an actual moment on the timeline. If Santa’s elves were tracking the start time for Eugene Oregon US, then they would use the WITH type and an input that included an offset or time zone such as 2016-12-25T00:00:00-08:00 which gets saved into Postgres as 2016-12-25T08:00.00Z (where the Z means Zulu or UTC).
The equivalent of Postgres’ TIMESTAMP WITHOUT TIME ZONE in java.time is java.time.LocalDateTime. As your intention was to work in UTC (a good thing), you should not be using LocalDateTime (a bad thing). That may be the main point of confusion and trouble for you. You keep thinking about using LocalDateTime or ZonedDateTime but you should be using neither; instead you should be using Instant (discussed below).
I also wonder if because I am going from LocalDateTime to ZonedDateTime and vice versa, I might be losing any timezone info.
Indeed you are. The entire point to LocalDateTime is to lose time zone info. So we rarely use this class in most apps. Again, the Christmas example. Or another example, "Company policy: All our factories around the world take lunch at 12:30 PM". That would be LocalTime, and for a particular date, LocalDateTime. But that has no real meaning, not an actual point on the timeline, until you apply a time zone to get a ZonedDateTime. That lunch break will be at different points on the timeline in the Delhi factory than the Düsseldorf factory and different again at the Detroit factory.
The word "Local" in LocalDateTime may be counter-intuitive as it means no particular locality. When you read “Local” in a class name, think “Not a moment… not on the timeline… just a fuzzy idea about a kinda-sorta date-time”.
Your servers should almost always be set to UTC in their operating system time zone. But your programming should never depend on this externality as it is all too easy for a sysadmin to change it or for any other Java app to change the current default time zone within the JVM. So always specify your desired/expected time zone. (Same goes for Locale, by the way.)
Upshot:
- You are working way too hard.
- Programmers/sysadmins must learn to "Think global, Present local".
During the work day while wearing your geek hard-hat, think in UTC. Only at the end of the day when switching to your layperson’s had should you go back to thinking of the local time of your town.
Your business logic should focus on UTC. Your database storage, business logic, data exchange, serialization, logging, and your own thinking should all be done in UTC time zone (and 24-hour clock, by the way). When presenting data to users, only then apply a particular time zone. Think of zoned date-times as an external thing, not a working part of your app’ internals.
On the Java side, use java.time.Instant (a moment on the timeline in UTC) in much of your business logic.
Instant now = Instant.now();
Hopefully JDBC drivers will eventually be updated to deal with java.time types like Instant directly. Until then we must use java.sql types. The old java.sql class have new methods for conversion to/from java.time.
java.sql.TimeStamp ts = java.sql.TimeStamp.valueOf( instant );
Now pass that java.sql.TimeStamp object via setTimestamp on a PreparedStatement to be saved to a column defined as TIMESTAMP WITH TIME ZONE in Postgres.
To go the other direction:
Instant instant = ts.toInstant();
So that is easy, going from Instant to java.sql.Timestamp to TIMESTAMP WITH TIME ZONE, all in UTC. No time zones involved. The current default time zone of your server OS, your JVM, and your clients, is all irrelevant.
To present to user, apply a time zone. Use proper time zone names, never the 3-4 letter codes such as EST or IST.
ZoneId zoneId = ZoneId.of( "America/Montreal" );
ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );
You can adjust into a different zone as needed.
ZonedDateTime zdtKolkata = zdt.withZoneSameInstant( ZoneId.of( "Asia/Kolkata" ) );
To get back to an Instant, a moment on the timeline in UTC, you can extract from the ZonedDateTime.
Instant instant = zdt.toInstant();
No where in there did we use LocalDateTime.
If you do get a piece of data without any offset-from-UTC or time zone, such as 2016-04-04T08:00, that data is entirely useless to you (assuming we are not talking about the Christmas or Company Lunch type scenarios discussed above). A date-time without offset/zone info is like a monetary amount without indicating currency: 142.70 or even $142.70 -- useless. But USD 142.70, or CAD 142.70, or MXN 142.70… those are useful.
If you do get that 2016-04-04T08:00 value, and you are absolutely certain of the intended offset/zone context, then:
- Parse that string as a
LocalDateTime.
- Apply an offset-from-UTC to get a
OffsetDateTime, or (better) apply a time zone to get a ZonedDateTime.
Like this code.
LocalDateTime ldt = LocalDateTime.parse( "2016-04-04T08:00" );
ZoneId zoneId = ZoneId.of( "Asia/Kolkata" ); // Or "America/Montreal" etc.
ZonedDateTime zdt = ldt.atZone( zoneId ); // Or atOffset( myZoneOffset ) if only an offset is known rather than a full time zone.
Your Question really is a duplicate of many others. These issues have been discussed many times in other Questions and Answers. I urge you to search and study Stack Overflow to learn more on this topic.
JDBC 4.2
As of JDBC 4.2 we can directly exchange java.time objects with the database. No need to ever use java.sql.Timestamp again, nor its related classes.
Storing, using OffsetDateTime as defined in the JDBC spec.
myPreparedStatement.setObject( … , instant.atOffset( ZoneOffset.UTC ) ) ; // The JDBC spec requires support for `OffsetDateTime`.
…or possibly use Instant directly, if supported by your JDBC driver.
myPreparedStatement.setObject( … , instant ) ; // Your JDBC driver may or may not support `Instant` directly, as it is not required by the JDBC spec.
Retrieving.
OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;
About java.time
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.
Where to obtain the java.time classes?
