CODEWITHSUNDEEP
sqlpostgresqltimezone
mat
mat

Reputation: 191

PostgreSQL get results in current time zone

as said in the title I would like to have a query that returns the value of the time stamp in my current time zone (even according summer time!).

my_table is:

|timestamp|name|value|property1|property2|
|---|---|---|---|---|
|2021-08-01 00:00:00+00|10|0.44|0|0|
|2021-08-01 00:05:00+00|15|0.76|0|0|
|2021-08-01 00:10:00+00|12|0.28|0|0|

(Don't ask me why I cannot put this table directly in markdown...prob cause the dates)

Now for example if I have to select the 24h corresponding to the entire day in my time zone at the moment my solution is:

SELECT timestamp AT TIME ZONE 'CEST',name,value
FROM my_table
WHERE name IN (10,11,12)
AND timestamp BETWEEN '2021-08-01 00:00:00+02' AND '2021-08-02 00:00:00+02' 
ORDER BY timestamp DESC

As you can see there is a problems here:

There is a way to avoid this conceptual repetition?? any suggestion even to improve the query is appreciated

the command SELECT version(); gives me back PostgreSQL 12.7

Upvotes: 1

Views: 218

Answers (1)

jjanes
jjanes

Reputation: 44373

Set your session's timezone appropriately.

set timezone TO 'Europe/Berlin';

select '2021-08-01 00:00:00+00'::timestamptz;
      timestamptz       
------------------------
 2021-08-01 02:00:00+02

select '2021-12-01 00:00:00+00'::timestamptz;
      timestamptz       
------------------------
 2021-12-01 01:00:00+01

select '2021-08-01 00:00:00'::timestamptz;
      timestamptz       
------------------------
 2021-08-01 00:00:00+02

What is your session timezone set to now?

Upvotes: 1

Related Questions