How to split a sample of MySQL on periods?

There is a table of transactions (the transaction date and its value). I need to graph transactions, breaking them into intervals. For example I want to see the sum of transactions for the previous day, breaking them down into hour increments. Ie show the amount of transactions from 00:00 to 01:00 27/03, c 01:01 to 02:00 27/03 etc to 00:00 28/03.

I use this query:

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


The problem is that it displays some strange value date:

| 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 |


As you can see, they are not located at precise intervals. What is wrong in the query?
March 19th 20 at 08:56
2 answers
March 19th 20 at 08:58
Solution
In the end, I found this query, which groups the way it should:

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
March 19th 20 at 09:00
For you need to add another field, or use current if it means a task, without minutes.
DATE_FORMAT(tstamp, '%Y-%m-%d %H') as dt,

And check your conditions.
00:00 to 01:00 01:01 to 02:00 there is a mistake.

Find more questions by tags MySQL