How do I store date data in DB after aggregating it in SQL (with SQL in this part)?

RubyonRails implements the process of aggregating (by time) articles.

Currently, I would like to run SQL such as the following, save the aggregated results to DB, and display them on the web screen.

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;

tabulation table

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

I'm still thinking about saving to DB after running SQL, so I haven't been able to implement it yet. Currently, the problem is the hour(datetipe) part.

This is because when aggregating, date_format(created_at, '%Y-%m-%d-%H') is used. You will not be able to save it to DB.

I will explain the reason why it was shaped like %Y-%m-%d-%H later, but I wanted to include characters recognizing the break in order to make it look like Wednesday, May 29, 2016.

There are two ways I thought about that.

1) change the hour to the string
2) Do not use date_format(created_at, '%Y-%m-%d-%H') when executing SQL, but successfully aggregate by time

1) When saving in the string type and displaying the screen, use the controller
I think it would be good to add the process of converting from string to datetime type.
(The reason for the conversion is to get the day of the week.)

Also, on the screen side,
Wednesday, May 29, 2016
I would like to make it look like this.

1) I think I can do it if I try my best, but I feel uncomfortable saving the date with the string type.

However, I am having trouble with 2) because I do not know how to do it.

Based on the above, I would appreciate your advice.

Thank you for your cooperation.

The subject matter was resolved by metropolis's response.

In order to aggregate by time, we have a way to convert the aggregation key from DATE_FORMAT to %Y-%m-%d%H:00:00 and cast it into a DATETIME type.

However, in the future, if you want to count by month, the tally key will look like (2016-04).
In this case, I thought it would be impossible to cast DATETIME or DATE.

In this case, is there no choice but to save it as a String type?

ruby-on-rails ruby mysql sql

2022-09-30 14:09

1 Answers

For PostgreSQL, date_trunc
https://www.postgresql.jp/document/9.5/html/functions-datetime.html#functions-datetime-trunc
I think you can get the desired value by aggregating it in the column you ran

I will also leave a SQL sample.
http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/9925

 WITH ARTICLES AS(
SELECT
  id, created_at
FROM(
  VALUES
    (1, TIMESTAMP'2016-07-1500:00:00'),
    (2, TIMESTAMP'2016-07-1500:01:00'),
    (3, TIMESTAMP'2016-07-1501:00:00'),
    (4, TIMESTAMP'2016-07-1501:01:00'),
    (5, TIMESTAMP'2016-07-1501:02:00'),
    (6, TIMESTAMP'2016-07-1502:00:00')
AST(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
;

If there are things that don't work well related to aggregation, I think using window function can solve many problems.
Try it if you have a chance
(But I don't think they will generate SQLbuilders for Rails...)

Mysql seems to have no choice but to do TIME_FORMAT or take the remainder of TO_SECONDS...
http://sqlfiddle.com/#!9/9eecb7d/69182

SELECT
  sec,
  FROM_UNIXTIME(sec*3600-TO_SECONDS('1970-01-0100:00:00') as my_date_time,
  count(sec)AS count
FROM(
  SELECT
    id,
    updated_at,
    CAST(TO_SECONDS(updated_at)/3600AS SIGNED INTEGER) ASsec,
    TIME_FORMAT(updated_at, '%Y%m%d%h')ASymdh
  FROM(
    SELECT1 AS id, TIMESTAMP'2016-07-1500:00:00'AS updated_at FROM DUAL
    UNION ALL SELECT 2, TIMESTAMP'2016-07-1500:01:00' FROM DUAL
    UNION ALL SELECT 3, TIMESTAMP'2016-07-1501:00' FROM DUAL
    UNION ALL SELECT 4, TIMESTAMP'2016-07-1501:01:00' FROM DUAL
    UNION ALL SELECT 5, TIMESTAMP'2016-07-1501:02:00' FROM DUAL
    UNION ALL SELECT 6, TIMESTAMP'2016-07-1502:00' FROM DUAL
  AST
AST
GROUP BY
  sec
ORDER BY
  sec
;

2022-09-30 14:09

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656