Module: Groupdate::SqlServerGroupClause

Included in:
RelationBuilder
Defined in:
lib/groupdate/sql_server_group_clause.rb

Instance Method Summary collapse

Instance Method Details

#sql_server_group_clause(tzid) ⇒ Object

Raises:



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
 
# File 'lib/groupdate/sql_server_group_clause.rb', line 5

def sql_server_group_clause(tzid)
  time_zone = to_windows_tz(tzid)
  raise Groupdate::Error, "unknown timezone: #{tzid}" if time_zone.nil?

  datetime_column = "(CAST(#{column} AS DATETIME2(3)) AT TIME ZONE 'UTC')"
  if day_start.to_i > 0
    adjusted_column = "DATEADD(second, #{day_start.to_i * -1}, #{datetime_column})"
  else
    adjusted_column = datetime_column
  end

  # To work with Date column, cast it to DATETIME2 first
  group_column = "#{adjusted_column} AT TIME ZONE '#{time_zone}'"
  day_of_week = "(@@DATEFIRST + DATEPART(weekday, #{group_column}) - 1) %% 7"

  if period == :day_of_week
    # - SQL Server settings:
    # - @@DATEFIRST : the start day setting of sql_server
    # - @@DATEFIRST defaults to 7, Sunday
    # - Monday..Sunday => 1..7
    # - Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.
    #
    # - GroupDate day_of_week = "(@@DATEFIRST + DATEPART(weekday, #{group_column}) - 1) %% 7": Sunday = 0, Monday = 1, .. Sat = 6
    return day_of_week
  elsif %i(hour_of_day minute_of_hour day_of_month month_of_year day_of_year).include?(period)
    case period
    when :hour_of_day
      dp = 'hour'
    when :minute_of_hour
      dp = 'minute'
    when :day_of_month
      dp = 'day'
    when :month_of_year
      dp = 'month'
    when :day_of_year
      dp = 'dayofyear'
    end
    return "DATEPART(#{dp}, #{group_column})"
  else
    case period
    when :week
      # --------------------------------------------------
      # - GroupDate week_start: Monday..Sunday => 0..6, default to 6 (Sunday)
      # [:mon, :tue, :wed, :thu, :fri, :sat, :sun].index
      day_offset = "-(7 + (#{day_of_week}) - (#{(week_start + 1) % 7})) %% 7"
      date_str = "CONVERT(varchar(30), DATEADD(DAY, #{day_offset}, #{group_column}), 102)"
    when :quarter
      date_str = "CONCAT(DATEPART(year, #{group_column}), '-', ((DATEPART(quarter, #{group_column}) - 1) * 3 + 1), '-01')"
    when :year
      date_str = "CONCAT(DATEPART(year, #{group_column}), '-01-01 00:00:00')"
    when :month
      date_str = "CONCAT(CONVERT(varchar(7), #{group_column}, 23), '-01')"
    when :day
      date_str = "CONVERT(varchar(30), #{group_column}, 23)"
    when :second
      date_str = "CONVERT(varchar(30), #{group_column}, 120)"
    when :minute
      date_str = "CONCAT(CONVERT(varchar(16), #{group_column}, 120), ':00')"
    when :hour
      date_str = "CONCAT(CONVERT(varchar(13), #{group_column}, 120), ':00:00')"
    else
      raise Groupdate::Error, "'#{period}' not supported for SQL Server"
    end

    converted_datetime_column = "CAST(#{date_str} AS DATETIME2(0)) AT TIME ZONE '#{time_zone}'"
    if day_start.to_i > 0
      converted_datetime_column = "DATEADD(second, #{day_start.to_i}, #{converted_datetime_column})"
    end

    "#{converted_datetime_column} AT TIME ZONE 'UTC'"
  end
end