dbt-date

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

Development of dbt-date is supported by Xebia Data (formerly known as GoDataDriven):

Install

Include in packages.yml

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

Supported Adapters

Adapter	Full Support	Partial Support
BigQuery	✅
Databricks	✅
DuckDB	✅
Postgres	✅
Spark	✅
Trino	✅
Snowflake		✅

• Full Support: Macros are tested against this adapter on every pull request and merge to main.
• Partial Support: Macros are not tested against this adapter on every pull request and merge to main. Support is provided, please create an issue.

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_year_week
• 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, language="default")

Extracts name of weekday from date. For language=default this will return the name depending on the language set in the database. To get weekday names in a specific language use the two-letter language abbreviation of a supported language (en, nl, de, fr, es, it, pt, pl, da, sv, tr, cs, fi) or overwrite the get_localized_datepart_names macro with your own language of choice.

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, language="es") }} as day_of_week_long_name_localized

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_year_week(date)

Computes the year-week combination in ISO-format, e.g. 2026-W01. Combining year and week seperately will return incorrect results at the edges of the year as start and end dates of the week might fall in a different year. This macro calculates the correct year for the ISO week, e.g. December 31st can fall in week 01 of the next 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, language="default")

Extracts the name of the month from a date. For language=default this will return the name depending on the language set in the database. To get month names in a specific language use the two-letter language abbreviation of a supported language (en, nl, de, fr, es, it, pt, pl, da, sv, tr, cs, fi) or overwrite the get_localized_datepart_names macro with your own language of choice.

{{ 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, language="nl") }} as month_long_name_localized

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') %}

Contributing

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

1. Set up your development environment:

   python -m venv .venv
   source .venv/bin/activate
   make setup

2. Copy .env_example to .env and fill in the necessary values. Set the environment variables:

   source .env

3. Run the integration tests:

   • To run all tests in parallel:

     tox -p all

   • To run all tests in series:

     tox

   • To run tests for a specific adapter:

     tox -e dbt_integration_<ADAPTER>

4. To debug on a specific adapters:

   dbt debug --target <ADAPTER>