Unable to change my time column from string to time in Bigquery

Question

I have a column that I made as HH:MM:SS in Excel, but when I moved it to Bigquery, it kept the format but changed to string. I need to change it back so that I can find averages.

At first I just tried altering the table:

ALTER TABLE `circular-nova-330422.trip_data_Cyclist.trip_data-12mths`
ALTER COLUMN ride_length SET DATA TYPE TIME;

But got this error:

ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (STRING) is assignable to the new type (TIME)

I found a question on here saying I should use parse_time, but when I do that, I just get this:

SELECT PARSE_TIME("%H:%M:%S", ride_length)
FROM `circular-nova-330422.trip_data_Cyclist.trip_data-12mths`;

Failed to parse input string"

What am I doing wrong, is there another way to permanently change the data type?

Answer 1

This will parse any time stamp between 00:00:00 to 23:59:59

is this the format your ride_length values come in as?

also are there any rows that may have empty string as the value?

SELECT PARSE_TIME("%H:%M:%S", "") would result in the error you posted above.

to resolve this you could try

SELECT PARSE_TIME("%H:%M:%S", nullif(ride_length, ""))
FROM `circular-nova-330422.trip_data_Cyclist.trip_data-12mths`;