# Date\_trunc changes time zone

**URL:** <https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198>\
**Category:** Hibernate ORM\
**Created:** [March 12, 2025, 9:51am UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198 "2025-03-12T09:51:32Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ofir-popowski](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/ofir-popowski/32/3900_2.png) [@ofir-popowski](https://discourse.hibernate.org/u/ofir-popowski)\
**Post date:** [March 12, 2025, 9:51am UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198/1 "2025-03-12T09:51:32Z")

</div>

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

```auto
@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:

```auto
@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

```auto
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

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 12, 2025, 2:50pm UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198/2 "2025-03-12T14:50:32Z")

</div>

Please try to create a reproducer with our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a bug ticket in our [issue tracker](https://hibernate.atlassian.net) and attach that reproducer.

---

<div class="post-metadata">

**Author:** ![cigaly](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/cigaly/32/2390_2.png) [@cigaly](https://discourse.hibernate.org/u/cigaly)\
**Post date:** [March 16, 2025, 10:20am UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198/3 "2025-03-16T10:20:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cigaly](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/cigaly/32/2390_2.png) [@cigaly](https://discourse.hibernate.org/u/cigaly)\
**Post date:** [March 16, 2025, 4:55pm UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198/4 "2025-03-16T16:55:55Z")

</div>

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 using`Instant`. 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.

---

<div class="post-metadata">

**Author:** ![cigaly](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/cigaly/32/2390_2.png) [@cigaly](https://discourse.hibernate.org/u/cigaly)\
**Post date:** [March 17, 2025, 11:06am UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198/5 "2025-03-17T11:06:36Z")

</div>

@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

```auto
                         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

```auto
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

```auto
  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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 25, 2025, 12:32pm UTC](https://discourse.hibernate.org/t/date-trunc-changes-time-zone/11198/6 "2025-03-25T12:32:15Z")

</div>

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](https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html#basic-timestamp-with-time-zone) and [annotation support](https://docs.jboss.org/hibernate/orm/6.6/userguide/html_single/Hibernate_User_Guide.html#_timezonestorage).

Also see this discussion about storing the zone id: [Jira](https://hibernate.atlassian.net/browse/HHH-16289)