如何在数组中创建一系列日期(宏)

huangapple go评论69阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年2月19日 10:25:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/75497633.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定