CODEWITHSUNDEEP
postgresqltimezone
mat
mat

Reputation: 191

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

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

Upvotes: 0

Views: 420

Answers (1)

nicht verf&#252;gbar
nicht verf&#252;gbar

Reputation: 152

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

Upvotes: 1

Related Questions