What Is the Most Recommended Way to Store Time in Postgresql Using Java

What is the most recommended way to store time in PostgreSQL using Java?

Any strategy for storing date-and-time data in PostgreSQL should, IMO, rely on these two points:

• Your solution should never depend on the server or client timezone setting.
• Currently, PostgreSQL (as most databases) doesn't have a datatype to store a full date-and-time with timezone. So, you need to decide between an Instant or a LocalDateTime datatype.

My recipe follows.

---

If you want to record the physical instant at when a particular event ocurred, (a true "timestamp" , typically some creation/modification/deletion event), then use:

• Java: Instant (Java 8 , or Jodatime).
• JDBC: java.sql.Timestamp
• PostgreSQL: TIMESTAMP WITH TIMEZONE (TIMESTAMPTZ)

(Don't let PostgreSQL peculiar datatypes WITH TIMEZONE/WITHOUT TIMEZONE confuse you: none of them actually stores a timezone)

Some boilerplate code: the following assumes that ps is a PreparedStatement, rs a ResultSet and tzUTC is a static Calendar object corresponding to UTC timezone.

public static final Calendar tzUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));  

Write Instant to database TIMESTAMPTZ:

Instant instant = ...;
Timestamp ts = instant != null ? Timestamp.from(instant) : null;
ps.setTimestamp(col, ts, tzUTC);   // column is TIMESTAMPTZ!

Read Instant from database TIMESTAMPTZ:

Timestamp ts = rs.getTimestamp(col,tzUTC); // column is TIMESTAMPTZ
Instant inst = ts !=null ? ts.toInstant() : null;

This works safely if your PG type is TIMESTAMPTZ (In that case, the calendarUTC has no effect in that code ; but it's always advisable to not depend on defaults timezones).
"Safely" means that the result will not depend on server or database timezone, or timezones information: the operation is fully reversible, and whatever happens to timezones settings, you'll always get the same "instant of time" you originally had on the Java side.

---

If, instead of a timestamp (an instant on the physical timeline), you are dealing with a "civil" local date-time (that is, the set of fields {year-month-day hour:min:sec(:msecs)}), you'd use:

• Java: LocalDateTime (Java 8 , or Jodatime).
• JDBC: java.sql.Timestamp
• PostgreSQL: TIMESTAMP WITHOUT TIMEZONE (TIMESTAMP)

Read LocalDateTime from database TIMESTAMP:

Timestamp ts = rs.getTimestamp(col, tzUTC); //
LocalDateTime localDt = null;
if( ts != null ) 
    localDt =  LocalDateTime.ofInstant(Instant.ofEpochMilli(ts.getTime()), ZoneOffset.UTC);

Write LocalDateTime to database TIMESTAMP:

  Timestamp ts = null;
  if( localDt != null)    
      ts = new Timestamp(localDt.toInstant(ZoneOffset.UTC).toEpochMilli()), tzUTC);
  ps.setTimestamp(colNum,ts, tzUTC); 

Again, this strategy is safe and you can sleep peacefully: if you stored 2011-10-30 23:59:30 , you'll retrieve those precise fields (hour=23, minute=59... etc) always, no matter what - even if tomorrow the timezone of your Postgresql server (or client) changes, or your JVM or your OS timezone, or if your country modifies its DST rules, etc.

---

Added: If you want (it seems a natural requirement) to store the full datetime specification (a ZonedDatetime: the timestamp together with the timezone, which implicitly also includes the full civil datetime info - plus the timezone)... then I have bad news for you: PostgreSQL hasn't a datatype for this (neither other databases, to my knowledge). You must devise your own storage, perhaps in a pair of fields: could be the two above types (highly redundant, though efficient for retrieval and calculation), or one of them plus the time offset (you lose the timezone info, some calculations become difficult, and some impossible), or one of them plus the timezone (as string; some calculations can be extremely costly).

Should I store the timezone separately from the timestamp for Postgres and JDBC?

