There is a ready array of browsing data for one day.

It is necessary to distribute these views on the clock. That is, for example, at 8:00 in the morning, there were so many views, etc.

    1 answer 1

    You can use the grouping of data by the hour. Let there be a table stats with the number of hits , which were entered in during created_at (field type DATETIME )

     SELECT DATE_FORMAT(created_at, '%d.%m.%Y %H:00:00') AS hour, SUM(hits) AS hits FROM stats GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %H') ORDER BY DATE_FORMAT(created_at, '%Y-%m-%d %H')

    The GROUP BY keyword groups the sample into groups of identical values. We need to group the data by the hour. We do not have such a field in the table, each field is marked with a unique value created_at , for example, '2016-06-22 21:45:15'. However, we can form such a field dynamically using the DATE_FORMAT() function and get '2016-06-22 21'. In SQL, there is a group of aggregate functions that work in conjunction with GROUP BY and calculate results for a group. One of them is SUM() - calculating the amount. As a result, we get for each hour the sum of the hits column.

     2016-06-22 21 532 2016-06-22 20 734 ... 2016-06-21 08 234

    Using the ORDER BY sort the results either in the direct (as in the query) or in the reverse order ( DATE_FORMAT(created_at, '%Y-%m-%d %H') DESC ).

    • Can you step by step describe the actions of the request, such as the execution scheme?) - Albert Ushakov
    • @Albert corrected the answer. - cheops 7:02
    • Quickly you) Works. Many thanks) - Albert Ushakov