'Format change when transferring data from excel to Big Query

In excel, I have a column labeled ride_length which has data from 2 different columns that include timestamps (end time - start time).

Example values: 0:06:40, 1:48:08, 34:56:57

I formatted these cells as TIME 37:30:55

After uploading the data to Big Query, the data is formatted as STRING and not time.

What am I doing wrong?

sqlexcelgoogle-bigqueryformat


Solution 1:[1]

To calculate the duration in Excel to a value with the unit secounds, use this formula

=(A2-A3)*24*3600

BigQuery can parse a string to tranform it to a value. However, the time can be a maximum of 24 hours. Therefore, I would tranform the duration in a value with the unit secound.

Select A, 
#time(parse_timestamp("%H:%M:%S", A)) as time_h_less_24,
3600*cast(split(A,":")[offset(0)] as int64)+TIME_DIFF(time(parse_timestamp("%Y:%M:%S", A)),"0:0:0",SECOND)as duration_in_s,

TIMESTAMP_MILLIS(1000*3600*cast(split(A,":")[offset(0)] as int64)+TIME_DIFF(time(parse_timestamp("%Y:%M:%S", A)),"0:0:0",MILLISECOND))
from 
(Select "23:56:57" as A)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Samuel

Related Questions