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

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

CodePudding user response：

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