Select a date in my time zone when database is in UTC with PostgreSQL

Question

I want to select a day in my timezone for example:

from the 2021-09-09 00:00 +02 to 2021-09-10 00:00 +02. But the following code:

set timezone TO 'Europe/Berlin';
SELECT TIMESTAMP::timestamptz,sensor_id,value
FROM my_table
WHERE sensor_id IN (1,2,3)
AND TIMESTAMP > '2021-09-09' AND TIMESTAMP < '2021-09-10' 
ORDER BY TIMESTAMP

gives me the right values but with wrong timezone for example first rows are:

timestamp,sensor_id,value
2021-09-09 02:00 +02,1,21
2021-09-09 02:00 +02,2,34
2021-09-09 02:00 +02,3,54

but should be 2021-09-09 00:00 +02 or 2021-09-08 22:00 +00

The problem is bigger when the difference with utc changes between winter and summer

Anybony can help me?

PostgreSQL v.12

nicht verfügbar · Accepted Answer · 2022-01-11 14:10:40Z

1

SELECT TIMESTAMP::timestamptz AT TIME ZONE 'CETDST',sensor_id,value
FROM my_table
WHERE sensor_id IN (1,2,3)
AND TIMESTAMP AT TIME ZONE 'CETDST' > '2021-09-09'
AND TIMESTAMP AT TIME ZONE 'CETDST' < '2021-09-10' 
ORDER BY TIMESTAMP

edited Jan 11, 2022 at 14:10

answered Jan 11, 2022 at 13:28

nicht verfügbar

1621 silver badge12 bronze badges

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

9 Comments

nicht verfügbar Over a year ago

Whoops sorry. You can try "CETDST" (Central European Daylight Savings Time). I am not 100% sure if it does, but i thinks it observes wether it's summer or winter time.

mat Over a year ago

write the full query because looking deeper doesn't seem to work (AT TIME ZONE has to be written after timestamptz)

nicht verfügbar Over a year ago

SELECT TIMESTAMP::timestamptz AT TIME ZONE "CETDST",sensor_id,value FROM my_table WHERE sensor_id IN (1,2,3) AND timestamptz > '2021-09-09' AND timestamptz < '2021-09-10' ORDER BY timestamptz

nicht verfügbar Over a year ago

Sorry, nevermind. I thoght "TIMESTAMP" was the type of the column. Let me take another look.

nicht verfügbar Over a year ago

I have edited the code above.

|

Collectives™ on Stack Overflow

Select a date in my time zone when database is in UTC with PostgreSQL

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related