やりたいこと
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 (年)