dbt-date
is an extension package for dbt to handle common date logic and calendar functionality.
Development of dbt-date
(and dbt-expectations
) is funded by our amazing sponsors, including our featured sponsors:
Include in packages.yml
packages:
- package: calogica/dbt_date
version: [">=0.7.0", "<0.8.0"]
# <see https://github.com/calogica/dbt-date/releases/latest> for the latest version tag
This package supports:
- Postgres
- Snowflake
- BigQuery
For other platforms, you will have to include a shim package for the platform, such as spark-utils
, or tsql-utils
.
For example, in packages.yml
, you will need to include the relevant package:
- package: dbt-labs/spark_utils
version: <latest or range>
And reference in the dispatch list for dbt_date
in dbt_project.yml
:
vars:
dbt_date_dispatch_list: [spark_utils]
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.
- 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
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:
{{ dbt_date.get_base_dates(start_date="2015-01-01", end_date="2022-12-31") }}
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") }}
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).
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
This project contains integration tests for all test macros in a separate integration_tests
dbt project contained in this repo.
To run the tests:
- You will need a profile called
integration_tests
in~/.dbt/profiles.yml
pointing to a writable database. We only support postgres, BigQuery and Snowflake. - Then, from within the
integration_tests
folder, rundbt build
to run the test models inintegration_tests/models/schema_tests/
and run the tests specified inintegration_tests/models/schema_tests/schema.yml