35

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
Improve this question
3
  • 1
    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. Commented Mar 28, 2017 at 1:38
  • Added all the errors. please check it. Thanks. Commented Mar 28, 2017 at 2:28
  • Read the error message and your code: USING create_time::timestamp without time zone vs. your code: create_time::without time zone type TIMESTAMP; Commented Mar 28, 2017 at 5:55

4 Answers 4

Reset to default
50

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi Leo. It is a right approach with setting "set datestyle = "ISO, DMY". Thank you so much!!
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..."
Did you by any chance export the table along with the additional holder column then re-import after the holder column was dropped?
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.
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.
|
15

USING... comes after the type:

... alter create_time type TIMESTAMP USING create_time::TIMESTAMP;
Improve this answer

3 Comments

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.
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;"
@Amy , set datestyle to 'sql,dmy'
5

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;
Improve this answer

2 Comments

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.
I've posted my comments as another answer — comments section is too limited for displaying multi-line code.
0

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);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.