CODEWITHSUNDEEP
postgresql
Prashant
Prashant

Reputation: 61

Convert text to UTC timestamp

I have timestamp in text format in my table. It's of the following format 2019-05-30T05:48:29.095705642+01:00. I want to convert this into UTC timestamp.

I am thinking of first converting text into timestamp and then converting it into UTC. I am executing SELECT TO_TIMESTAMP('2019-05-30T05:48:29.095705642+01:00', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') but it doesn't work. I'm unable to figure it out.

Upvotes: 1

Views: 748

Answers (1)

S-Man
S-Man

Reputation: 23686

For Milliseconds (.MS) you only can take 3 decimal digits. If you would use .US instead, you are able to cast 6 digits.

But if you do not want to strip the digits in an separate operation (and if you are sure that there are always 3 digits more) you can ignore them by adding the 9 pattern (US999). The result would be the same as use stripped it before.

documentation (table 9.26)

SELECT 
    TO_TIMESTAMP(
        '2019-05-30T05:48:29.095705642+01:00', 
        'YYYY-MM-DD"T"HH24:MI:SS.US999"Z"'
     )

But the most simple way - if you really have this date format - is the direct cast into timestamptz. PostgreSQL does the magic for you and there is no need to consider the number of decimal digits:

SELECT '2019-05-30T05:48:29.095705642+01:00'::timestamptz

After the cast from text to timestamp you can switch the timezone with at time zone 'UTC':

SELECT '2019-05-30T05:48:29.095705642+01:00'::timestamptz at time zone 'UTC'

demo:db<>fiddle

As stated in the comments: The time zone is not considered correctly. "Z" only ignored the letter Z. To parse the time zone you would need to write TZH:TZM. Another reason why you simply should use the PostgreSQL magic.

Upvotes: 1

Related Questions