Question 220761 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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)

Tags:

2

Answers


  1. 0

    Here’s how you can do it

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

To apply it on your accounting data, use this

    SELECT
        (myTimeStampColumnName AT TIME ZONE 'Europe/Berlin')::date
        FROM
        my_accounting_table_name;

Please change `myTimeStampColumnName` with your actual Attribute Name holding the `TimeStamp` data and `my_accounting_table_name` with the actual Table Name of the parent `myTimeStampColumnName`.

    In order to Group/Sum, use this approach

    SELECT
    (myTimeStampColumnName AT TIME ZONE 'Europe/Berlin')::date AS europe_berlin_date,
    SUM(amount) AS total_amount
FROM
    my_accounting_table_name
GROUP BY
    (myTimeStampColumnName AT TIME ZONE 'Europe/Berlin')::date;

    Hope this is what you are looking for.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search