When you store a timestamp with time zone (timestamptz) it's converted to UTC for storage in the DB. When retrieved, it's converted to the client's current timezone, not the timezone it was originally in. It's a point in time, basically.

There is also timestamp without time zone (timestamp). This is not subject to conversion, but does not carry a timestamp with it. If you store a timestamp with your client time zone set to UTC, then retrieve it when the client time zone is '+08:00', you get the same value. That's half what you want, in that it preserves the raw time value.

The names and behaviours are awful and confusing, but set by the SQL standard.

You must store the time zone separately if you wish to record a point in time at a particular time zone. I'd recommend storing it as an INTERVAL with a CHECK constraint limiting it to be colname BETWEEN INTERVAL '-12' HOUR + INTERVAL '1' SECOND AND INTERVAL '12' HOUR. That definition rejects -12:00 and accepts +12:00; I'm not totally sure that's right, so check.

You could either store the timestamp of local time at that time zone (what I'd probably do), or store the timestamptz of the UTC time when the event occurred plus an offset that lets you convert it to local time.

Either will work fine for JDBC. For JPA, it'll depend on how well your provider understands and maps interval types. Ideally you want a transient generated field in your entity that reconstructs the Calendar instance you want using the timestamp and interval stored in the database.

Use java.util.Date to query column with TIMESTAMPTZ

Don't use java.util.Date, use java.time.OffsetDateTime

OffsetDateTime modifiedAfter = OffsetDateTime.of(2021, 6, 11, 15, 20, 0, 0, ZoneOffset.UTC);
ps.setObject(1, modifiedAfter);

Do the same when reading the value:

ResultSet rs = statement.executeQuery(...);
while (rs.next()) {
  OffsetDateTime odt = rs.getObject(1, OffsetDateTime.class);
  ....
}

---

If you don't care about time zones at all, and are sure that everything will always be specified with the same time zone (e.g. UTC), then use timezone as the column data type in Postgres.

Then use LocalDateTime instead of OffsetDateTime in Java

Java 8, Hibernate and PostgreSQL - how to store date with timezone?

Normally hibernate store dates using UTC. Then you need to convert all time zones into UTC.

On startup,

TimeZone.setDefault(TimeZone.getTimeZone("ETC/UTC"));

In JOOQ how do we use Java Instant to map to Postgresql's timetamp type?

On PostgreSQL timestamp data types

In PostgreSQL (I'm assuming you're using PG), TIMESTAMP is short for TIMESTAMP WITHOUT TIME ZONE as documented here: https://www.postgresql.org/docs/current/datatype-datetime.html

The best Java types to map that to are:

• java.sql.Timestamp (the old JDBC type, whose valueOf() and toString() behaviours do not support time zones and also work with your local time zone)
• java.time.LocalDateTime

You can see this also in the JDBC spec, or derive it from the fact that there are methods like java.sql.Timestamp.valueOf(LocalDateTime) and java.sql.Timestamp.toLocalDateTime().

The fact that you prefer working with java.time.Instant hints at it being better to use TIMESTAMP WITH TIME ZONE in PostgreSQL, which JDBC (and thus jOOQ) maps to OffsetDateTime by default. You can rewrite that type without a converter to INSTANT in jOOQ:
https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/

You can also see this recommendation in the PostgreSQL wiki:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29

Your specific configuration

Regarding your specific attempts to map the type:

.withIncludeExpression("timestamp")

That only applies to column named timestamp. You probably meant to apply your forced type to type names?

.withIncludeTypes("timestamp")

Reading and Writing UTC to TIMESTAMP in Postgresql

You could set the timezone of your RDBMS to UTC, see https://medium.com/building-the-system/how-to-store-dates-and-times-in-postgresql-269bda8d6403

When that's done, whatever dates you store, they will be in UTC. Converting from UTC into something else can be done either in queries, like

select created_at at time zone 'utc' at time zone 'america/los_angeles'
from users;

Taken from https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql

Or, you can convert the timezone at application level.

---

Related Topics