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.
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
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
