When should I use OffsetDateTime, ZonedDateTime and Instant in database applications?

user_4592248 0
Viewed 1165

The documentation of the java.time package says:

Many applications can be written only using LocalDate, LocalTime and Instant, with the time-zone added at the user interface (UI) layer.

The offset-based date-time types OffsetTime and OffsetDateTime, are intended primarily for use with network protocols and database access.

So if I want to save some date-time of an event in a database, e.g. the time a post comment was added to the system, should I use OffsetDateTime, not ZonedDateTime and Instant? Or maybe I should use LocalDateTime or Instant? Does it depend on the database access technology, e.g. whether I use Hibernate or JDBC?

Are there any rules when to use OffsetDateTime, ZonedDateTime and Instant in database applications?

The summary of the java.time package is not clear for me.

java
1 Answers

My recommendation is to look at what information you need to know beforehand to convert from one type to another or, equivalently, what information you lose.

The concrete use in each case may depend strongly on how the information is used.

However, in general terms:

  • Instant only store the elapsed time since 1970-01-01T00:00:00Z with a precision of nanoseconds. Then, you will be able to convert that instant to the exact date of that instant of time in any time zone, but the time zone is not stored, it is not known, in this class. For example if you need to sort, prioritize, queue, ... times, this could be the right class.
  • Local* these classes are somewhat the opposite of Instant. They store a specific date and time assuming you know (but do not store) what time zone it is. With these classes, you don't know what time instant it is! (unless someone tells you what time zone it occurs in). They can be useful for storing times and dates for which the time zone is known, e.g. arrivals and departures at airports, hotels, ... personal alarms (e.g. wake me up at 8 am every day; no matter where on Earth I am).
  • Offset* are a bit complicated to understand intuitively. They are like an Instant but also store the position on Earth where we are considered to be (not to be confused with "time use"/timezone!). The hours on Earth go from -12h to +12h. Instant is in UTC (i.e. +0h), so using Instant or Offset* is a bit similar, however, these classes are widely used for network protocols, as it is less clear to give an Instant 1628340379.718919021 than 2021-08-13T12:24:05.478482150+01:00.
  • Zoned* store as much information as possible, because they indicate a "time use"/timezone, that is to say, besides knowing the Instant, you know how the user expects you to show that date according to local customs and habits. Many countries on Earth advance or set back the clock according to the time of the year.

There are no silver bullet rules but very generally. If the information you need to store (not to be confused with how you need to display it) is:

  • instants in time only to know when something has happened or will happen globally (i.e., no matter where we are on Earth). So Instant.
  • otherwise, moments in the day, week, month (e.g. every Tuesday) assuming they occur where we are but without specifying where. Then we use Local*.
  • if we have to transfer an instant of time through a formatted protocol we can use Instant but Offset* is more appropriate.
  • if we have to store also how we will present to the user the time information (hourly usage) then we will use Zoned*. But it will be quite rare that we use this class because we are usually interested in the instant when something happens (either globally Instant or locally Local*) and we will format it to the user according to other preferences.

Only as an example, postgresql does not support timezone, then, you must to store or Local* or Instant (or Offset* but only in UTC what is bad), in any case you will need to know the hourly usage of the users but, if you have not timezone defined (e.g. you store events) Instant will be better.

As I say, without knowing your specific need, it is difficult to say which is better.

user_15407490
Related