forked from calogica/dbt-date
-
Notifications
You must be signed in to change notification settings - Fork 0
/
day_of_week.sql
84 lines (62 loc) · 2.02 KB
/
day_of_week.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
{%- macro day_of_week(date, isoweek=true) -%}
{{ adapter.dispatch('day_of_week', 'dbt_date') (date, isoweek) }}
{%- endmacro %}
{%- macro default__day_of_week(date, isoweek) -%}
{%- set dow = dbt_date.date_part('dayofweek', date) -%}
{%- if isoweek -%}
case
-- Shift start of week from Sunday (0) to Monday (1)
when {{ dow }} = 0 then 7
else {{ dow }}
end
{%- else -%}
{{ dow }} + 1
{%- endif -%}
{%- endmacro %}
{%- macro snowflake__day_of_week(date, isoweek) -%}
{%- if isoweek -%}
{%- set dow_part = 'dayofweekiso' -%}
{{ dbt_date.date_part(dow_part, date) }}
{%- else -%}
{%- set dow_part = 'dayofweek' -%}
case
when {{ dbt_date.date_part(dow_part, date) }} = 7 then 1
else {{ dbt_date.date_part(dow_part, date) }} + 1
end
{%- endif -%}
{%- endmacro %}
{%- macro bigquery__day_of_week(date, isoweek) -%}
{%- set dow = dbt_date.date_part('dayofweek', date) -%}
{%- if isoweek -%}
case
-- Shift start of week from Sunday (1) to Monday (2)
when {{ dow }} = 1 then 7
else {{ dow }} - 1
end
{%- else -%}
{{ dow }}
{%- endif -%}
{%- endmacro %}
{%- macro postgres__day_of_week(date, isoweek) -%}
{%- if isoweek -%}
{%- set dow_part = 'isodow' -%}
-- Monday(1) to Sunday (7)
cast({{ dbt_date.date_part(dow_part, date) }} as {{ type_int() }})
{%- else -%}
{%- set dow_part = 'dow' -%}
-- Sunday(1) to Saturday (7)
cast({{ dbt_date.date_part(dow_part, date) }} + 1 as {{ type_int() }})
{%- endif -%}
{%- endmacro %}
{%- macro redshift__day_of_week(date, isoweek) -%}
{%- set dow = dbt_date.date_part('dayofweek', date) -%}
{%- if isoweek -%}
case
-- Shift start of week from Sunday (0) to Monday (1)
when {{ dow }} = 0 then 7
else cast({{ dow }} as {{ type_bigint() }})
end
{%- else -%}
cast({{ dow }} + 1 as {{ type_bigint() }})
{%- endif -%}
{%- endmacro %}