dbt-date

dbt-date is an extension package for dbt to handle common date logic and calendar functionality.

Featured Sponsors ❤️

Development of dbt-date (and dbt-expectations) is funded by our amazing sponsors, including our featured sponsors:

datacoves.com

Install

Include in packages.yml

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

This package supports:

• Postgres
• Snowflake
• BigQuery
• DuckDB
• Spark
• Trino

Variables

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

vars:
    "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.

Available Macros

Date Dimension

• get_base_dates
• get_date_dimension

Calendar Date

• convert_timezone
• date_part
• day_name
• day_of_month
• day_of_week
• day_of_year
• from_unixtimestamp
• iso_week_end
• iso_week_of_year
• iso_week_start
• last_month_name
• last_month_number
• last_month
• last_week
• month_name
• n_days_ago
• n_days_away
• n_months_ago
• n_months_away
• n_weeks_ago
• n_weeks_away
• next_month_name
• next_month_number
• next_month
• next_week
• now
• periods_since
• round_timestamp
• to_unixtimestamp
• today
• tomorrow
• week_end
• week_of_year
• week_start
• yesterday

Fiscal Date

• get_fiscal_periods

Utils

• date
• datetime

Documentation

get_base_dates(start_date=None, end_date=None, n_dateparts=None, datepart="day")

A wrapper around dbt_utils.date_spine that allows you to specify either start_date and end_date for your date spine, or specify a number of periods (n_dateparts) in the past from today.

Usage to build a daily date dimension for the years 2015 to 2022:

{{ dbt_date.get_base_dates(start_date="2015-01-01", end_date="2023-01-01") }}

or to build a daily date dimension for the last 3 years:

{{ dbt_date.get_base_dates(n_dateparts=365*3, datepart="day") }}

get_date_dimension(start_date, end_date)

Returns a 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(dates, year_end_month, week_start_day, shift_year=1)

Returns a 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( column, target_tz=None, source_tz=None)

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(datepart, date)

Extracts date parts from date.

Usage:

{{ dbt_date.date_part("dayofweek", "date_col") }} as day_of_week

day_name(date, short=True)

Extracts name of weekday from date.

Usage:

{{ dbt_date.day_name("date_col") }} as day_of_week_short_name

{{ dbt_date.day_name("date_col", short=true) }} as day_of_week_short_name

{{ dbt_date.day_name("date_col", short=false) }} as day_of_week_long_name

day_of_month(date)

Extracts day of the month from a date (e.g. 2022-03-06 --> 6).

Usage:

{{ dbt_date.day_of_month("date_col") }} as day_of_month

day_of_week(date, isoweek=true)

Extracts day of the week number from a date, starting with 1. By default, uses isoweek=True, i.e. assumes week starts on Monday.

Usage:

{{ dbt_date.day_of_week("'2022-03-06'") }} as day_of_week_iso

returns: 7 (Sunday is the last day of the ISO week)

{{ dbt_date.day_of_week("'2022-03-06'", isoweek=False) }} as day_of_week

returns: 1 (Sunday is the first day of the non-ISO week)

day_of_year(date)

Extracts day of the year from a date (e.g. 2022-02-02 --> 33).

Usage:

{{ dbt_date.day_of_year("date_col") }} as day_of_year

or

{{ dbt_date.day_of_year("'2022-02-02'") }} as day_of_year

returns: 33

from_unixtimestamp(epochs, format="seconds")

Converts an epoch into a timestamp. The default for format is seconds, which can overriden depending your data"s epoch format.

Usage:

{{ dbt_date.from_unixtimestamp("epoch_column") }} as timestamp_column

{{ dbt_date.from_unixtimestamp("epoch_column", format="milliseconds") }} as timestamp_column

See also: to_unixtimestamp

iso_week_end(date=None, tz=None)

Computes the week ending date using ISO format, i.e. week starting Monday and ending Sunday.

Usage:

{{ dbt_date.iso_week_end("date_col") }} as iso_week_end_date

or, optionally, you can override the default timezone:

{{ dbt_date.iso_week_end("date_col", tz="America/New_York") }} as iso_week_end_date

iso_week_of_year(date=None, tz=None)

Computes the week of the year using ISO format, i.e. week starting Monday.

Usage:

{{ dbt_date.iso_week_of_year("date_col") }} as iso_week_of_year

or, optionally, you can override the default timezone:

{{ dbt_date.iso_week_of_year("date_col", tz="America/New_York") }} as iso_week_of_year

iso_week_start(date=None, tz=None)

Computes the week starting date using ISO format, i.e. week starting Monday.

Usage:

{{ dbt_date.iso_week_start("date_col") }} as iso_week_start_date

