Module: DWH::Functions::Dates

Included in:
DWH::Functions
Defined in:
lib/dwh/functions/dates.rb

Overview

Standard date functions except for those dealing with extracting a date part.

Constant Summary collapse

TIMESTAMPABLE_UNITS = 
%w[millisecond second minute hour].freeze
DATE_CLASSES = 
[Date, DateTime, Time].freeze

Instance Method Summary collapse

Instance Method Details

#adjust_week_start_day(exp) ⇒ Object

Apply translation to desired week start day



151
152
153
154
 
# File 'lib/dwh/functions/dates.rb', line 151

def adjust_week_start_day(exp)
  gsk("#{settings[:week_start_day].downcase}_week_start_day")
    .gsub(/@exp/i, exp)
end

#adjust_week_start_day? ⇒ Boolean

Whether we need to adjust the week start day. If its different from the default, we need to adjust

Returns:

  • (Boolean) 


146
147
148
 
# File 'lib/dwh/functions/dates.rb', line 146

def adjust_week_start_day?
  week_start_day.strip != default_week_start_day.strip
end

#current_date ⇒ Object

The native function to return current date



34
35
36
 
# File 'lib/dwh/functions/dates.rb', line 34

def current_date
  settings[:current_date]
end

#current_time ⇒ Object

The native function to return current time



39
40
41
 
# File 'lib/dwh/functions/dates.rb', line 39

def current_time
  settings[:current_time]
end

#current_timestamp ⇒ Object

The native function to return current timestamp



44
45
46
 
# File 'lib/dwh/functions/dates.rb', line 44

def current_timestamp
  settings[:current_timestamp]
end

#date_add(unit, val, exp) ⇒ Object

Add some interval of time to a given date expression.

Parameters:

  • unit (String) —

    the units we are adding i.e day, month, week etc

  • val (Integer) —

    the number of said units

  • exp (String) —

    the target expression being operated on



91
92
93
94
95
96
 
# File 'lib/dwh/functions/dates.rb', line 91

def date_add(unit, val, exp)
  gsk(:date_add)
    .gsub(/@unit/i, unit)
    .gsub(/@val/i, val.to_s)
    .gsub(/@exp/i, exp)
end

#date_data_type ⇒ Object

The native date storage type of the db.



24
25
26
 
# File 'lib/dwh/functions/dates.rb', line 24

def date_data_type
  settings[:data_type]
end

#date_diff(unit, start_exp, end_exp) ⇒ Object

Differnect between two dates in terms of the given unit.

Parameters:

  • unit (String) —

    i.e day, month, hour etc

  • start_exp (String) —

    starting date expression

  • end_exp (String) —

    ending date expression



102
103
104
105
106
107
 
# File 'lib/dwh/functions/dates.rb', line 102

def date_diff(unit, start_exp, end_exp)
  gsk(:date_diff)
    .gsub(/@unit/i, unit)
    .gsub(/@start_exp/i, start_exp)
    .gsub(/@end_exp/i, end_exp)
end

#date_format ⇒ Object

Ruby format string that is compatible for the target database.



7
8
9
 
# File 'lib/dwh/functions/dates.rb', line 7

def date_format
  settings[:date_format]
end

#date_format_sql(exp, format) ⇒ Object

Applies the given format to the target date expression



110
111
112
113
114
 
# File 'lib/dwh/functions/dates.rb', line 110

def date_format_sql(exp, format)
  gsk(:date_format_sql)
    .gsub(/@exp/i, exp)
    .gsub(/@format/i, format)
end

#date_int? ⇒ Boolean

Whether the db uses an int format store dates natively

Returns:

  • (Boolean) 


29
30
31
 
# File 'lib/dwh/functions/dates.rb', line 29

def date_int?
  date_data_type =~ /int/i
end

#date_literal(val) ⇒ Object

Generates a valid date literal string. Most db’s this is just single quoted value while others require a date declaration.

Parameters:

  • val (String, Date, DateTime, Time) 


122
123
124
125
 
# File 'lib/dwh/functions/dates.rb', line 122

def date_literal(val)
  val = DATE_CLASSES.include?(val.class) ? val.strftime(date_format) : val
  gsk(:date_literal).gsub(/@val/i, val)
end

#date_time_format ⇒ Object

Ruby format string that is compatible timestamp format for the target db.



13
14
15
 
# File 'lib/dwh/functions/dates.rb', line 13

def date_time_format
  settings[:date_time_format]
end

#date_time_literal(val) ⇒ Object

Parameters:

  • val (String, Date, DateTime, Time) 


128
129
130
131
 
# File 'lib/dwh/functions/dates.rb', line 128

def date_time_literal(val)
  val = DATE_CLASSES.include?(val.class) ? val.strftime(date_time_format) : val
  gsk(:date_time_literal).gsub(/@val/i, val)
end

#date_time_tz_format ⇒ Object

Ruby format string that is compatible timestamp with timezone format for the target db.



19
20
21
 
# File 'lib/dwh/functions/dates.rb', line 19

def date_time_tz_format
  settings[:date_time_tz_format]
end

#default_week_start_day ⇒ Object

The current default week start day. This is how the db is currently setup. Should be either monday or sunday



135
136
137
 
# File 'lib/dwh/functions/dates.rb', line 135

def default_week_start_day
  gsk(:default_week_start_day)
end

#truncate_date(unit, exp) ⇒ Object

Note:

When truncating a literal date rather than an expression, the date_literal function should be called on it first. e.g. truncate_date(‘week’, date_lit(‘2025-08-06’)) For many dbs it won’t matter, but some require date literals to be specified.

Given a date expression, truncate it to a given level. The SQL output of a truncation is still date or timestamp.

Examples:

Truncate date to the week start day

truncate_date('week', 'my_date_col')
Postgres ==> DATE_TRUNC('week', 'my_date_col')
SQL Server ==> DATETRUNC(week, 'my_date_col')

Parameters:

  • unit (String) —

    the unit to truncate to

  • exp (String) —

    the expression to truncate



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
# File 'lib/dwh/functions/dates.rb', line 66

def truncate_date(unit, exp)
  unit = unit.strip.downcase
  res = if unit == 'week' && adjust_week_start_day?
          gsk("#{settings[:week_start_day].downcase}_week_start_day")
            .gsub(/@exp/i, exp)
        else
          gsk(:truncate_date)
            .gsub(/@unit/i, unit)
            .gsub(/@exp/i, exp)

        end

  # If we are truncating above the timestamp level ie days, years etc
  # then we can cast the result to date
  if TIMESTAMPABLE_UNITS.include?(unit)
    res
  else
    cast(res, 'DATE')
  end
end

#week_start_day ⇒ Object

The desired week start day. Could be diff from the db setting.



140
141
142
 
# File 'lib/dwh/functions/dates.rb', line 140

def week_start_day
  gsk(:week_start_day)
end

#week_starts_on_sunday? ⇒ Boolean

Does the week start on sunday?

Returns:

  • (Boolean) 


157
158
159
 
# File 'lib/dwh/functions/dates.rb', line 157

def week_starts_on_sunday?
  gsk(:week_start_day) == 'SUNDAY'
end