Postgres convert timestamp with timezone to date

Question

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)

Per @LaurenzAlbe answer change to SELECT date('2023-08-01'::timestamptz AT TIME ZONE 'Europe/Berlin'); 2023-08-01. — Adrian Klaver
– Adrian Klaver, Commented Aug 1, 2023 at 15:12
Keep in mind that a timestamp with time zone field doesn't actually store the time zone. So users with different time zone settings will get different dates back with that approach. For this reason, often it's useful to store an additional field that is a date type for the date relevant to the business logic, along side the timestamp. Especially for accounting - you probably don't want end-of-month dates being treated as start-of-month dates in other time zones. — Matt Johnson-Pint
– Matt Johnson-Pint, Commented Aug 1, 2023 at 16:37

Laurenz Albe · Accepted Answer · 2023-08-01 13:52:50Z

2

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.

answered Aug 1, 2023 at 13:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ST-DDT Over a year ago

This answer is confusing me. current_timestamp is a different value than the one I can pass to the query. FFR: SELECT '2023-08-01'::timestamptz AT TIME ZONE 'Europe/Berlin'

Laurenz Albe Over a year ago

But you said that the data type in the PostgreSQL table is timestamp with time zone, so I used current_timestamp as a substitute. All your examples show a timestamp without time zone which behaves quite differently. Perhaps you can add some detail to the question to add clarity.

ST-DDT Over a year ago

I think I confused myself here quite a bit, because the ::timestamp AT TIME ZONE part produces a timestamp with timezone, but timestamptz AT TIME ZONE produces a timestamp without timezone. date(timestamp) produces the date in the default/selected timezone which is UTC and not the passed timezone of the value. I'll probably have to use both approaches, one for filters passed by users, the other for the grouping of the in table data.

Raky · Accepted Answer · 2023-08-01 13:44:01Z

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.

Collectives™ on Stack Overflow

Postgres convert timestamp with timezone to date

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related