CODEWITHSUNDEEP
sqlgoogle-bigquerytype-conversion
yola
yola

Reputation: 67

Convert seconds to days, hours, minutes in Bigquery

i'm having trouble in converting seconds in Bigquery, is there any function to convert seconds to hour:minute:second format in Bigquery? i already tried the TIMESTAMP_SECONDS() function but it also returns some date and i can't use it if the hour more than 23. for example:

second= 100000

result= 27:46:40

or maybe as 1 day 3 hour 46 minute 40 second

and i also want it in timestamp datatype so i can order it ascending or descending.

Upvotes: 3

Views: 10968

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

With recently introduced INTERVAL data type and respective functions - such conversion becomes much easier

select seconds, 
  make_interval(second => seconds) result,
  justify_interval(make_interval(second => seconds)) normalized_result
from `project.dataset.table`

with output like

enter image description here

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL

#standardSQL
select seconds, 
  regexp_replace(
    cast(time(ts) as string), 
    r'^\d\d', 
    cast(extract(hour from time(ts)) + 24 * unix_date(date(ts)) as string)
  ) as option1, 
  format(
    '%i day %i hour %i minute %i second', 
    unix_date(date(ts)), 
    extract(hour from time(ts)), 
    extract(minute from time(ts)), 
    extract(second from time(ts))
  ) as option2
from `project.dataset.table`, 
unnest([timestamp_seconds(seconds)]) ts

if to apply to sample data from your question as in

with `project.dataset.table` AS (
  select 100000 seconds union all
  select 200000 union all
  select 300000
)

the output is

enter image description here

Upvotes: 5

Related Questions