casting string to date postgresql-CodePudding

Using postgres 14

I have a timestamp something like this 2011-04-26T05:04:11Z. Its in UTC time I tried converting it to a postgres timestamp using this function and i get a wrong result 2022-04-26 00:04:11-07. The time part seems messed up.

This is the query i have

select to_TIMESTAMP('2011-04-26T05:04:11Z','YYYY-MM-DDTHH:MI:SS')

CodePudding user response：

If you just want to convert the string to a Postgres timestamp then:

select '2011-04-26T05:04:11Z'::timestamptz; 
04/25/2011 22:04:11 PDT

The output will depend on the DateStyle setting.

To get your example to work then:

select to_TIMESTAMP('2011-04-26T5:04:11Z','YYYY-MM-DD"T"HH24:MI:SS');
      to_timestamp       
-------------------------
 04/26/2011 05:04:11 PDT

Note the "T" this causes it to be ignored as that seems to be what is causing the issues. Not certain, but probably related to Postgres ISO format using a space instead of T. Quoting characters to be ignored comes from Formatting function:

Tip

Prior to PostgreSQL 12, it was possible to skip arbitrary text in the input string using non-letter or non-digit characters. For example, to_timestamp('2000y6m1d', 'yyyy-MM-DD') used to work. Now you can only use letter characters for this purpose. For example, to_timestamp('2000y6m1d', 'yyyytMMtDDt') and to_timestamp('2000y6m1d', 'yyyy"y"MM"m"DD"d"') skip y, m, and d.

There is no provision for a time zone abbreviation in to_timestamp so the Z will be ignored and the timestamp will be in local time with the same time value. That is why I made my first suggestion using the timestamptz cast.

Two ways to deal with time zone:

One:

select to_TIMESTAMP('2011-04-26T5:04:11Z','YYYY-MM-DD"T"HH24:MI:SS')::timestamp AT time zone 'UTC';
        timezone         
-------------------------
 04/25/2011 22:04:11 PDT

Two:

select to_TIMESTAMP('2011-04-26T5:04:11 00','YYYY-MM-DD"T"HH24:MI:SS TZH');
      to_timestamp       
-------------------------
 04/25/2011 22:04:11 PDT