Different TimeZone outputs wrong values

Question

I am wondering why I am getting different times from this post Post

set timezone to 'UTC';

SELECT timestamptz '2012-03-05 17:00:00+0';  
Expected :2012-03-05 17:00:00+00
Got : 2012-03-05 22:30:00

SET timezone TO 'Europe/Berlin';

SELECT '2012-03-05 17:00:00+0'::timestamptz;  
Expected :  2012-03-05 18:00:00+01
Got: 2012-03-05 22:30:00



SELECT timestamptz '2012-03-05 18:00:00+1';
Expected : 2012-03-05 17:00:00+00
Got : 2012-03-05 22:30:00

score 1 · Accepted Answer · answered Mar 16 '19 at 08:12

1

Actually I am using DBeaver as a DB IDE and there we need to do some adjustments in dbeaver.ini refer this post for more insight

answered Mar 16 '19 at 08:12

Prabhat Mishra

951
2
12
33

score 0 · Answer 2 · answered Mar 11 '19 at 17:16

0

I believe the issue is how you are setting your timezone.

Can you try changing SET timezone TO to SET TIME ZONE and see if it works?

You can see the documentation below for more help: https://www.postgresql.org/docs/9.1/datatype-datetime.html#DATATYPE-TIMEZONES

answered Mar 11 '19 at 17:16

Japes

56
5

[The docs](https://www.postgresql.org/docs/9.1/sql-set.html) say: "`SET TIME ZONE value` is an alias for `SET timezone TO value`." – Matt Johnson-Pint Mar 11 '19 at 17:35

score 0 · Answer 3 · answered Mar 12 '19 at 07:30

0

Your expectations are correct. The problem must be that you are running the statements in different database sessions.

The scope of SET is limited to the current session.

answered Mar 12 '19 at 07:30

Laurenz Albe

209,280
17
206
263

Different TimeZone outputs wrong values

3 Answers3