postgresqldatetimezonetimestamp-with-timezone

Postgres convert timestamp with timezone to date

I have a postgres database, that stores some accounting data. The accounting data have a timestamp (with timezone).

I would like to aggregate/group the accounting data by date. But the date should be in the user's/requested timezone not UTC.

SELECT '2023-08-01'::timestamp AT TIME ZONE 'Europe/Berlin'; -- 2023-07-31 22:00:00+00

Outputs the correct UTC value for the timestamp,

but if I cast that to date then it returns the UTC date:

SELECT date('2023-08-01'::timestamp AT TIME ZONE 'Europe/Berlin'); -- 2023-07-31

Is there a way to output the date in Europe/Berlin time? (2023-08-01)

Solution

  • Given a timestamp with time zone, you can get the current date in Germany at that time with

    SELECT date(current_timestamp AT TIME ZONE 'Europe/Berlin');

    Your example uses a timestamp without time zone, which may be the problem.