Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
add comment (requires an account with 50 reputation)

1 Answer

up vote 0 down vote accepted

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
share|improve this answer
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). – user2631089 17 hours ago
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.