or, optionally, you can override the default timezone:

{{ dbt_date.iso_week_start("date_col", tz="America/New_York") }} as iso_week_start_date

last_month_name(short=True, tz=None)

Extracts the name of the prior month from a date.

{{ dbt_date.last_month_name() }} as last_month_short_name

{{ dbt_date.last_month_name(short=true) }} as last_month_short_name

{{ dbt_date.last_month_name(short=false) }} as last_month_long_name

or, optionally, you can override the default timezone:

{{ dbt_date.last_month_name(tz="America/New_York") }} as last_month_short_name

last_month_number(tz=None)

Returns the number of the prior month.

{{ dbt_date.last_month_number() }}

or, optionally, you can override the default timezone:

{{ dbt_date.last_month_number(tz="America/New_York") }}

last_month(tz=None)

Returns the start date of the prior month.

{{ dbt_date.last_month() }} as last_month_start_date

or, optionally, you can override the default timezone:

{{ dbt_date.last_month(tz="America/New_York") }} as last_month_start_date

last_week(tz=None)

Convenience function to get the start date of last week (non-ISO)

Wraps:

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

Usage:

{{ dbt_date.last_week()) }} as last_week_start_date

or, optionally, you can override the default timezone:

{{ dbt_date.last_week(tz="America/New_York)) }} as last_week_start_date

month_name(date, short=True, tz=None)

Extracts the name of the month from a date.

{{ dbt_date.month_name(date_col) }} as month_short_name

{{ dbt_date.month_name(date_col, short=true) }} as month_short_name

{{ dbt_date.month_name(date_col, short=false) }} as month_long_name

n_days_ago(n, date=None, tz=None)

Gets date n days ago, based on local date.

Usage:

{{ dbt_date.n_days_ago(7) }}

Alternatively, you can specify a date column instead of defaulting the local date:

{{ dbt_date.n_days_ago(7, date="date_col") }}

or, optionally, you can override the default timezone:

{{ dbt_date.n_days_ago(7, tz="America/New_York)) }}

n_days_away(n, date=None, tz=None)

Gets date n days away, based on local date.

Usage:

{{ dbt_date.n_days_away(7) }}

Alternatively, you can specify a date column instead of defaulting the local date:

{{ dbt_date.n_days_away(7, date="date_col") }}

or, optionally, you can override the default timezone:

{{ dbt_date.n_days_away(7, tz="America/New_York)) }}

n_months_ago(n, tz=None)

Gets date n months ago, based on local date.

Usage:

{{ dbt_date.n_months_ago(12) }}

or, optionally, you can override the default timezone:

{{ dbt_date.n_months_ago(12, tz="America/New_York)) }}

n_months_away(n, tz=None)

Gets date n months away, based on local date.

Usage:

{{ dbt_date.n_months_ago(12) }}

or, optionally, you can override the default timezone:

{{ dbt_date.n_months_away(12, tz="America/New_York)) }}

n_weeks_ago(n, tz=None)

Gets date n weeks ago, based on local date.

Usage:

{{ dbt_date.n_weeks_ago(12) }}

or, optionally, you can override the default timezone:

{{ dbt_date.n_weeks_ago(12, tz="America/New_York)) }}

n_weeks_away(n, tz=None)

Gets date n weeks away, based on local date.

Usage:

{{ dbt_date.n_weeks_away(12) }}

or, optionally, you can override the default timezone:

{{ dbt_date.n_weeks_away(12, tz="America/New_York)) }}

next_month_name(short=True, tz=None)

Extracts the name of the next month from a date.

{{ dbt_date.next_month_name() }} as next_month_short_name

{{ dbt_date.next_month_name(short=true) }} as next_month_short_name

{{ dbt_date.next_month_name(short=false) }} as next_month_long_name

or, optionally, you can override the default timezone:

{{ dbt_date.next_month_name(tz="America/New_York") }} as next_month_short_name

next_month_number(tz=None)

Returns the number of the next month.

{{ dbt_date.next_month_number() }}

or, optionally, you can override the default timezone:

{{ dbt_date.next_month_number(tz="America/New_York") }}

next_month(tz=None)

Returns the start date of the next month.

{{ dbt_date.next_month() }} as next_month_start_date

or, optionally, you can override the default timezone:

{{ dbt_date.next_month(tz="America/New_York") }} as next_month_start_date

next_week(tz=None)

Convenience function to get the start date of next week (non-ISO)

Wraps:

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

Usage:

{{ dbt_date.next_week()) }} as next_week_start_date

or, optionally, you can override the default timezone:

{{ dbt_date.next_week(tz="America/New_York") }}  as next_week_start_date

now(tz=None)

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

Usage:

{{ dbt_date.now() }}

or, optionally, you can override the default timezone:

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

periods_since(date_col, period_name='day', tz=None)

Returns the number of periods since a specified date or to now.

Usage:

{{ dbt_date.periods_since("my_date_column", period_name="day") }}

or,

{{ dbt_date.periods_since("my_timestamp_column", period_name="minute") }}

or, optionally, you can override the default timezone:

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

round_timestamp(timestamp)

Rounds the given timestamp or date to the nearest date (return type is timestamp).

select
{{ dbt_date.round_timestamp("timestamp_col") }} as nearest_date
...

A few examples:

{{ dbt_date.round_timestamp("'2022-02-05 18:45:15'")}}
-- results in 2022-02-06

{{ dbt_date.round_timestamp("'2022-02-05 11:45:15'")}}
-- results in 2022-02-05

{{ dbt_date.round_timestamp("'2022-02-05 12:00:00'")}}
-- results in 2022-02-06

{{ dbt_date.round_timestamp("'2022-02-05 00:00:00'")}}
-- results in 2022-02-05

to_unixtimestamp(timestamp)

Gets Unix timestamp (epochs) based on provided timestamp.

Usage:

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

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

today(tz=None)

Gets date based on local timezone.

Usage:

{{ dbt_date.today() }}

or, optionally, you can override the default timezone:

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

tomorrow(date=None, tz=None)

Gets tomorrow's date, based on local date.

Usage:

{{ dbt_date.tomorrow() }}

or, optionally, you can override the default timezone:

{{ dbt_date.tomorrow(tz="America/New_York") }} as date_tomorrow

Alternatively, you can also override the anchor date from the default today to some other date:

{{ dbt_date.tomorrow(date="date_col", tz="America/New_York") }} as date_tomorrow

week_end(date=None, tz=None)

Computes the week ending date using standard (US) format, i.e. week starting Sunday.

Usage:

If date is not specified, the date anchor defaults to today.

{{ dbt_date.week_end() }} as week_end_date

or specify a date (column):

{{ dbt_date.week_end("date_col") }} as week_end_date

or, optionally, you can override the default timezone:

{{ dbt_date.week_end("date_col", tz="America/New_York") }} as week_end_date

week_of_year(date=None, tz=None)

Computes the week of the year using standard (US) format, i.e. week starting Sunday and ending Saturday.

Usage:

If date is not specified, the date anchor defaults to today.

{{ dbt_date.week_of_year() }} as week_of_year

or specify a date (column):

{{ dbt_date.week_of_year("date_col") }} as week_of_year

or, optionally, you can override the default timezone:

{{ dbt_date.week_of_year("date_col", tz="America/New_York") }} as week_of_year

week_start(date=None, tz=None)

Computes the week starting date using standard (US) format, i.e. week starting Sunday.

Usage:

If date is not specified, the date anchor defaults to today.

{{ dbt_date.week_start() }} as week_start

or specify a date (column):

{{ dbt_date.week_start("date_col") }} as week_start

or, optionally, you can override the default timezone:

{{ dbt_date.week_start("date_col", tz="America/New_York") }} as week_start

yesterday(date=None, tz=None)

Gets yesterday's date, based on local date.

Usage:

If date is not specified, the date anchor defaults to today.

{{ dbt_date.yesterday() }} as date_yesterday

or specify a date (column):

{{ dbt_date.yesterday("date_col") }} as date_yesterday

or, optionally, you can override the default timezone:

{{ dbt_date.yesterday(tz="America/New_York") }} as date_yesterday

date(year, month, day)

Reduces the boilerplate syntax required to produce a date object. This is not converted to a string to allow pythonic manipulation.

Usage:

{% set date_object = dbt_date.date(1997, 9, 29) %}

datetime(year, month, day, hour=0, minute=0, second=0, microsecond=0, tz=None)

Reduces the boilerplate syntax required to produce a datetime object. This is not converted to a string to allow pythonic manipulation.

Usage:

{% set datetime_object = dbt_date.datetime(1997, 9, 29, 6, 14) %}

or, optionally, you can override the default timezone:

{% set datetime_object = dbt_date.datetime(1997, 9, 29, 6, 14, tz='America/New_York') %}

Integration Tests (Developers Only)

This project contains integration tests for all test macros in a separate integration_tests dbt project contained in this repo.

To run the tests:

1. You will need a profile called integration_tests in ~/.dbt/profiles.yml pointing to a writable database. We only support postgres, BigQuery and Snowflake.
2. Then, from within the integration_tests folder, run dbt build to run the test models in integration_tests/models/schema_tests/ and run the tests specified in integration_tests/models/schema_tests/schema.yml