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

Question

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

if possible, wouldn't be better to change both columns to timestamp? — Jim Jones
– Jim Jones, Commented Aug 10, 2022 at 15:07
see it yourself: SELECT '2022/08/03 13:29:59'::timestamp, '03/08/2022 13:29:59'::timestamp — Jim Jones
– Jim Jones, Commented Aug 10, 2022 at 15:09
I just added an answer with a fiddle that might make my case better. cheers — Jim Jones
– Jim Jones, Commented Aug 10, 2022 at 15:14

Jim Jones · Accepted Answer · 2022-08-10 15:12:07Z

2

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

answered Aug 10, 2022 at 15:12

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related