PostgreSQLで曜日ごと・時間帯ごとにレコードを抽出する

やりたいこと

railsのactiverecordを使って、PostgreSQLのtimestampカラムに対して、曜日ごと・時間帯ごとにレコードを抽出したい。

曜日ごと

スクリプト:

['日', '月', '火', '水', '木', '金', '土'].each_with_index do |dow, i|
  count_by_dow = Model.where("extract(dow from created_at AT TIME ZONE 'UTC' AT TIME ZONE 'JST') = ?", i).count
  puts "#{dow}曜日: #{count_by_dow}"
end

出力:

日曜日: 2405
月曜日: 3768
火曜日: 2336
水曜日: 4860
木曜日: 3158
金曜日: 2753
土曜日: 2220

created_at AT TIME ZONE 'UTC' AT TIME ZONE 'JST'は、(created_at AT TIME ZONE 'UTC') AT TIME ZONE 'JST'と同じ。DBのタイムゾーンがUTCなのでJSTに変換している。

時間帯ごと

スクリプト:

(0..23).each do |clock|
  time_string_from = "#{sprintf('%02d', clock)}:00"
  time_string_to = "#{sprintf('%02d', clock + 1)}:00"
  count_by_timescope = Model.where("to_char(created_at AT TIME ZONE 'UTC' AT TIME ZONE 'JST', 'HH24:MI') >= '#{time_string_from}'")
                            .where("to_char(created_at AT TIME ZONE 'UTC' AT TIME ZONE 'JST', 'HH24:MI') < '#{time_string_to}'")
                            .count
  puts "#{time_string_from}時〜: #{count_by_timescope}"
end

出力:

00:00時〜: 1438
01:00時〜: 735
02:00時〜: 157
03:00時〜: 142
04:00時〜: 75
05:00時〜: 176
06:00時〜: 325
07:00時〜: 348
08:00時〜: 537
09:00時〜: 1047
10:00時〜: 1228
11:00時〜: 1500
12:00時〜: 658
13:00時〜: 1129
14:00時〜: 1099
15:00時〜: 981
16:00時〜: 927
17:00時〜: 1502
18:00時〜: 1763
19:00時〜: 593
20:00時〜: 703
21:00時〜: 1026
22:00時〜: 1991
23:00時〜: 1420

to_char関数でタイムスタンプを文字列に変換してから、文字列で比較を行っている。もっと良い方法がありそうなのだが、、

補足

extract関数で、timestampのカラムからいろいろなデータが取得できる。https://www.postgresql.jp/document/7.2/user/functions-datetime.html に詳しく書いてある。

• century (世紀)
• day (日)
• decade (十年)
• dow (曜日)
• doy (通算日数)
• epoch (1970-01-01 00:00:00 からの秒数)
• hour (時)
• microseconds (マイクロ秒)
• millennium (ミレニアム)
• milliseconds (ミリ秒)
• minute (分)
• month (月)
• quarter (四半期)
• second (秒)
• timezone_hour (時間帯オフセットの時の成分)
• timezone_minute (時間帯オフセットの分の成分)
• week (週)
• year (年)