MySQL implements the data count by minute and second

In MySQL, the transaction volume per second, per minute, per 5 minutes, per 10 minutes, per 30 minutes can be usedGROUP BYand MySQL time-handling functions to implement.

Assume that the transaction record table name istransactions, the transaction time field istransaction_time, and count the transaction volume for each time period.

1. Trading volume per second

SELECT 
    DATE_FORMAT(transaction_time, '%Y-%m-%d %H:%i:%s') AS time_sec, 
    COUNT(*) AS transaction_count
FROM transactions
GROUP BY time_sec
ORDER BY time_sec DESC;

DATE_FORMAT(transaction_time, '%Y-%m-%d %H:%i:%s') Format time to seconds.
COUNT(*)Count the number of transaction records per second.

2. Trading volume per minute

SELECT 
    DATE_FORMAT(transaction_time, '%Y-%m-%d %H:%i') AS time_min, 
    COUNT(*) AS transaction_count
FROM transactions
GROUP BY time_min
ORDER BY time_min DESC;

DATE_FORMAT(transaction_time, '%Y-%m-%d %H:%i') Format time to minutes.

3. Trading volume every 5 minutes

SELECT 
    CONCAT(DATE_FORMAT(transaction_time, '%Y-%m-%d %H:'), 
           LPAD(FLOOR(MINUTE(transaction_time) / 5) * 5, 2, '0')) AS time_5min, 
    COUNT(*) AS transaction_count
FROM transactions
GROUP BY time_5min
ORDER BY time_5min DESC;

FLOOR(MINUTE(transaction_time) / 5) * 5 Divide the time into 5-minute intervals.
LPADUsed to ensure that the minutes are displayed in two digits.

4. Trading volume every 10 minutes

SELECT 
    CONCAT(DATE_FORMAT(transaction_time, '%Y-%m-%d %H:'), 
           LPAD(FLOOR(MINUTE(transaction_time) / 10) * 10, 2, '0')) AS time_10min, 
    COUNT(*) AS transaction_count
FROM transactions
GROUP BY time_10min
ORDER BY time_10min DESC;

FLOOR(MINUTE(transaction_time) / 10) * 10 Divide the time into 10-minute intervals.

5. Trading volume every 30 minutes

SELECT 
    CONCAT(DATE_FORMAT(transaction_time, '%Y-%m-%d %H:'), 
           LPAD(FLOOR(MINUTE(transaction_time) / 30) * 30, 2, '0')) AS time_30min, 
    COUNT(*) AS transaction_count
FROM transactions
GROUP BY time_30min
ORDER BY time_30min DESC;

FLOOR(MINUTE(transaction_time) / 30) * 30 Divide the time into 30-minute intervals.

Combined with WHERE filtering time range

Can be passed in the queryWHEREConditions to limit the time range of statistics.

For example, count the transaction volume per minute of the last day:

SELECT 
    DATE_FORMAT(transaction_time, '%Y-%m-%d %H:%i') AS time_min, 
    COUNT(*) AS transaction_count
FROM transactions
WHERE transaction_time >= NOW() - INTERVAL 1 DAY
GROUP BY time_min
ORDER BY time_min DESC;

These queries count transaction volumes per second, per minute, per 5 minutes, per 10 minutes and per 30 minutes respectively.

If you need to expand to other time periods, just adjustFLOOR(MINUTE(transaction_time))The time interval is enough.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.