How to convert a string to timestamp in a desired timezone

Question

I need to convert a string that represents a timestamp without time zone (i.e. "2017-03-08T20:53:05") in some time zone (e.g.'Etc/UTC') to a timestamp with timezone.

The issue is that the default time zone is not 'Etc/UTC'. So, when I'm trying to to

SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'Etc/UTC'

it converts the string to a value with the default timezone and then applies conversion from a local time zone to the 'Etc/UTC'. This is not what's requred.

Basically, I'm looking for a way to tell postgres that the original string value is representing time in a particular timezone, not the default local one.

Thank you.

Update: I've checked that the above statement is actually working.

SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'Etc/UTC'

I've been mislead by by the client's timezone setting.

Answer 1

You could run set time zone UTC; before your query:

set time zone UTC;
SELECT to_timestamp(field1, 'YYYY-MM-DD hh24:mi:ss')::timestamp without time zone at time zone 'Etc/UTC';

Does this solve your issue ?

Answer 2

My question is different than OP's question. My string already knows its timezone. I just want to convert to the timestamptz datatype.

In other words: "how to convert a string in a desired timezone, to a timestamp". I could use the approach described here and here; casting with ::timestamptz

SELECT '2016-01-01 00:00+10'::timestamptz;