PostgreSQL: replace string with datetime

Question

I have a varying character field (datestring) with a timestamp and I want to convert it to timestamp type. I can successfully create a new timestamp column (see below) and populate this column with a formatted version of the column datestring but I would rather not create a new field, I just want to replace the datestring field with a timestamp version of itself.

So this works fine:

ALTER TABLE service_areas ADD COLUMN newdate timestamp
update service_areas set newdate = to_timestamp(datestring, 'YYYY-MM-DD HH24:MI:SS.MS')

But I'd like to do something like:

update service_areas set datestring = to_timestamp(datestring, 'YYYY-MM-DD HH24:MI:SS.MS')

Suggestions?

Z

Pavel Stehule · Accepted Answer · 2013-07-29 16:48:19Z

1

Use a USING clause

postgres=# CREATE TABLE foo(a varchar);
CREATE TABLE
postgres=# INSERT INTO foo VALUES('2013-07-15');
INSERT 0 1
postgres=# ALTER TABLE foo ALTER COLUMN a TYPE date USING to_date(a,'YYYY-MM-DD');
ALTER TABLE

answered Jul 29, 2013 at 16:48

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

1 Comment

ZRoss Over a year ago

Worked perfectly! (I am new to the site and tried to mark this as the correct answer, let me know if I did not do it correctly).

Collectives™ on Stack Overflow

PostgreSQL: replace string with datetime

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related