Postgres does not have a data type like TIMESTAMP . Postgres has two types for the date and time of the day: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE . These types have very different behaviors regarding time zone information.
- The
WITH type uses any information about the offset or time zone to set the date-time to UTC , and then removes the offset or time zone; Postgres never saves estimate / zone information. - The
WITHOUT type ignores any offset or zone information that may be present.
You almost always want the WITH type, as described here by expert David E. Wheeler. WITHOUT only makes sense when you have a vague idea of a time date, not a fixed point on the timeline. For example, “Christmas this year starts from 2016-12-25T00: 00: 00” will be stored in WITHOUT , since it applies to any time zone, it has not yet been applied to any individual time zone to get the actual time on the timeline. If the Santas elves tracked the start time for Eugene Oregon US, then they would use the WITH type and an input that included an offset or time zone, for example 2016-12-25T00:00:00-08:00 , which is saved in Postgres as 2016-12-25T08:00.00Z (where Z means Zulu or UTC).
The equivalent of Postgres TIMESTAMP WITHOUT TIME ZONE in java.time is java.time.LocalDateTime . Since you intend to work in UTC (good), you should not use LocalDateTime (the bad thing). This can be a major confusion and trouble for you. You keep thinking about using LocalDateTime or ZonedDateTime , but you shouldn't use either; you should use Instant instead (see below).
I also wonder if it’s because I’m switching from LocalDateTime to ZonedDateTime and vice versa, I can lose time zone information.
You really are. The whole point of LocalDateTime is to lose LocalDateTime information. . Therefore, we rarely use this class in most applications. Again, an example of Christmas. Or another example: "Company policy: all our factories around the world dine at 12:30." This will be LocalTime , and for a specific date, LocalDateTime . But that makes no real sense, not the actual point on the timeline, until you apply the time zone to get the ZonedDateTime . This lunch break will be at different points on the timeline in the Delhi factory than in the Düsseldorf factory, and will again be in the Detroit factory.
The word "Local" in LocalDateTime can be counterintuitive, as it means the lack of a certain locality. When you read “Local” in the class name, think “Not for a moment ... not on the timeline ... just a fuzzy idea of a“ peculiar date-time ”.
Your servers should always be set to UTC in the time zone of the operating system. But your programming should never depend on this externality, since it is too difficult for sysadmin to change it or for any other Java application to change the current default time zone in the JVM. Therefore, always indicate the desired / expected time zone. (The same goes for Locale , by the way.)
Result:
- You work too much.
- Programmers / sysadmins must learn to "think globally, be local."
During the work day, when you wear your computer hat, think in UTC. Only at the end of the day, when you move on to your laypeople, should you return to thinking about the local time of your city.
Your business logic should focus on UTC. Your database repository, business logic, data exchange, serialization, logging and your own thinking should be done in the UTC time zone (by the way, and the 24-hour clock). When presenting data to users, only then use a specific time zone. Think of zoned dates as external, not the working part of internal applications.
On the Java side, use java.time.Instant (a moment in the UTC timeline) in most of your business logic.
Instant now = Instant.now();
We hope that JDBC will eventually be updated to directly access java.time types such as Instant . Until then, we have to use java.sql types. The old java.sql class has new methods for converting to / from java.time.
java.sql.TimeStamp ts = java.sql.TimeStamp.valueOf( instant );
Now pass the setTimestamp object through setTimestamp to the PreparedStatement to save to the column defined as TIMESTAMP WITH TIME ZONE in Postgres.
To go in another direction:
Instant instant = ts.toInstant();
So it's easy going from Instant to java.sql.TimeStamp to TIMESTAMP WITH TIME ZONE , all in UTC. Time zones are not involved. The current time zone of your server, your JVM, and your clients does not matter by default.
To introduce the user, use the time zone. Use the correct time zone names , not 3-4 letter codes, such as EST or IST .
ZoneId zoneId = ZoneId.of( "America/Montreal" ); ZonedDateTime zdt = ZonedDateTime.ofInstant( instant , zoneId );
If necessary, you can configure another zone.
ZonedDateTime zdtKolkata = zdt.withZoneSameInstant( ZoneId.of( "Asia/Kolkata" ) );
To return to Instant , in the UTC timeline, you can extract from ZonedDateTime .
Instant instant = zdt.toInstant();
No where we used LocalDateTime .
If you receive part of the data without any offset-from-UTC or time zone, for example 2016-04-04T08:00 , this data is completely useless to you (assuming that we are not talking about scenarios such as "Christmas" or "Company" Lunch "discussed above.) A date-date without costing / zone information is like a monetary amount without specifying a currency: 142.70 or even $142.70 is useless. But USD 142.70 , or CAD 142.70 , or MXN 142.70 ... they are useful.
If you get this value 2016-04-04T08:00 , and you are absolutely sure about the intended offset / zone context, then:
- Parse this line as
LocalDateTime . - Use UTC-offset to get
OffsetDateTime or (better) apply a timezone to get 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 is indeed a duplicate of many others. These questions have been repeatedly discussed in other Questions and Answers. I encourage you to search and study Stack Overflow to learn more about this topic.