Google BigQuery Datetime Formats.

Что такое BigQuery?

BigQuery-это полностью управляемое бессерверное хранилище данных, которое позволяет масштабировать анализ по петабайтам данных. Это платформа как служба, которая поддерживает запросы с использованием ANSI SQL. Он также имеет встроенные возможности машинного обучения. BigQuery был анонсирован в мае 2010 года и стал общедоступным в ноябре 2011 года.

Позвольте мне сформулировать это в моем собственном полевом смысле … это аналитика..

BigQuery-это облачное хранилище данных, которое позволяет выполнять сверхбыстрые запросы к большим наборам данных.

Вы можете экспортировать данные сеанса и посещений из учетной записи Google Analytics 360 в BigQuery, а затем использовать синтаксис, подобный SQL, для запроса всех ваших аналитических данных.

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

Обработка форматов даты и времени в BigQuery

Итак, теперь давайте приступим к делу. За все время работы с Bigquery я обнаружил, что преобразовать строку в дату и время в большом запросе или преобразовать строку в формат даты непросто, поскольку для всех ваших потребностей не существует только одной формулы.

Давайте начнем со стандартных функций:

DATETIME

1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression[, time_expression])
3. DATETIME(timestamp_expression [, timezone])

Описание

1. Создает DATETIME объект, используя значения INT64, представляющие год, месяц, день, час, минуту и секунду.
2. Создает DATETIME объект, используя объект ДАТЫ и необязательный объект ВРЕМЕНИ.
3. Создает DATETIME объект с использованием объекта МЕТКИ ВРЕМЕНИ. Он поддерживает необязательный параметр для указания часового пояса. Если часовой пояс не указан, используется часовой пояс по умолчанию-UTC.

FORMAT_DATETIME

FORMAT_DATETIME(format_string, datetime_expression)

Описание

Форматирует DATETIME объект в соответствии с указанным format_string.

Возвращаемый Тип Данных

STRING

Примеры

SELECT
 FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
 AS formatted;

+--------------------------+
| formatted |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+

SELECT
 FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
 AS formatted;

+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+

SELECT
 FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
 AS formatted;

+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+

PARSE_DATETIME

PARSE_DATETIME(format_string, datetime_string)

Описание

Преобразует строковое представление даты-времени в DATETIMEобъект.

format_string содержит элементы формата, определяющие способ datetime_string форматирования. Каждый элемент in datetime_string должен иметь соответствующий элемент in format_string. Расположение каждого элемента в format_string должно соответствовать расположению каждого элемента в datetime_string.

-- This works because elements on both sides match.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATETIME("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008")

-- This works because %c can find all matching elements in datetime_string.
SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008")

Строка формата полностью поддерживает большинство элементов формата, за исключением: %a, %A, %g, %G, %j, %P, %u, %U, %V, %w, и %W…

PARSE_DATETIME анализирует string в соответствии со следующими правилами:

• Неуказанные поля. Любое неуказанное поле инициализируется из 1970-01-01 00:00:00.0. Например, если год не указан, то по умолчанию 1970он равен .
• Нечувствительность к регистру. Имена, такие как Mondayи February, не учитывают регистр.
• Пробелы. Один или несколько последовательных пробелов в строке формата совпадают с нулем или более последовательными пробелами в DATETIMEстроке. Начальные и конечные пробелы в DATETIMEстроке всегда разрешены, даже если они не указаны в строке формата.
• Приоритет формата. Когда два или более элементов формата содержат перекрывающуюся информацию, последний, как правило, переопределяет все предыдущие, за некоторыми исключениями. Например, оба %Fи %Yвлияют на год, поэтому более ранний элемент переопределяет более поздний. Смотрите описания %s, %C, и %yв Поддерживаемых Элементах формата Для ДАТЫи времени.
• Расхождение форматов. %p может использоваться с am, AM, pm, и PM.

Возвращаемый Тип Данных

DATETIME

Примеры

В следующих примерах STRING литерал анализируется как a DATETIME.

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') 
AS datetime;

+---------------------+
| datetime |
+---------------------+
| 1998-10-18T13:45:55 |
+---------------------+

SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 pm') 
AS datetime

+---------------------+
| datetime |
+---------------------+
| 2018-08-30T14:23:38 |
+---------------------+

Вышеперечисленных предопределенных функций было бы достаточно для выполнения базовых повседневных задач начального уровня в Bigquery, когда вы имеете дело с форматами даты и времени.