dbt-date

Extension package for dbt to handle date logic and calendar functionality.

FYI: this package includes dbt-utils so there's no need to also import dbt-utils in your local project. (In fact, you may get an error if you do.)

Include in packages.yml

packages:
  - package: calogica/dbt_date
    version: [">=0.4.0", "<0.5.0"]
    # <see https://github.com/calogica/dbt-date/releases/latest> for the latest version tag

Note: we no longer include spark_utils in this package to avoid versioning conflicts. If you are running this package on non-core (Snowflake, BigQuery, Redshift, Postgres) platforms, you will need to use a package like spark_utils to shim macros.

For example, in packages.yml, you will need to include the relevant package:

  - package: fishtown-analytics/spark_utils
    version: <latest or range>

And reference in the dispatch list for dbt_utils in dbt_project.yml:

vars:
    dbt_utils_dispatch_list: [spark_utils]

Variables

The following variables need to be defined in your dbt_project.yml file:

'dbt_date:time_zone': 'America/Los_Angeles'

You may specify any valid timezone string in place of America/Los_Angeles. For example, use America/New_York for East Coast Time.

Macros

Date Dimension

get_date_dimension (source)

Returns query to build date dimension from/to specified dates, including a number of useful columns based on each date. See the example model for details.

Usage:

{{ dbt_date.get_date_dimension('2015-01-01', '2022-12-31') }}

Fiscal Periods

get_fiscal_periods (source)

Returns query to build a fiscal period calendar based on the 4-5-4 week retail period concept. See the example model for details and this blog post for more context on custom business calendars.

Usage:

{{ dbt_date.get_fiscal_periods(ref('dates'), year_end_month, week_start_day) }}

Note: the first parameter expects a dbt ref variable, i.e. a reference to a model containing the necessary date dimension attributes, which can be generated via the get_date_dimension macro (see above).

Date

convert_timezone (source)

Cross-database implemention of convert_timezone function.

Usage:

{{ dbt_date.convert_timezone('my_column') }}

or, specify a target timezone:

{{ dbt_date.convert_timezone('my_column', 'America/New_York') }}

or, also specify a source timezone:

{{ dbt_date.convert_timezone('my_column', 'America/New_York', 'UTC') }}

Using named parameters, we can also specify the source only and rely on the configuration parameter for the target:

{{ dbt_date.convert_timezone('my_column', source_tz='UTC') }}

date_part (source)

Extracts date parts from date.

Usage:

{{ dbt_date.date_part('dayofweek', 'date_day') }} as day_of_week

day_name (source)

Extracts name of weekday from date.

Usage:

{{ dbt_date.day_name('date_day', short=true) }} as day_of_week_short_name,
{{ dbt_date.day_name('date_day', short=false) }} as day_of_week_long_name

last_week (source)

Convenience function to get the start date of last week

Wraps:

{{ dbt_date.n_weeks_ago(1, tz) }}

Usage:

{{ dbt_date.last_week()) }}

{{ dbt_date.last_week(tz='America/New_York)) }}

month_name (source)

Extracts name of month from date.

{{ dbt_date.month_name('date_day', short=true) }} as month_short_name,
{{ dbt_date.month_name('date_day', short=false) }} as month_long_name

n_days_ago (source)

Gets date n days ago, based on local date.

Usage:

{{ dbt_date.n_days_ago(7) }}

n_days_away (source)

Gets date n days from now, based on local date.

Usage:

{{ dbt_date.n_days_away(7) }}

n_months_ago (source)

Gets date n months ago, based on local date.

Usage:

{{ dbt_date.n_months_ago(12) }}

n_months_away (source)

Gets date n months from now, based on local date.

Usage:

{{ dbt_date.n_months_away(12) }}

n_weeks_ago (source)

Gets date n weeks ago, based on local date.

Usage:

{{ dbt_date.n_weeks_ago(4) }}

n_weeks_away (source)

Gets date n weeks from now, based on local date.

Usage:

{{ dbt_date.n_weeks_away(4) }}

now (source)

Gets time based on local timezone (specified). Default is "America/Los_Angeles".

Usage:

{{ dbt_date.now() }}

or, specify a timezone:

{{ dbt_date.now('America/New_York') }}

periods_since (source)

Returns the number of periods since a specified date.

Usage:

{{ dbt_date.periods_since('my_date_column', period_name='day' }}

The macro used the dbt_date:time_zone variable from dbt_project.yml to calculate today's date. Alternatively, a timezone can be specified via the tz parameter:

{{ dbt_date.periods_since('my_timestamp_column', period_name='minute', tz='UTC' }}

this_week (source)

Gets current week start date, based on local date.

Usage:

{{ dbt_date.this_week() }}

to_unixtimestamp (source)

Gets Unix timestamp (epochs) based on provided timestamp.

Usage:

{{ dbt_date.to_unixtimestamp('my_timestamp_column') }}

{{ dbt_date.to_unixtimestamp(dbt_date.now()) }}

today (source)

Gets date based on local timezone (specified). Package default is "America/Los_Angeles". The default must be specified in dbt_project.yml, in the 'dbt_date:time_zone' variable. e.g 'dbt_date:time_zone': 'America/New_York'.

Usage:

{{ dbt_date.today() }}

or, specify a timezone:

{{ dbt_date.today('America/New_York') }}

tomorrow (source)

Gets tomorrow's date, based on local date.

Usage:

{{ dbt_date.tomorrow() }}

or, specify a timezone:

{{ dbt_date.tomorrow('America/New_York') }}

yesterday (source)

Gets yesterday's date, based on local date.

Usage:

{{ dbt_date.yesterday() }}