Convert any input timestamp ex (4/25/2021 20.01.42) to Correct Postgres TimeStamp (YYYY-MM-DD HH12:MI:SS)

Question

I always get random timestamp format input for a data/time field from a file. Irrespective of the input, i always want it get converted into correct Postgres timestamp format.

ex1: (4/25/2021 20.01.42) to Correct Postgres TimeStamp (YYYY-MM-DD HH12:MI:SS)

ex2: sometime the format we receive from file is (10/05/2022 09:17:23)

I'm getting error as

invalid date format or convert 24 hr clock to 12 hr for example-1 etc.

Can we write a generic conversion statement in Postgres?

user_15603477 · Answer

24h:

  select to_timestamp('4/25/2021 20.01.42','MM/DD/YYYY HH24:MI:SS');

12h:

select to_char( to_timestamp ('4/25/2021 20.01.42','MM/DD/YYYY HH24:MI:SS') , 'HH12:MI:SS PM' )

Further info: https://www.postgresql.org/docs/current/datetime-appendix.html

Convert any input timestamp ex (4/25/2021 20.01.42) to Correct Postgres TimeStamp (YYYY-MM-DD HH12:MI:SS)

1 Answers