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

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. — Andy Carlson, Mar 28 '17 at 01:38
Read the error message and your code: `USING create_time::timestamp without time zone` vs. your code: `create_time::without time zone type TIMESTAMP;` — , Mar 28 '17 at 05:55

score 42 · Accepted Answer · answered Mar 29 '17 at 02:25

42

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;

answered Mar 29 '17 at 02:25

Leo C

22,006
3
26
39

Hi Leo. It is a right approach with setting "set datestyle = "ISO, DMY". Thank you so much!! – Amy Mar 29 '17 at 18:54
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,,29/03/2017 14:57:55,30/03/2017 03:20:02,,i..." – Amy Mar 29 '17 at 20:21
Did you by any chance export the table along with the additional holder column then re-import after the holder column was dropped? – Leo C Mar 29 '17 at 21:46
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. – Amy Mar 30 '17 at 21:06
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](http://www.tldp.org/LDP/abs/html/awk.html), [sed](http://www.tldp.org/LDP/abs/html/x23170.html), etc. – Leo C Mar 30 '17 at 21:51
Thank you. I will think about it. – Amy Mar 31 '17 at 22:12

Jasen · Answer 2 · 2017-04-06T11:04:00.800

14

USING... comes after the type:

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

edited Apr 06 '17 at 11:04

answered Mar 28 '17 at 06:22

Jasen

11,837
2
30
48

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 Mar 28 '17 at 23:06
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;" – Sukma Saputra Oct 20 '18 at 23:18
@Amy , `set datestyle to 'sql,dmy'` – Jasen Oct 21 '18 at 19:34

score 4 · Answer 3 · answered Mar 28 '17 at 06:22

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;

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. — Amy, Mar 29 '17 at 00:51
I've posted my comments as another answer — comments section is too limited for displaying multi-line code. — Leo C, Mar 29 '17 at 02:27

score 0 · Answer 4 · edited Aug 29 '17 at 10:29

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);

postgreSQL alter column data type to timestamp without time zone

4 Answers4

Linked