Работа с функциями даты и времени в PostgreSQL

Работа с расчетами даты и времени является неотъемлемой частью любого решения Data Analytics. Будь то финансовые данные или любые данные, связанные с продажами, даты всегда играют ключевую роль в анализе тенденций и, следовательно, выводят из них закономерность. Некоторые из наиболее часто используемых KPI, которые включают расчет даты и времени, - это ежемесячные продажи , ежеквартальный веб-трафик, годовой рост и т. д. Эти KPI помогают компаниям понять и измерить свою эффективность, а также помогают принимать решения, необходимые для их развития. В этой статье мы собираемся изучить некоторые важные функции даты и времени, которые можно использовать в PostgreSQL, и способы их реализации в дальнейшем.

PostgreSQL - одна из ведущих систем реляционных баз данных с открытым исходным кодом, которую можно использовать для создания операционных баз данных, а также хранилищ данных. PostgreSQL по умолчанию предоставляет ряд различных функций даты и времени, которые можно использовать для расчета таких ключевых показателей эффективности. Кроме того, мы можем рассчитывать временные интервалы с помощью этих функций, которые помогут нам проанализировать, что изменилось между этими двумя таймфреймами. В этой статье мы сосредоточимся на следующих концепциях.

• Извлечение части даты из существующих меток времени
• Использование функции INTERVAL в PostgreSQL
• Использование функции CAST в PostgreSQL для преобразования меток времени в другие форматы

Извлечь части даты из существующих меток времени

Работая над большим проектом базы данных, мы часто сталкиваемся с несколькими столбцами, в которых данные хранятся в формате временной метки. Эти столбцы могут быть с информацией о часовом поясе или без нее. Иногда требуется, чтобы мы не использовали всю метку времени для анализа, а просто использовали ее часть, например, день или час. Давайте сначала разберемся, как выглядит столбец с отметкой времени в PostgreSQL.

Как вы можете видеть на рисунке выше, я использовал функцию now() в PostgreSQL, которая возвращает текущее время системы. Поскольку мой коллега находится в Дублине, и его часовой пояс - UTC + 1 (летнее время). Давайте сначала разберемся с анатомией метки времени и разберем метку времени, чтобы выделить каждую из частей в разные столбцы.

• 2021 г. - ГОД - Извлекает часть года из метки времени.
• 07 - МЕСЯЦ - Извлекает месяц из метки времени.
• 28 год - ДЕНЬ - Извлекает день из метки времени.
• 04 - ЧАС - Извлекает час из отметки времени.
• 39 - МИНУТА - Извлекает минуты из отметки времени.
• 39 - СЕКУНДЫ - Извлекает секунды из метки времени.
• 247007 - МИЛЛИСЕКУНД - Извлекает миллисекунды из отметки времени.

Как поясняется в приведенной выше таблице, метку времени можно разбить на эти части. Теперь, чтобы извлечь и распечатать каждую из этих частей по отдельности, мы можем использовать функцию EXTRACT в PostgreSQL. Синтаксис функции EXTRACT следующий.

Давайте теперь попробуем извлечь значения даты из метки времени. Это можно сделать следующим образом.

Как вы можете видеть на рисунке выше, мы извлекли год, месяц и дату из часового пояса. Эти значения теперь доступны в виде отдельных столбцов. Эти столбцы можно использовать для выполнения различных расчетов. Затем мы также можем извлечь значения времени из метки времени. Это можно сделать, используя следующее.

На приведенном выше рисунке значения часа, минуты и секунды были извлечены из метки времени, и их можно использовать как части расчета, в которых используются дата и время. Помимо этого, функция EXTRACT также предоставляет некоторые другие методы для извлечения дополнительных частей даты из временных меток. Это следующие.

• • WEEK (НЕДЕЛЯ) - Извлекает неделю из даты.
  • DOW - Возвращает день недели из даты.
  • DOY - Возвращает день года из даты.
  • EPOCH (ЭПОХА) - Возвращает отметку времени UNIX. Вычислено 01.01.1970.

• QUARTER (ЧЕТВЕРТЬ) - Извлекает четверть из даты.
• TIMEZONE (ЧАСОВОЙ ПОЯС) - Извлекает информацию о часовом поясе.
• TIMEZONE_HOUR - Возвращает разницу часовых поясов в часах.
• TIMEZONE_MINUTE - Возвращает разницу часовых поясов в минутах.

Как вы можете видеть на рисунках выше, я использовал большинство часто используемых частей даты, которые используются с функцией EXTRACT в PostgreSQL.

Использование функции INTERVAL в PostgreSQL

При работе с вычислениями даты и времени в PostgreSQL иногда нам нужно вычислить продолжительность между двумя заданными временными метками. Это может быть что угодно, например, разница между днями в двух метках времени или разница между часами в двух метках времени. Такие расчеты важны в цепочках логистики или доставки продуктов питания, где клиентам может потребоваться рассчитать время, необходимое для доставки товаров или услуг. Давайте теперь посмотрим, как мы можем вычислить разницу между двумя отметками времени, а также добавить время к существующей отметке времени. Предположим, у нас есть две отметки времени, как показано ниже.

• Order_Date = 2021-02-28 20:15:21.298284+01
• Delivery_Date = 2021-03-10 10:39:11.943206+01

В первом сценарии мы хотели бы рассчитать время в днях, прошедшее с момента размещения заказа до его доставки. Это можно сделать в PostgreSQL с помощью функции AGE(). Эта функция принимает в качестве аргументов две метки времени и затем возвращает интервал между ними. Давайте теперь посмотрим, как это работает.

Видно, что разница между метками времени составляет около 9,5 дней. Точно так же мы можем использовать функцию INTERVAL, чтобы добавить время к существующей метке времени и вернуть новую метку времени. Это полезно, если мы хотим установить дату через фиксированный период времени. Например, при размещении заказа нам может потребоваться рассчитать дату доставки на основе интервала времени доставки. Это можно сделать следующим образом.

Как видно на рисунке выше, я рассчитал дату доставки, добавив 2 дня к дате заказа. Это простой способ добавить дни, часы или минуты и т. Д. К существующей метке времени.

Использование функции CAST в PostgreSQL

Функция CAST в PostgreSQL используется для преобразования значений из одного формата в другой. Обычно это делается для возврата фиксированного типа данных для поля. Здесь важно отметить, что для использования функции CAST возвращаемые значения должны быть совместимы с типом данных, указанным в функции CAST. Синтаксис функции CAST следующий.

Мы можем использовать любое из выражений для использования функции CAST в PostgreSQL. Давайте теперь посмотрим, как это работает.

Как вы можете видеть на приведенном выше рисунке, типы данных, возвращаемые двумя столбцами, - это дата и временная метка соответственно. Однако выражение даты, которое мы предоставили функции CAST, остается прежним. Таким образом, вы можете использовать функцию CAST для преобразования одного типа данных в другой.

Заключение

В этой статье мы рассмотрели несколько важных функций даты и времени в PostgreSQL. При построении любой современной модели данных чрезвычайно важно реализовать правильное измерение даты, которое можно полностью использовать для анализа данных. Использование этих функций даты и времени значительно упростит процесс анализа данных, так как вы можете легко перейти к анализу даты и внести необходимые изменения по мере необходимости.  С вами был Иван Сёмин автор и создатель IT портала Pyatilistnik.org.