CODEWITHSUNDEEP
sqlpostgresqltimestamp
Amy
Amy

Reputation: 453

postgreSQL alter column data type to timestamp without time zone

I want to alter one column of data from text into type timestamp. There is no time zone in my data. The format of my data is like 28-03-17 17:22, including time and date but no time zone. In other words, all my data are in the same time zone. How can I do it?

I tried multiple ways below, but I still can not find the right approach. Hope you can help me.

Certainly, I can build a new table if my trouble can be solved.

alter table AB
alter create_time type TIMESTAMP;

ERROR:  column "create_time" cannot be cast automatically to type timestamp without time zone
HINT:  You might need to specify "USING create_time::timestamp without time zone".
********** Error **********

ERROR: column "create_time" cannot be cast automatically to type timestamp without time zone
SQL state: 42804
Hint: You might need to specify "USING create_time::timestamp without time zone".

alter table AB
alter create_time type TIMESTAMP without time zone;

ERROR:  column "create_time" cannot be cast automatically to type timestamp without time zone
HINT:  You might need to specify "USING create_time::timestamp without time zone".
********** Error **********

ERROR: column "create_time" cannot be cast automatically to type timestamp without time zone
SQL state: 42804
Hint: You might need to specify "USING create_time::timestamp without time zone".

alter table AB
alter create_time::without time zone type TIMESTAMP;

ERROR:  syntax error at or near "::"
LINE 2:  alter create_time::without time zone type TIMESTAM
                          ^
********** Error **********

ERROR: syntax error at or near "::"
SQL state: 42601
Character: 50

alter table AB
alter create_time UTC type TIMESTAMP;

ERROR:  syntax error at or near "UTC"
LINE 2: alter create_time UTC type TIMESTAMP;
                          ^
********** Error **********

ERROR: syntax error at or near "UTC"
SQL state: 42601
Character: 50

Upvotes: 34

Views: 118317

Answers (4)

raman.pndy
raman.pndy

Reputation: 117

for changing the data type of column from bigint to timestamp(for epoch to timestamp)

alter table <tablename> alter column <columnname> type timestamp without time zone using to_timestamp(<columnname>) AT TIME ZONE 'UTC';

for eg; 

alter table AB alter column col type timestamp without time zone using to_timestamp(col) AT TIME ZONE 'UTC';

for changing the data type of column from timestamp to bigint(for timestamp to epoch)

alter table <tablename> alter column <columnname> type bigint using extract(EPOCH from <columnname>);

for eg; 

alter table AB alter column col type bigint using extract(EPOCH from col);

Upvotes: 0

Jasen
Jasen

Reputation: 12412

USING... comes after the type:

... alter create_time type TIMESTAMP USING create_time::TIMESTAMP;

Upvotes: 15

Leo C
Leo C

Reputation: 22439

If create_time is of type TEXT with valid date value, it'll be easier to proceed with the change as follows (Be advised to first do a table dump as a backup):

-- Create a temporary TIMESTAMP column
ALTER TABLE AB ADD COLUMN create_time_holder TIMESTAMP without time zone NULL;

-- Copy casted value over to the temporary column
UPDATE AB SET create_time_holder = create_time::TIMESTAMP;

-- Modify original column using the temporary column
ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING create_time_holder;

-- Drop the temporary column (after examining altered column values)
ALTER TABLE AB DROP COLUMN create_time_holder;

Upvotes: 50

Leo C
Leo C

Reputation: 22439

You didn't specify the original type of create_time, so I assume it's TIME with time zone (as type DATE or TIMESTAMP with time zone shouldn't give the said error when trying to alter to TIMESTAMP without time zone). Since TIMESTAMP has date information in addition to TIME, you'll need to supplement your date information in your ALTER statement, like:

ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING date('20170327') + create_time;

If you have a corresponding DATE column (say, create_date), you can pass it to the date() function, like:

ALTER TABLE AB ALTER COLUMN create_time TYPE TIMESTAMP without time zone USING date(create_date) + create_time;

Upvotes: 5

Related Questions