I need to shift characters around in a character varying column in postgres

August 10, 2022

I have a column with date and timestamp stored as character varying, I need to join to another table that has the same dates (also stored as character varying) but in different positions.

IE is there a way to convert:

"2022/08/03 13:29:59" ----------> "03/08/2022 13:29:59"

Thanks

>Solution :

The best way would be to change the data type of both columns, as timestamps shouldn’t be stored as text. If not possible, a simple cast would do:

SELECT 
  '2022/08/03 13:29:59'::timestamp,
  '03/08/2022 13:29:59'::timestamp;

      timestamp      |      timestamp      
---------------------+---------------------
 2022-08-03 13:29:59 | 2022-08-03 13:29:59
(1 row)

Demo: db<>fiddle