英文:
How to create a range of dates in an array (macro)
问题
I'm trying to develop a macro to create a range of dates (datetime) that will be kept in an array.
I'm a beginner with Jinja (DBT dialect) and I'm very near a solution but it seems that something is missing.
I received an error message that let me think that this is only a problem of casting.
I want to keep all code inside a macro in DBT.
Here is my code (DBT macro in Jinja) to generate the array of dates (datetime in fact) :
{%- macro get_range_of_dates() -%}
{%- set dates = [] -%}
{%- set start = modules.datetime.datetime.strptime(var("currentDate"), "%Y-%m-%d") -%}
{%- set end = modules.datetime.datetime.now() -%}
{%- if start and end -%}
{%- set duration = (end - start).days + 1 -%}
{%- for day in range(0, duration) -%}
{%- set tempo = dbt.dateadd(datepart="day", interval=day, from_date_or_timestamp="' ~ start ~ '") -%}
{%- set final = modules.datetime.datetime.strptime(tempo, '%Y/%m/%d') -%}
{%- do dates.append(final) -%}
{%- endfor -%}
{%- endif -%}
{{ dates }}
{%- endmacro -%}
When I run this code, I received the following error message:
time data "\n\n dateadd(\n day,\n 0,\n '2023-02-07 00:00:00'\n )\n\n" does not match format '%Y/%m/%d'
(As you can guess with this message, my variable called "CurrentDate" coming from DBT is the first date of the range and is equal to "2023-02-07").
The question: How to keep the result of "dbt.dateadd...." as a datetime in variable "tempo" ?
Do I need to cast it ? and if yes, how ?
Many thanks in advance for your help.
英文:
I'm trying to develop a macro to create a range of dates (datetime) that will be kept in an array.
I'm a beginner with Jinja (DBT dialect) and I'm very near a solution but it seems that something is missing.
I received an error message that let me think that this is only a problem of casting.
I want to keep all code inside a macro in DBT.
Here is my code (DBT macro in Jinja) to generate the array of dates (datetime in fact) :
{%- macro get_range_of_dates() -%}
{%- set dates = [] -%}
{%- set start = modules.datetime.datetime.strptime(var("currentDate"), "%Y-%m-%d") -%}
{%- set end = modules.datetime.datetime.now() -%}
{%- if start and end -%}
{%- set duration = (end - start).days + 1 -%}
{%- for day in range(0, duration) -%}
{%- set tempo = dbt.dateadd(datepart="day", interval=day, from_date_or_timestamp="'" ~ start ~ "'") -%}
{%- set final = modules.datetime.datetime.strptime(tempo,'%Y/%m/%d') -%}
{%- do dates.append(final) -%}
{%- endfor -%}
{%- endif -%}
{{ dates }}
{%- endmacro -%}
When I run this code, I received the following error message:
time data "\n\n dateadd(\n day,\n 0,\n '2023-02-07 00:00:00'\n )\n\n" does not match format '%Y/%m/%d'
(As you can guess with this message, my variable called "CurrentDate" coming from DBT is the first date of the range and is equal to "2023-02-07").
The question: How to keep the result of "dbt.dateadd...." as a datetime in variable "tempo" ?
Do I need to cast it ? and if yes, how ?
Many thanks in advance for your help.
答案1
得分: 3
有一个名为dbt_utils
的宏,已经为您执行了这项任务。以下是一个示例,使用它来构建日期维度表。
SELECT
DS.DATE_DAY RAW_DATE,
DATE_PART('MONTH', DS.DATE_DAY) MONTH,
DATE_PART('DAY', DS.DATE_DAY) DAY,
DATE_PART('YEAR', DS.DATE_DAY) YEAR,
DATE_PART('DAYOFWEEK', DS.DATE_DAY) DAY_OF_WEEK,
DATE_PART('DAYOFYEAR', DS.DATE_DAY) DAY_OF_YEAR,
DATE_PART('WEEK', DS.DATE_DAY) WEEK,
DATE_PART('QUARTER', DS.DATE_DAY) QUARTER,
(DAY_OF_WEEK = 0 OR DAY_OF_WEEK = 6) IS_WEEKEND
FROM ({{ dbt_utils.date_spine(datepart="day", start_date="to_date('01-01-1970', 'MM-DD-YYYY')", end_date="to_date('12-31-2099', 'MM-DD-YYYY')" )}}) DS
只需将硬编码的日期替换为您的 vars
。
英文:
There is a dbt_utils
macro that already does this for you. Here's an example that uses it to build a date dimension table.
SELECT
DS.DATE_DAY RAW_DATE,
DATE_PART('MONTH', DS.DATE_DAY) MONTH,
DATE_PART('DAY', DS.DATE_DAY) DAY,
DATE_PART('YEAR', DS.DATE_DAY) YEAR,
DATE_PART('DAYOFWEEK', DS.DATE_DAY) DAY_OF_WEEK,
DATE_PART('DAYOFYEAR', DS.DATE_DAY) DAY_OF_YEAR,
DATE_PART('WEEK', DS.DATE_DAY) WEEK,
DATE_PART('QUARTER', DS.DATE_DAY) QUARTER,
(DAY_OF_WEEK = 0 OR DAY_OF_WEEK = 6) IS_WEEKEND
FROM ({{ dbt_utils.date_spine(datepart="day", start_date="to_date('01-01-1970', 'MM-DD-YYYY')", end_date="to_date('12-31-2099', 'MM-DD-YYYY')" )}}) DS
Just replace the hardcoded dates with your vars
.
答案2
得分: 1
我最终找到了解决方案。我将它发布出来,以防这对某人有帮助。
我错的主要地方是在使用函数“dbt.dateadd...”而不是使用函数“timedelta(...”以及我没有直接使用函数“strftime”与datetime对象。
这是最终的代码与注释:
{%- macro get_range_of_dates() -%}
{# 创建一个空数组来存储所有日期 #}
{%- set datesArray = [] -%}
{# 从dbt_project.yml中定义的变量创建表示日期范围的起始日期 #}
{%- set start = modules.datetime.datetime.strptime(var("currentDate"), "%Y-%m-%d") -%}
{# 创建一个表示日期范围结束日期的日期(始终为currentDay) #}
{%- set end = modules.datetime.datetime.now() -%}
{# 如果起始和结束变量不为空...#}
{%- if start and end -%}
{# 计算范围的长度 #}
{%- set duration = (end - start).days + 1 -%}
{# 循环范围 #}
{%- for day in range(0, duration) -%}
{# 每次迭代时添加一天 #}
{%- set dateIterated = (start + modules.datetime.timedelta(day)) -%}
{# 使用所需格式将datetime转换为字符串 #}
{%- set finalDate = dateIterated.strftime("%Y-%m-%d") -%}
{# 将新日期附加到日期数组中 #}
{%- do datesArray.append(finalDate) -%}
{%- endfor -%}
{%- endif -%}
{# 返回日期数组 #}
{{ datesArray }} {%- endmacro -%}
这是调用:
select {{ get_range_of_dates() }} from
这是此调用的结果:
select
['2023-02-07', '2023-02-08', '2023-02-09', '2023-02-10', '2023-02-11', '2023-02-12', '2023-02-13', '2023-02-14', '2023-02-15', '2023-02-16', '2023-02-17', '2023-02-18', '2023-02-19', '2023-02-20'] from
希望这能帮助某人。问候。
英文:
I finally found myself a solution. I post it in case this can help someone.
The principal point where I was wrong was the utilization of the function "dbt.dateadd..." instead of the utilization of the function "timedelta(..." and also the fact that I didn't use directly the function "strftime" with the datetime object.
Here is the final code with comments:
{%- macro get_range_of_dates() -%}
{# Create an empty array to keep all dates #}
{%- set datesArray = [] -%}
{# Create a date representing the starting date of the range from a variable defined in dbt_project.yml #}
{%- set start = modules.datetime.datetime.strptime(var("currentDate"), "%Y-%m-%d") -%}
{# Create a date reprsenting the ending date of the range (always currentDay) #}
{%- set end = modules.datetime.datetime.now() -%}
{# If start and end variables are not empty...#}
{%- if start and end -%}
{# Calculate the lenght of the range #}
{%- set duration = (end - start).days + 1 -%}
{# Loop the range #}
{%- for day in range(0, duration) -%}
{# Add one day at each iteration #}
{%- set dateIterated = (start + modules.datetime.timedelta(day)) -%}
{# Convert datetime to string with the desired format #}
{%- set finalDate = dateIterated.strftime("%Y-%m-%d") -%}
{# Append the new date to the array of dates #}
{%- do datesArray.append(finalDate) -%}
{%- endfor -%}
{%- endif -%}
{# return the array of dates #}
{{ datesArray }} {%- endmacro -%}
Here is the call:
select {{ get_range_of_dates() }} from
And here is the results of this call:
select
['2023-02-07', '2023-02-08', '2023-02-09', '2023-02-10', '2023-02-11', '2023-02-12', '2023-02-13', '2023-02-14', '2023-02-15', '2023-02-16', '2023-02-17', '2023-02-18', '2023-02-19', '2023-02-20'] from
Hoping that this can help somebody.
Regards.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论