Timestamp conversion in postgres SQL-CodePudding

Problem

How to convert 2022-06-14T12:47:00.560Z to YYYY-MM-DD HH:mm:ss (2022-06-14 12:47:00)

what I have tried is

select to_timestamp(to_char('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS'))
select to_timestamp('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS')

What issues I am getting

Error while connecting to PostgreSQL Execution failed on sql 'select to_timestamp(to_char('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS'))': TEIID30068 The function 'to_char('2022-06-14T13:04:00.610Z', 'YYYY-MM-DD HH24:MI:SS')' is an unknown form. Check that the function name and number of arguments is correct.

Error while connecting to PostgreSQL Execution failed on sql 'select to_timestamp('2022-06-14T13:04:00.610Z','YYYY-MM-DD HH24:MI:SS')': TEIID30068 The function 'to_timestamp('2022-06-14T13:04:00.610Z', 'YYYY-MM-DD HH24:MI:SS')' is an unknown form. Check that the function name and number of arguments is correct.

Please help me in converting.

CodePudding user response：

you can use built in timestamo conversion

SELECT timestamp '2022-06-14T13:04:00.610Z'

| timestamp              |
| :--------------------- |
| 2022-06-14 13:04:00.61 |

SELECT '2022-06-14T13:04:00.610Z'::timestamp

| timestamp              |
| :--------------------- |
| 2022-06-14 13:04:00.61 |

db<>fiddle here

You can use date_trunc

SELECT date_trunc('second',timestamp '2022-06-14T13:04:00.610Z')

| date_trunc          |
| :------------------ |
| 2022-06-14 13:04:00 |

SELECT date_trunc('second','2022-06-14T13:04:00.610Z'::timestamp)

| date_trunc          |
| :------------------ |
| 2022-06-14 13:04:00 |

db<>fiddle here

CodePudding user response：

You can do:

select to_timestamp('2022-06-14T13:04:00.610Z','YYYY-MM-DD"T"HH24:MI:SS')

See running example at DB Fiddle.

EDIT

I though you needed to parse the timestamp only. Now I realize you want to format it back to a string. You can add to_char() to format it as needed.

For example:

select
  to_char(
    to_timestamp('2022-06-14T13:04:00.610Z','YYYY-MM-DD"T"HH24:MI:SS'),
    'YYYY-MM-DD HH24:MI:SS'
  );

See updated fiddle.