Добавление двоеточия после двух чисел с использованием стандартного SQL

Я пытаюсь выполнить запрос в Big-Query с использованием стандартного SQL.

В моей таблице есть столбец, в котором время представлено в следующем формате: 170519

Что я хочу сделать, это добавить двоеточие (:) после каждых двух чисел, чтобы получить такие результаты: 17:05:19

Как мне это сделать?

Я искал, но не смог найти решение этой проблемы. Может я не ищу с правильными словами?

Спасибо за помощь!

РЕДАКТИРОВАТЬ:

Таким образом, ответ @Gordon Linoff работает для одного столбца.

Теперь, как я могу использовать это при объединении двух столбцов?

Вот мой код, где я получаю синтаксическую ошибку:

SELECT
  CONCAT(FORMAT_DATE("%Y-%m-%d", PARSE_DATE('%d%m%Y',
        Date1)), '  ',(PARSE_TIME("%H%M%S",
        Time1)) AS Date_Time
  FROM
Источник

4 ответа

Решение

Ниже приведен пример для BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '09052019' date1, '170519' time1 UNION ALL
  SELECT '10052019', '091020'
)
SELECT PARSE_DATETIME('%d%m%Y%H%M%S', CONCAT(date1, time1)) date_time,
  CAST(PARSE_DATETIME('%d%m%Y%H%M%S', CONCAT(date1, time1)) AS STRING) date_time_as_string
FROM `project.dataset.table`

с результатом

Row     date_time               date_time_as_string  
1       2019-05-09T17:05:19     2019-05-09 17:05:19  
2       2019-05-10T09:10:20     2019-05-10 09:10:20
Источник

Вот пример, который вы можете попробовать (скопируйте и вставьте его в редактор в https://console.cloud.google.com/bigquery):

CREATE TEMP FUNCTION InsertColons(s STRING) AS (
  CONCAT(SUBSTR(s, 1, 2), ':', SUBSTR(s, 3, 2), ':', SUBSTR(s, 5, 2))
);

SELECT s, InsertColons(s) AS result
FROM (
  SELECT '123456' AS s UNION ALL
  SELECT '170519'
);

SUBSTR Функция выбирает части строки, например, первые два символа с порядковым номером 1, затем первые два символа с порядковым номером 3, затем с порядковым номером 5, перед использованием CONCAT объединить части.

Источник

Вы могли бы сделать что-то вроде этого:

SELECT LEFT(c, 2) + ':' + SUBSTRING(c, 3, 2) + ':' + SUBSTRING(c, 5, 2)
FROM
    -- Use your own table in the FROM statement instead of this fake one
    (SELECT '170519' as c ) as q1

Возвращает 17:05:19

или с вашими данными:

SELECT LEFT(timo, 2) + ':' + SUBSTRING(timo, 3, 2) + ':' + SUBSTRING(timo, 5, 2)
FROM
    table1
Источник

Я бы превратил это в time тип данных, а не строка:

select parse_time('%H%M%S', '170519')

Если это начинается как число:

select parse_time('%H%M%S', cast(170519 as string))

Если вы хотите отформатировать это как строку, вы можете отформатировать время. Например, следующее поместит это в формат ЧЧ: ММ:

select format_time('%H:%M', parse_time('%H%M%S', cast(170519 as string)))

Я фанат хранения вещей в правильном типе.

Источник 
SELECT  CONCAT(SUBSTRING('170519', 1, 2), ':', SUBSTRING('170519',3, 2), ':', SUBSTRING('170519',5, 2))

это дает вам правильный ответ, например 17:05:19

Источник
Другие вопросы по тегам