Date_trunc changes time zone

Hibernate ORM
1

I have a query that truncates to month in order to group by a month. My query uses date_trunc('month', foo.date), but it ends up subtracting 2 hours, since I’m at UTC+2 I think

Simplified example

@Entity
class Foo(
  val date: ZonedDateTime,
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE)
  val id: Long? = null,
)

I don’t know if it matters, but I’m using Spring Data, on top of Postgres. Here’s my query:

@Query("select new com.example.GroupedDTO(count(foo.id), date_trunc('month', foo.date) as date) from Foo foo group by date")

data class GroupedDTO(val count: Long, val date: ZonedDateTime)

If I save an entity with ZonedDateTime.now(), it has a date of today (12/03), truncated should return 01/03. Running the query directly on Postgres also returns it correctly. Running the query from the app instead returns 28/2 at 22:00, it is off by 2 hours

Is there any way to ensure I can perform this query and have the grouped by result returned correctly?
Is it possible this is an issue with Spring Data and I should ask them instead?
(Hibernate version 6.6.8.Final)

I tried the same thing using the entity manager

entityManager.createQuery("select new com.example.GroupedDTO(count(foo.id), date_trunc('month', foo.date) as date) from Foo foo group by date", GroupedDTO::class.java)

and the dates here are still offset by 2 hours

2

Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

3

I have simple test case. Since it’s rainy day here, and I am big fan of java.time, I’ll look for solution.

4

One problem is that query in example will give result only for H2, PostgreSQL, and DB2. For all other databases will fail with message saying that date_trunc function is not defined. Of those three, only H2 is internally saving ZonedDateTime values converted to OffsetDataTime, and PostgreSQL and DB2 are usingInstant. I guess that in case of PostgreSQL may be possible to also use OffsetDateTime. Probably for DB2 as well, but I did not tested that.
Problem is that neither of those tjree JDBC drivers can use ZonedDateTimeDirectly. This is, most likely, limitation of JDBC specification, bu I did not checked documentation.
It is, of course, possible in org.hibernate.type.descriptor.java.ZonedDateTimeJavaType#wrap to use default ZoneId when converting OffsetDateTime and Instant value to ZonedDateTime, like it has been done for other types (why not for those two?!?). This is fixing problem with test in example.

5

@ofir-popowski I am afraid that there is no solution to problem you’ve described. It is not caused by date_trunc function. Same will happen when you select property of ZonedDateTime type.

There are (at least) two reasons for this problem. First, JDBC (and most of the databases I guess) is not supporting time zones. There are types with ‘zone’ in name, but this is actually time offset, not time zone. Time offset is difference between local time and UTC, while time zone is set of rules describing time offset change in time. The best that can be achieved is to preserve tie offset, but time zone can not be inferred from time offset.

The other, more serious problem, is that for most of databases I’ve tested (with exception of H2, Oracle, and Microsoft SQL Server) even time offset is not preserved. Generally I do not know if this is caused by Hibernate, JDBC drivers, or databases, but in case of PostgreSQL there is in database itself. For some reason, it is not preserving time offset for columns of type timestamp with time zone (but is preserving for time with time zone), For example, there is table

                         Table "public.temporal_test"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 id               | integer                  |           | not null | 
 time_offset      | time with time zone      |           |          | 
 date_time_offset | timestamp with time zone |           |          |

and insert

insert into temporal_test (id, time_offset, date_time_offset) values (12345, '18:30:43.941342+08'::time with time zone, '2025-03-17 18:30:43.941343+08'::timestamp with time zone)

but the result is

  id   |    time_offset     |       date_time_offset        
-------+--------------------+-------------------------------
 12345 | 18:30:43.941342+08 | 2025-03-17 11:30:43.941343+01

i.e. it replaced time offset of inserted value with my local offset (+1 hour)

In other words, nothing that can be ‘fixed’ in Hibernate.

6

Some databases support storing the zone offset (H2, Oracle and SQL Server and DB2 for z/OS), also see org.hibernate.dialect.Dialect#getTimeZoneSupport. I don’t know of databases that support storing the actual time zone itself, at least the JDBC spec does not specify support for passing/reading ZonedDateTime, only OffsetDateTime.

In Hibernate ORM we only support storing the offset itself, but not the time zone string. Also see this configuration option and annotation support.

Also see this discussion about storing the zone id: Jira