0

When running the following query, I would expect it to give the same result independently of the session time zone but instead it gives different result depending on the session:

select '2025-09-22' >= date_trunc('week', '2025-09-22 13:20:24.951556+02'::timestamptz, 'UTC')

It gives true when executed within session that has set time zone 'UTC';

It gives false when executed within session that has set time zone 'Europe/Copenhagen';

The Postgresql version:

select version();
-- PostgreSQL 17.5 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 12.4.0, 64-bit

Can someone explain why it acts differently when I specify the time zone on the date_trunc function to be 'UTC'?

Improve this question

1 Answer 1

Reset to default
1

This is because

the truncation is performed with respect to a particular time zone; for example, truncation to day produces a value that is midnight in that zone

This applies to the week truncation as well. Since UTC is two hours behind Copenhagen, midnight in UTC is 02:00 in Europe/Copenhagen.

postgres=# set time zone 'Europe/Copenhagen';
SET

postgres=# select date_trunc('week', '2025-09-22 13:20:24.951556+02'::timestamptz, 'UTC');
       date_trunc
------------------------
 2025-09-22 02:00:00+02
(1 row)

postgres=# select date_trunc('week', '2025-09-22 13:20:24.951556+02'::timestamptz);
       date_trunc
------------------------
 2025-09-22 00:00:00+02
(1 row)

In your query the left side value '2025-09-22' is converted to '2025-09-22 00:00:00+02', which cannot be greater than or equal to '2025-09-22 02:00:00+02'.

Improve this answer
2
  • Okay, I guess the misleading part was the assumption that the time zone parameter on the date_trunc function would return the timestamp in that time zone. But why does the conversion of '2025-09-22' happen automatically to the session time zone? Commented Sep 22 at 14:19
  • To which time zone would you expect it to be converted, if not the one defined in the session? I mean, the whole idea of set time zone is to inform the behaviour of the default conversion, no? Commented Sep 22 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.