CODEWITHSUNDEEP
sqlgoogle-bigquery
Adiansyah
Adiansyah

Reputation: 353

Bigquery Time as duration, convert to second

i have a problem with time from string

overall, my goal is want to get how many seconds from duration AS string that containing HH:MM:SS

00:01:00 -> 60
00:02:30 -> 150
01:00:00 -> 3600

my query is like this

TIME_DIFF(CAST(duration AS TIME), '00:00:00', SECOND)

However, query above does not work with duration that more than 24:00:00 is there any different solution to get

Upvotes: 1

Views: 7256

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

I assume the duration column is a string representing time in following format HHHH:MM:SS where HHHH can be any value of hours even above 24. If this is a case - consider below solution

select duration, 
  3600 * cast(regexp_extract(duration, r'^\d*') as int64) +
  time_diff(cast(regexp_replace(duration, r'^\d*', '00') as time), '00:00:00', second) as seconds
from `project.dataset.table`

below is example of output

enter image description here

Upvotes: 1

Vibhor Gupta
Vibhor Gupta

Reputation: 699

You can try out an alternative way to parse TIMESTAMP DATA:-

WITH DATA AS(
SELECT '2019-05-03T00:01:00' AS F_DATE 
UNION ALL
SELECT '2019-05-03T00:02:30'
UNION ALL
SELECT '2019-05-03T01:00:30'
)
select F_DATE, EXTRACT(HOUR FROM CAST( F_DATE AS TIMESTAMP))  * 60 *60 + EXTRACT(MINUTE FROM CAST(F_DATE AS TIMESTAMP)) * 60 + EXTRACT(SECOND FROM CAST(F_DATE AS TIMESTAMP)) AS DATE_SEC FROM DATA

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want to split "time" values longer than 24 hours, then I must assume that your source data is a string and not a time. In BigQuery, time values must be less than 24 hours.

So, use string operations:

select (cast(split(hhmmss, ':')[ordinal(1)] as int64) * 60 * 60 +
        cast(split(hhmmss, ':')[ordinal(2)] as int64) * 60 +
        cast(split(hhmmss, ':')[ordinal(3)] as int64)
       ) as seconds
from (select '00:01:00' as hhmmss union all 
      select '00:02:30' union all 
      select '01:00:00') x

Upvotes: 0

Related Questions