PostgreSQL Question posted in
The official documentation can be found here.

How to convert string to datetime in PostgreSQL

I have a column named cancel_date in my table, which stores date in varchar datatype and have some empty rows:

id cancel_date
111 2024-03-27T11:27:20.215419+00:00
222 NULL
333 2024-02-23T22:02:41.671511+00:00
444 NULL

So, i need to transform date values from cancel_date to TIMESTAMP format. i use TO_TIMESTAMP(cancel_date, 'YYYY-MM-DD"T"HH24:MI:SS.US+TZ'), but got this error: Failed to tokenize string [Y] at offset [0]
How to deal with Null values using TO_TIMESTAMP()?

UPD also tried to cut msc by SUBSTRING() and got same error – Failed to tokenize string [Y] at offset [0]

select
id,
cancel_date,
SUBSTRING(cancel_date FROM 1 FOR 19),
CASE 
    WHEN cancel_date IS NOT NULL then
        TO_TIMESTAMP(SUBSTRING(cancel_date FROM 1 FOR 19),'YYYY-MM-DD"T"HH24:MI:SS')
    ELSE NULL 
END AS formatted_timestamp
from mytable
Tags:

2

Answers


  1. 0

    Since you are working with dates/timestamps in ISO8601 format, all you have to do is cast the string; it works like a charm.

    SELECT id, cancel_date::timestamptz
FROM (
    VALUES
    (111, '2024-03-27T11:27:20.215419+00:00'),
    (222, NULL),
    (333, '2024-02-23T22:02:41.671511+00:00'),
    (444, NULL)
) T(id, cancel_date)

    Alternatively, the TO_TIMESTAMP pattern you can apply is like so:

    SELECT id, TO_TIMESTAMP(cancel_date, 'YYYY-MM-DD"T"HH24:MI:SS.USTZH:TZM')
FROM ...

    This is documented in the Postgres documentation. As is written there:

    • TZ only works in TO_CHAR, therefore you need to use TZH and TZM.
    • TZH can match a signed number, so no need to put a + or - before it (and in fact, you must not put one).

    BTW, both solutions work for NULL values.

    Login or Signup to reply.
  2. 0

    I am afraud that the value NULL is a text value rather than null. So here it is:

    select id, nullif(cancel_date, 'NULL')::timestamptz
from the_table;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search