Postgresql – Change column type from character varying to timestamp without time zone in PostgreSQL

datetimepostgresqlruby-on-rails-3types

In rails I created a string column called open_time but then I realized I should use the datetime type. I did

change_column :polls, :open_time, :datetime

But it said:

PG::Error: ERROR:  column "open_time" cannot be cast to type timestamp without time zone
: ALTER TABLE "polls" ALTER COLUMN "open_time" TYPE timestamp

If I just drop the string column and add new datetime column, I will lose the data stored in the string column. Alternatively, in PostgreSQL I add column:

 ALTER TABLE polls ADD COLUMN published_time timestamp;

Then I tried to get the data from the string column like:

UPDATE polls SET published_time = strToTimeStamp(open_time);

So my question is are there any functions I can use as strToTimeStamp that can convert character varying type to timestamp without time zone type?

Best Answer

.. are there any functions I can use as strToTimeStamp that can convert character varying type to timestamp without time zone type?

Use to_timestamp() to alter the data type of the column in place.

ALTER TABLE tbl ALTER COLUMN col TYPE timestamp
USING to_timestamp(col, '<your pattern here>');

More under these very similar questions:
Alter character field to date
Cast varchar type to date

Related Solutions

Postgresql – Cannot create column with type “TIMESTAMP WITHOUT TIME ZONE” in PostgreSQL

Instead of using the tag and switch completely from XML to sql, you could modify the resulting generated SQL using the tag which is valid across the whole changeset: http://www.liquibase.org/documentation/modify_sql.html

for example in your case you would have this:

<modifySql dbms="postgresql">
    <replace replace="WITH" with="WITHOUT"/>
</modifySql>

Python – How to make a timezone aware datetime object in Python

In general, to make a naive datetime timezone-aware, use the localize method:

import datetime
import pytz

unaware = datetime.datetime(2011, 8, 15, 8, 15, 12, 0)
aware = datetime.datetime(2011, 8, 15, 8, 15, 12, 0, pytz.UTC)

now_aware = pytz.utc.localize(unaware)
assert aware == now_aware

For the UTC timezone, it is not really necessary to use localize since there is no daylight savings time calculation to handle:

now_aware = unaware.replace(tzinfo=pytz.UTC)

works. (.replace returns a new datetime; it does not modify unaware.)

Best Answer

Related Solutions

Postgresql – Cannot create column with type “TIMESTAMP WITHOUT TIME ZONE” in PostgreSQL

Python – How to make a timezone aware datetime object in Python

Related Topic