Как разбить выборку из MySQL на периоды?

Question

[Андрей Шульц]

Есть таблица транзакций (дата транзакции и ее значение). Мне надо построить график транзакций, разбив их на интервалы. Например я хочу посмотреть суммы транзакций за предыдущий день, разбив их на часовой интервал. Т.е показать сумму транзакций с 00:00 до 01:00 27/03, c 01:01 до 02:00 27/03 и т.д до 00:00 28/03.

Я использую такой запрос:

SELECT id, DATE_FORMAT(tstamp, '%Y-%m-%d %H:%i') as dt, sum(amount) as amount
FROM transactions
GROUP BY time_to_sec(tstamp)- time_to_sec(tstamp)%(60*60)
ORDER BY dt DESC

Проблема в том, что он выводит какие то странные значения даты:

| 2018-03-27 19:22 | 103112.02551724651 |
| 2018-03-27 21:45 | 60061.116740413505 |
| 2018-03-28 01:16 | 103629.88987819279 |
| 2018-03-28 04:55 | 158171.05616502554 |
| 2018-03-28 05:00 | 107739.29807474681 |
| 2018-03-28 06:25 |  106808.2803523091 |
| 2018-03-28 07:26 | 158702.58691193615 |
| 2018-03-28 08:05 |  143791.8812470863 |
| 2018-03-28 09:39 | 126213.54103553489 |
| 2018-03-28 10:09 | 152209.51851207032 |
| 2018-03-28 11:21 | 145477.54706805685 |
| 2018-03-28 13:34 | 127102.92891048045 |
| 2018-03-28 14:27 | 121832.60294653267 |
| 2018-03-28 17:13 | 103144.06730868602 |
| 2018-03-29 00:13 | 48084.689964825106 |
| 2018-03-29 02:35 | 148910.04249381932 |
| 2018-03-29 03:16 |   171725.706533219 |
| 2018-03-29 12:37 |  141955.3731104362 |
| 2018-03-29 15:50 | 130090.14078938986 |
| 2018-03-29 16:33 |  98498.52734681964 |
| 2018-03-29 18:40 |  51951.85931785985 |
| 2018-03-29 22:19 | 65228.564068534484 |
| 2018-03-29 23:20 |  41622.47713425062 |
| 2018-04-02 20:00 |  68994.59391487371 |

Как видно, они не расположены в четких интервалах. Что не так в запросе?

Comments

[Митя ТоДаСё]

не странные, вы их в секунды переводите time_to_sec
скорректируйте sql запрос, чтобы он правильно по дате сортировал, при переборке результатов, создавайте переменную, например с номером месяца, и проверяйте ее, изменилась или нет, массивы вам в помощь.

Т.е. смысл такой, создаете массив, для каждого периода, туда заносите информацию, а периоды определяете как я описал выше, например по номеру месяц как-то так $msv[1][sum] грубо говоря это массив содержащий в себе сумму всех транзакций за январь

Answer 1

[Андрей Шульц]

В итоге нашел вот такой запрос, который группирует так, как надо:

SELECT 
    FROM_UNIXTIME( TRUNCATE( UNIX_TIMESTAMP( tstamp )/900, 0 ) * 900 ) as h,
    AVG( amount ) as v
FROM 
    transactions
GROUP BY 
   h
ORDER BY 
   h desc
LIMIT
    24

Answer 2

[Dmitry Bay]

Для вас достаточно добавить еще одно поле, либо использовать текущее, если подразумевается задачей, без минут.
DATE_FORMAT(tstamp, '%Y-%m-%d %H') as dt,

И проверяйте ваши условия.
00:00 до 01:00 c 01:01 до 02:00 тут ошибка.