postgreSQL alter column data type to timestamp without time zone

Question

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

Solution 1

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;

Solution 2

USING... comes after the type:

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

Solution 3

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;

Comments

[Andy Carlson]

As a general rule, please include any relevant error messages in your questions. If there is no error, mention why you believe it isn't working as expected. Saying it "isn't the right approach" is not very helpful or detailed.

[Amy]

Added all the errors. please check it. Thanks.

[a_horse_with_no_name]

Read the error message and your code: USING create_time::timestamp without time zone vs. your code: create_time::without time zone type TIMESTAMP;

[Amy]

ERROR: date/time field value out of range: "23/03/2017 05:20:02" HINT: Perhaps you need a different "datestyle" setting. ********** Error ********** ERROR: date/time field value out of range: "23/03/2017 05:20:02" SQL state: 22008 Hint: Perhaps you need a different "datestyle" setting.

[Amy]

Thank you for your answer. The data type I set was text at the beginning sine the data is like '28-03-17 17:22', including both time and date but no time zone.

[Leo C]

I've posted my comments as another answer — comments section is too limited for displaying multi-line code.

[Amy]

Hi Leo. It is a right approach with setting "set datestyle = "ISO, DMY". Thank you so much!!

[Amy]

After changing the format, all my existing data is good. But the problem is that when I was trying to import data with the same previous format, it showed me error. I set delimiter with ','. ERROR: extra data after last expected column CONTEXT: COPY ca_manifest_checkpoint, line 2: "Canada,YYZ01A,YYZ01A_20170329_151501.txt,953353795858,31,,2‌​9/03/2017 14:57:55,30/03/2017 03:20:02,,i..."

[Leo C]

Did you by any chance export the table along with the additional holder column then re-import after the holder column was dropped?

[Amy]

it is weird for me. In long term, I need to import new data to the table everyday. It is not convenient to do it in this approach every time.

[Leo C]

If this is a regular import task and you can't make your data source to conform to the new timestamp format, you might want to consider pre-processing the text data with some scripts using awk, sed, etc.

[Amy]

Thank you. I will think about it.

[Sukma Saputra]

This is my query and it's work perfectly on postgresql 10.5. "alter table tmp_usersgroups alter column datecreated type timestamp using datecreated::timestamp;"

[Jasen]

@Amy , set datestyle to 'sql,dmy'