CODEWITHSUNDEEP
postgresql
Vlad K
Vlad K

Reputation: 2831

How to convert a string to timestamp in a desired timezone

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.

Upvotes: 68

Views: 190708

Answers (6)

EAmez
EAmez

Reputation: 895

Just in case this helps someone. My problem was the same: database values were in GMT, while my server was configured with different timezone, and I needed GMT values to be converted to GMT+10.

Also my dates were stored as varchar with YYYYMMDDHH24MISS format.

to_timestamp(dateColumn, 'YYYYMMDDHH24MISS')::timestamp at time zone 'GMT' at time zone 'AEST'

The important thing here is this part: ::timestamp, whithout this it won't work as desired.

Also note that while first at time zone returns a timestamptz value, second one doesn't.

Upvotes: 2

Nate Anderson
Nate Anderson

Reputation: 21034

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;

Upvotes: 24

Roman Pelant
Roman Pelant

Reputation: 91

This easy way works for me, you just declare that the timestamp without time zone is UTC. Than whatever time zone you have on server the timestamp is selected and displayed in your time zone.

 TO_TIMESTAMP(start_ts)::timestamp at time zone 'UTC'

Upvotes: 9

Paolo Cappello
Paolo Cappello

Reputation: 121

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'

Upvotes: 10

shajji
shajji

Reputation: 1667

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';

Upvotes: 12

SegFault
SegFault

Reputation: 1175

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 ?

Upvotes: 49

Related Questions