1

Application server (JVM) running in time zone: Europe/Dublin - TimeZone.getDefault().getID()

Time zone for JDBC connections set for Spring / Hibernate using the configuration property:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

Java Entity:

@Column(name = "local_time")
private LocalTime localTime;

@Column(name = "local_date_time")
private LocalDateTime localDateTime;

@Column(name = "offset_time")
private OffsetTime offsetTime;

Java values debugged on application server (JVM) ..

LocalTime - time SQL type:

// LocalTime.ofInstant(now, Clock.systemDefaultZone().getZone())
01:32:13.283256

LocalDateTime - timestamp SQL type:

// LocalDateTime.ofInstant(now, Clock.systemDefaultZone().getZone())
2022-12-06T01:32:13.283256

OffsetTime - time with time zone SQL type:

// OffsetTime.ofInstant(now, Clock.systemDefaultZone().getZone())
01:32:13.283256Z

Database configured to use UTC:

postgres=# select name, setting, source from pg_settings where name='TimeZone';
   name   | setting |       source       
----------+---------+--------------------
 TimeZone | Etc/UTC | configuration file

Query database (PostgreSQL) for values:

select * from item;

 id | local_time |      local_date_time       | offset_time
----+------------+----------------------------+-------------
  1 | 00:32:13   | 2022-12-06 01:32:13.283256 | 00:32:13+00

Why are the column values for local_time and offset_time 1 hour behind UTC? Shouldn't they be the same as local_date_time i.e 01:32 rather than 00:32

local_date_time is the only column with correct value when directly querying the database. Why is that?

bobbyrne01
  • 6,295
  • 19
  • 80
  • 150
  • 3
    Since a time value has no date associated with it, there's no way of telling whether daylight savings time is in effect, so it is not taken into account. – Robby Cornelissen Dec 06 '22 at 01:51
  • Using e.g. `\d+ item`, what's the column type of `local_time`, `local_date_time`, and `offset_time`? – rzwitserloot Dec 06 '22 at 03:14
  • @rzwitserloot The database types for each column are already outlined in the question for `local_time`, `local_date_time` and `offset_time` – bobbyrne01 Dec 06 '22 at 09:52
  • It seems to be a bug. Instead of waiting for an answer (which can be simply based on guesses), you should raise a ticket with the Postgresql community and/or support team. – Arvind Kumar Avinash Dec 14 '22 at 16:00
  • Seems like the same issue as https://stackoverflow.com/questions/74717698/why-is-java-localtime-013213-283256-stored-as-time-003213-in-database-1-hou/74789263#74789263, as Robby Cornelissen alluded to above. Maybe I missed something. :-) – Andrew Dec 15 '22 at 10:46
  • **Missing information…** You need to show us the *exact* structure of your `item` table. And you need to show us *exactly* how you wrote the row of data shown in your Question. And you need to explain *exactly* how you ran that query, in what tool, along with the **current default time zone in the database session** used by that tool. Far too little info here for us to explain the behavior. – Basil Bourque Dec 18 '22 at 23:21

0 Answers0