CODEWITHSUNDEEP
sqlpostgresql
peter.petrov
peter.petrov

Reputation: 39477

Parse string with `T` to timestamp PostgreSQL

I have this string 2019-02-14T17:49:20.987 which I want to parse into a timestamp. So I am playing with the to_timestamp function and it seems to work fine except... The problem is with this T letter there. How do I make PostgreSQL skip it?
What pattern should I use in to_timestamp?

Of course I can replace the T with a space and then parse it but I find this approach too clumsy.

Upvotes: 5

Views: 3870

Answers (2)

klin
klin

Reputation: 121794

The string with T is a valid input literal for timestamp or timestamptz:

select '2019-02-14T17:49:20.987'::timestamp;

        timestamp        
-------------------------
 2019-02-14 17:49:20.987
(1 row)

Upvotes: 3

user330315
user330315

Reputation:

Quote from the manual

If there are characters in the template string that are not template patterns, the corresponding characters in the input data string are simply skipped over (whether or not they are equal to the template string characters).

So just put any non-template character there (e.g. X):

select to_timestamp('2019-02-14T17:49:20.987', 'YYYY-MM-DDXHH24:MI:SS.MS')

Online example: https://rextester.com/OHYD18205

Alternatively, you can simply cast the value:

select '2019-02-14T17:49:20.987'::timestamp

Upvotes: 10

Related Questions