CODEWITHSUNDEEP
postgresql
Rufi
Rufi

Reputation: 2645

Postgres times: wrong conversion with time zones

Let's assume that we have some date, for example 2018-06-25T06:00:00Z. So, in case I do the following I get the same date:

select '2018-06-25T06:00:00Z'::timestamp at time zone 'UTC';

Now, I want to get local time for this date and as I am located in Helsinki I do the following:

select '2018-06-25T06:00:00Z'::timestamp at time zone 'Europe/Helsinki';

As I know that Helsinki is currently +3 hours my expectation is to get 2018-06-25 09:00:00.000000, however, for some reason I get 2018-06-25 03:00:00.000000.

So, why it decided to deduct this 3 hours instead of adding them?

Note, that this select current_setting('timezone') returns UTC.

Upvotes: 1

Views: 208

Answers (1)

clemens
clemens

Reputation: 17711

Your second statement interprets the given timestamp as timestamp in the time zone Europe/Helsinki and converts it to a TIMESTAMP WITH TIMEZONE. psql prints the result of the timestamp according the the SESSION TIMEZONE.

To convert your timestamp from UTC to Europe/Helsinki, you should use a timestamp with time zone:

SELECT '2018-06-25T06:00:00Z'::TIMESTAMPTZ AT TIME ZONE 'Europe/Helsinki';

This will create a timestamp and convert it to Europe/Helsinki. You may change the time zone for output with:

SET SESSION TIMEZONE TO 'Europe/Helsinki';

Upvotes: 1

Related Questions