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.
4 Answers
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 ?
5My 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;
First find your default timezone. by using following query.
Select current_setting('timezone');
In my case Asia/Karachi
k, now just try following query.
Select Cast('1990-01-25' as Date) at time zone '<Your Default Timezone>' at time zone 'utc';In my case.
Select Cast('1990-01-25' as Date) at time zone 'Asia/Karachi' at time zone 'utc';In my case this did the trick (thanks to shajji answer)
to_timestamp(:date, 'YYYY-MM-DD"T"HH24:MI:SS:MS"Z"') at time zone (select current_setting('timezone')) at time zone 'Etc/UTC'
