Making column that has interval data type from a column of string in BIG QUERY

Question

I am struggling to figure out how to convert my column of string to interval data type. But my problem is that I only want the HOURS to SECONDS portion of it to be reflected on the table not the "Y-M D H:M:S". I needed it to be able to create my visualization properly in tableau. How do I just get the "H:M:S" to reflect on the table?

Here is my code with annotations it did give me the interval but I can't figure out how to just get the hours to seconds:

/updating a data type from string to interval/

/STEP 1- changing the column name to dump it later/ ALTER TABLE scenic-arc-373908.Cyclistic_Data.04_2022 RENAME COLUMN ride_length TO ride_length_to_dump

/STEP 2- add a new column name with the original name and required data type/ ALTER TABLE scenic-arc-373908.Cyclistic_Data.04_2022 ADD COLUMN ride_length INTERVAL

/STEP 3- drop the new column because I have to set the interval right/ ALTER TABLE scenic-arc-373908.Cyclistic_Data.04_2022 DROP COLUMN IF EXISTS ride_length

/STEP 4- updating the table to get all the info from ride_length_to_dump into ride_length/ UPDATE scenic-arc-373908.Cyclistic_Data.04_2022 SET ride_length = CAST(ride_length_to_dump AS INTERVAL 'H') WHERE ride_length_to_dump IS NOT NULL

Answer 1

import datetime

Example input string representing time interval

input_str = "3 days 12:30:45"

Parse the string and extract time portion

time_portion = datetime.datetime.strptime(input_str, "%d days %H:%M:%S").time()

print(time_portion)

12:30:45

sql :

SELECT SUBSTRING(time_interval_column, CHARINDEX(' ', time_interval_column) + 1, LEN(time_interval_column)) FROM your_table_name;

note: This SQL query will extract the time portion from your string column. Adjust the query as per your database system and column names.

Answer 2

Another option would be to use EXTRACT

SELECT EXTRACT(HOUR FROM DATETIME("2022-02-05 23:15:00")) as hour;

/*------------------*
 | hour             |
 +------------------+
 | 15               |
 *------------------*/