Блог

Главная - Вопросы по программированию - Преобразование секунд в дни, часы, минуты в Bigquery

Преобразование секунд в дни, часы, минуты в Bigquery

#sql #google-bigquery #type-conversion

#sql #google-bigquery #тип-преобразование

Вопрос:

у меня возникли проблемы с преобразованием секунд в Bigquery, есть ли какая-либо функция для преобразования секунд в формат hour: minute: second в Bigquery? я уже пробовал эту TIMESTAMP_SECONDS() функцию, но она также возвращает некоторую дату, и я не могу ее использовать, если час больше 23. например:

секунда = 100000

результат= 27:46:40

или, может быть, как 1 день 3 часа 46 минут 40 секунд

и я также хочу, чтобы это было в типе данных метки времени, чтобы я мог упорядочить его по возрастанию или убыванию.

Ответ №1:

Ниже приведен стандартный SQL для BigQuery

 #standardSQL
select seconds, 
  regexp_replace(
    cast(time(ts) as string), 
    r'^dd', 
    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

если применить к образцу данных из вашего вопроса, как в

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

вывод

введите описание изображения здесь

Комментарии:

1. вау! большое вам спасибо! но он не может быть упорядочен по asc или desc, поскольку он преобразуется в строку, не так ли?

2. вам все равно нужно использовать исходные файлы (в данном случае seconds ) для выполнения любого необходимого вам порядка. очевидно, что вывод в том виде, в каком он есть, не может быть использован для упорядочения — если вы не нормализуете его, скажем, имея все компоненты из двух цифр и первый компонент (часы в option1 и дни в option2), скажем, из 4 цифр — зависит от ожидаемых чисел

Ответ №2:

С недавно введенным ИНТЕРВАЛЬНЫМ типом данных и соответствующими функциями — такое преобразование становится намного проще

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

с выводом типа

введите описание изображения здесь