11
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2016-12-12

やりたいこと

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 (年)
11
10
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
11
10

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?