Функция DATEADD в SQL для различных хранилищ данных

Я использовал функцию dateadd в SQL тысячи раз.

Я гуглил синтаксис функции dateadd в SQL все эти разы, кроме одного, когда решил нажать кнопку "Мне повезет" и попробовать.

При переключении между диалектами SQL (BigQuery, Postgres и Snowflake — мои основные), я буквально никогда не могу запомнить порядок аргументов (или точное название функции) dateadd.

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

Что такое функция DATEADD в SQL?

Функция DATEADD в SQL добавляет временной/датный интервал к дате и затем возвращает дату. Это позволяет вам добавлять или вычитать определенный период времени от заданной начальной даты.

Звучит достаточно просто, но эта функция позволяет делать довольно полезные вещи, такие как расчет предполагаемой даты доставки на основе даты заказа.

Различия в синтаксисе DATEADD на различных платформах хранилищ данных

Все они принимают примерно одинаковые параметры, но в немного различном синтаксисе и порядке:

• Начальная/исходная дата
• Часть даты (день, неделя, месяц, год)
• Интервал (целое число для увеличения)

Сами функции называются немного по-разному, что является обычным для диалектов SQL.

Например, функция DATEADD в Snowflake…

dateadd( {{ datepart }}, {{ interval }}, {{ from_date }} )

Час, минута и секунда поддерживаются!

Функция DATEADD в Databricks

date_add( {{ startDate }}, {{ numDays }} )

Функция DATEADD в BigQuery…

date_add( {{ from_date }}, INTERVAL {{ interval }} {{ datepart }} )

Части даты менее одного дня (час / минута / секунда) не поддерживаются.

Функция DATEADD в Postgres...

Postgres не предоставляет функцию dateadd из коробки, поэтому вам придется справляться самостоятельно - но синтаксис выглядит очень похоже на функцию BigQuery…

{{ from_date }} + (interval '{{ interval }} {{ datepart }}')

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

Поэтому я сделал эту удобную матрицу 2x2, чтобы помочь разобраться в различиях:

Извините - это просто пустая матрица 2x2. Я сдался и просто ищу документацию.

Стандартизация вашего синтаксиса DATEADD в SQL с помощью макроса dbt

Но разве мы не могли бы делать что-то лучше с этими нажатиями клавиш, например, печатать и затем удалять твит?

dbt (и пакет макросов dbt_utils) помогает нам сгладить эти шероховатости при написании SQL для хранилищ данных.

Вместо того чтобы каждый раз искать синтаксис, вы можете просто писать его одинаково каждый раз, и макрос скомпилирует его для выполнения в выбранном вами хранилище:

{{ dbt_utils.dateadd(datepart, interval, from_date_or_timestamp) }}

Добавление 1 месяца к сегодняшнему дню выглядело бы так...

{{ dbt_utils.dateadd(month, 1, '2021-08-12' }}

Новичок в dbt? Ознакомьтесь с введением в dbt для получения дополнительной информации о dbt и рабочем процессе аналитической инженерии, который он облегчает.

Кратко: dbt позволяет специалистам по данным писать код как инженеры-программисты, что в данном случае означает не повторять себя без необходимости.

Компиляция ваших проблем с DATEADD

Когда мы запускаем dbt, макрос dateadd компилирует вашу функцию в SQL-диалект адаптера хранилища, на котором вы работаете — он выполняет тот же SQL, который вы бы написали сами в своем родном браузере запросов.

И это на самом деле довольно простой макрос из 31 строки (исходный код здесь и снимок ниже) - если вы захотите его расширить (например, чтобы поддержать другой адаптер хранилища), я верю, что почти любой пользователь SQL квалифицирован для отправки PR в репозиторий:

{% macro dateadd(datepart, interval, from_date_or_timestamp) %}
  {{ adapter_macro('dbt_utils.dateadd', datepart, interval, from_date_or_timestamp) }}
{% endmacro %}


{% macro default__dateadd(datepart, interval, from_date_or_timestamp) %}

    dateadd(
        {{ datepart }},
        {{ interval }},
        {{ from_date_or_timestamp }}
        )

{% endmacro %}


{% macro bigquery__dateadd(datepart, interval, from_date_or_timestamp) %}

        datetime_add(
            cast( {{ from_date_or_timestamp }} as datetime),
        interval {{ interval }} {{ datepart }}
        )

{% endmacro %}


{% macro postgres__dateadd(datepart, interval, from_date_or_timestamp) %}

    {{ from_date_or_timestamp }} + ((interval '1 {{ datepart }}') * ({{ interval }}))

{% endmacro %}

Наслаждайтесь! К вашему сведению, я использовал макрос dateadd в dbt-utils на BigQuery, Postgres, Redshift и Snowflake, но он, вероятно, работает и в большинстве других хранилищ.

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

Этот пакет spark_utils может помочь вам реализовать необходимое переопределение для добавления поддержки функции dateadd в Databricks.