57

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.

spoonboy
  • 2,570
  • 5
  • 32
  • 56

4 Answers4

42

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 ?

SegFault
  • 1,097
  • 1
  • 14
  • 14
  • 1
    Unfortunately, not. This is effectively a workaround rather than a proper solution. Of course, I could change the default timezone. But this not not what I'n looking for. – spoonboy Mar 09 '17 at 02:39
  • Could you give an example output you would expect ? Because the `to_timestamp(...)::timestamp without time zone` returns as expected a timestamp without time zone. If you see the time zone at the end of your `select` query (eg. `+04`), this is just the way PostgreSQL formats the output, which is controlled by `set time zone`, ie. `select timestamp with time zone` will always append the current session's time zone to the output... – SegFault Mar 09 '17 at 09:13
  • This is not about the format, this is about the value conversion. I'll update the question with an example. – spoonboy Mar 09 '17 at 20:10
  • This way carries the risk of accidentally leaving the timezone set in the session, which might cause hard to track down bugs if other queries make assumptions about the time zone. – jpmc26 Jun 09 '19 at 18:34
  • How to convert timestamp with timezone date to date, I tried different approach but didn't worked. select to_timestamp('2020-02-30T12:00:00.000Z')::timestamp b – ruyamonis346 May 22 '20 at 03:06
15

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;

Nate Anderson
  • 18,334
  • 18
  • 100
  • 135
  • 3
    For those who deal with non ISO formatted datetime, can use something like this ```select to_timestamp('20/01/2021 08:58:42+7', 'DD/MM/YYYY HH24:MI:SSTZH')::timestamptz;``` – Bertie May 25 '21 at 03:11
  • 1
    @AlbertGan How do you apply this on column from a table? – dan May 27 '21 at 16:59
  • 1
    @atp - `SELECT to_timestamp(ColumnName, 'DD/MM/YYYY HH24:MI:SSTZH')::timestamptz FROM TableName;` – Nate Anderson May 27 '21 at 17:54
  • 1
    @TheRedPea It works while returning the result but I wanted to make this change permanently in the given table. Is it possible to have that column type changed? – dan May 27 '21 at 18:16
  • Oh @atp, you want to modify/ALTER your column, vs select a new column. Maybe your column type is string, and you want type of ::timestamptz? I don't know if a single [`ALTER TABLE / ALTER COLUMN` statement](https://www.postgresql.org/docs/12/sql-altertable.html) can go from `string` to `timestamptz` in PostgreSQL (does anyone know?) – Nate Anderson May 27 '21 at 19:02
  • 1
    However @atp, you could create a **new** `timestamptz` " column, and transfer the `string` value from "**existing** column, into the **new** timestamptz column, then delete existing, rename the new one... Hope it makes sense... [This post describes a similar approach to modifying columns, using a new *table* strategy](https://stackoverflow.com/questions/408825/how-to-change-the-data-type-of-a-column-without-dropping-the-column-with-query): – Nate Anderson May 27 '21 at 19:05
  • 1
    @atp I would create a dbview to select another ```select to_timestamp``` column in addition to existing columns of the table. It seems simpler rather than having to change the base table. – Bertie May 30 '21 at 09:09
12

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';
shajji
  • 1,517
  • 10
  • 14
7

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'
Paolo Cappello
  • 91
  • 1
  • 2