

RubyonRailsで記事の投稿を集計（時間別）する処理の実装をしております。

現在、次のようなSQLを実行し、集計した結果をDBに保存し、Web上の画面に表示させるということをやりたいです。

SQL

> SELECT date_format(created_at, '%Y-%m-%d-%H') as hour, count(id)  
>  FROM `articles` WHERE (created_at >= "2016-05-29" AND created_at <
> "2016-05-30")  
>  GROUP BY hour;

集計用テーブル

> Report  
>  id(integer)  
>  hour(datetipe)  
>  count(integer)

SQLを実行してから、DBに保存するところの処理はまだ考えている途中のため、実装できてませんが、  
現在、問題視している点は、hour(datetipe)の部分です。

というのも、集計するときにdate_format(created_at, '%Y-%m-%d-%H')という風にしているため、  
そのまま、DBに保存ができなくなってしまいます。

%Y-%m-%d-%Hという風に整形している理由は後でも述べますが、2016年05月29日（水）というふうに表示させるため、区切りを認識する文字をいれておきたかったためです。

それに対して私が考えた方法は次の2つです。

[code]

    1) hourをstring型に変更する
    ２） SQL実行の際にdate_format(created_at, '%Y-%m-%d-%H')を使わず、上手く時間別に集計する
    
[/code]

1)については、string型で保存をし、画面を表示する際に、Controllerで  
stringからdatetime型に変換する処理を入れるといいかなと思っております。  
（変換する理由は曜日を取得したいためです）

また、画面側では  
2016年05月29日（水）  
と言った風に表示できるようにしたいと思っております。

1)のやり方だと頑張ればできそうな気がするのですが、そもそもstring型で日付を保存することに違和感を感じてしまいます。

しかし、2)については方法がわからず困っております。

以上をふまえまして、アドバイスを頂けますと幸いです。

よろしくお願いします。

## 追記

表題の問題はmetropolis様のご回答により解決しました。

今回は時間別に集計するため、集計キーをDATE_FORMAT で %Y-%m-%d %H:00:00 に変換して DATETIME
型にキャストするという方法ができました。

しかし、今後、もし月別に集計したいとなったとき、集計キーは(2016-04)みたいな形になります。  
こういった場合はDATETIME型もしくはDATE型にキャストできないのかなと思いました。

こういう場合は、String型で保存するしかないでしょうか？


CC BY-SA 3.0
2016-06-24T13:52:24.623
27034
2016-07-15T12:59:21.673
2016-06-25T10:07:08.073
13175
13175
question
ruby-on-rails ruby mysql sql
SQLで集計（この部分のSQLあり）した上でDBに日付のデータも保存するにはどのようにするのがいいでしょうか？


PostgreSQLであれば、date_trunc  
<https://www.postgresql.jp/document/9.5/html/functions-
datetime.html#functions-datetime-trunc>  
を実行した列で集約すれば、望みの値が得られるように思います

SQLのサンプルも置いておきます  
<http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9925>

[code]

    WITH articles AS (
    SELECT
      id, created_at
    FROM (
      VALUES
        (1, TIMESTAMP '2016-07-15 00:00:00'),
        (2, TIMESTAMP '2016-07-15 00:01:00'),
        (3, TIMESTAMP '2016-07-15 01:00:00'),
        (4, TIMESTAMP '2016-07-15 01:01:00'),
        (5, TIMESTAMP '2016-07-15 01:02:00'),
        (6, TIMESTAMP '2016-07-15 02:00:00')
    ) AS T(id, created_at)
    )
    SELECT
      hour_trunc,
      count(*) AS hour_count
    FROM (  
      SELECT
        id,
        date_trunc('hour', created_at) AS hour_trunc,
        count(created_at) over (partition by date_trunc('hour', created_at))
      FROM
        articles
    ) T
    GROUP BY
      hour_trunc
    ORDER BY
      hour_trunc
    ;
    
[/code]

集約絡みでうまくいかないことがあれば、window functionを使うと多くの問題が解決できると思います  
機会があれば挑戦してみてください  
(でも、RailsのSQLbuilderは生成してくれないと思います...)

* * *

mysqlでは TIME_FORMAT するか TO_SECONDS の剰余を取るほかないようです...  
<http://sqlfiddle.com/#!9/9eecb7d/69182>

[code]

    SELECT
      sec,
      FROM_UNIXTIME(sec * 3600 - TO_SECONDS('1970-01-01 00:00:00')) as my_date_time,
      count(sec) AS count
    FROM (
      SELECT
        id,
        updated_at,
        CAST(TO_SECONDS(updated_at) / 3600 AS SIGNED INTEGER) AS sec,
        TIME_FORMAT(updated_at, '%Y%m%d%h') AS ymdh
      FROM (
        SELECT 1 AS id, TIMESTAMP '2016-07-15 00:00:00' AS updated_at FROM DUAL
        UNION ALL SELECT 2, TIMESTAMP '2016-07-15 00:01:00' FROM DUAL
        UNION ALL SELECT 3, TIMESTAMP '2016-07-15 01:00:00' FROM DUAL
        UNION ALL SELECT 4, TIMESTAMP '2016-07-15 01:01:00' FROM DUAL
        UNION ALL SELECT 5, TIMESTAMP '2016-07-15 01:02:00' FROM DUAL
        UNION ALL SELECT 6, TIMESTAMP '2016-07-15 02:00:00' FROM DUAL
      ) AS T
    ) AS T
    GROUP BY
      sec
    ORDER BY
      sec
    ;
    
[/code]


CC BY-SA 3.0
2016-07-15T12:07:23.947
27651
2016-07-15T12:59:21.673
2016-07-15T12:59:21.673
9796
9796
27034
answer