0

I'm using Java and JDBC connect to a PostgreSQL database. I have a problem saving time to the database. My database have table student with column look like

Id,Name,Created_at(timestamp)

//some row here

I using JDBC connect to PostgreSQL and save my data

jdbc:postgresql://localhost:3306/someDatabase?useSSL=false&serverTimezone=UTC

When I insert data my data looks like

Student student = new Student()
student.name = "some name";
student.createdAt = LocalDateTime.now()

//some logic save

However, when I insert into the database, PostgreSQL saves the same time as my server. I want if my time server is "2021-10-10 00:00:00", when I save to database it must save to database as "2021-10-09 15:00:00". I want when it save it must convert to timezone Japan.

When I select database, timezone on PostgreSQL +9 . If I change code some like:

TimeZone.setDefault(TimeZone.getTimeZone("UTC"))

it does not help me.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
BaoTrung Tran
  • 392
  • 2
  • 6
  • 21

1 Answers1

2

You are using the wrong classes and types.

Never use TimeZone and Timestamp classes. These are part of the terrible date-time classes from the earliest versions of Java, supplanted years ago by the modern java.time classes defined in JSR 310. Specially replaced by ZoneId and Instant/OffsetDateTime.

In Postgres, be sure your created_at column is of the type TIMESTAMP WITH TIME ZONE, not WITHOUT.

Generally best to capture the current moment as a Instant. That class represents a moment as seen in UTC.

Instant instant = Instant.now() ;

Unfortunately, the people writing the JDBC 4.2 spec inexplicably required support for OffsetDateTime but not the two more commonly used classes Instant and ZonedDateTime. No matter, we can easily convert back and forth, if your particular JDBC driver does not support Instant.

OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;

Or, skip the Instant.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Send to the database.

myPreparedStatement.setObject( … , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

The LocalDateTime class is the wrong one here. That class represents a date and time without the context of a time zone or offset-from-UTC. So that class cannot represent a moment, a point on the timeline. So this class cannot be used to record when something happened. I cannot think of any scenario where calling LocalDateTime.now() would be the right thing to do.

These topics have been addressed on many existing Questions and Answers. Search to learn more.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • There is nothing inexplicable about requiring support for `OffsetDateTime` and not `Instant` and `ZonedDateTime`: the SQL standard only defines support for offset-based `WITH TIME ZONE` types, and therefor so does JDBC. Using `Instant` wouldn't preserve any stored offset information, and for storing wouldn't be much different from using an `OffsetDateTime` with offset zero. – Mark Rotteveel May 25 '21 at 14:40
  • @MarkRotteveel Converting back-and-forth between `Instant` and `OffsetDateTime` with `ZoneOffset.UTC` is utterly simple. Why not require JDBC drivers to perform that conversion for the convenience of the calling programmer? Regarding `ZonedDateTime`, thank you for the explanation, that makes sense now. – Basil Bourque May 25 '21 at 15:17
  • Because an Instant is not an OffsetDateTime, and the choice was made to only use the type that directly maps to the equivalent SQL standard type. – Mark Rotteveel May 25 '21 at 15:39