Is there a way to group by one minute periods in MySQL using unix timestamps?

I have a MariaDB database holding some data for a game and I wanted to use Grafana to graph out how many rows have been inserted every minute. I’m using a Node.js script to fetch the data every minute from the game’s Public API and insert it into my db, however I can’t figure out how to get MySQL to output something that works with Grafana. What I need it to do is output a table with a column for the number of new inserts and the time at the start of the one minute window. My DB looks something like this:

 --------------------------------------------------------------------
| auction_id | seller | buyer | timestamp | price | bin | item_bytes |
 --------------------------------------------------------------------

I have a working query for returning the number of new inserts for the current data supplied by the API, however I can’t figure out how to get the historical data for the past minutes. The output I’m looking for would look something like this:

 -----------------------------
| new inserts |   timestamp   |
 -----------------------------
|     234     | 1625373706053 |
 -----------------------------
|     684     | 1625373666053 |
 -----------------------------
|     720     | 1625373626053 |
 -----------------------------
|     403     | 1625373586053 |
 -----------------------------

Notice how the timestamp goes down by 60,000 every row, which is equivalent to 60 seconds, or a minute. I have tried using GROUP BY and almost every other solution I could find on StackOverflow and other sites, however it still doesn’t work.

Please don’t hesitate to comment if I wasn’t clear enough.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You may use UNIX_TIMESTAMP to convert your milliseconds-since-epoch values into a bona fide datetime. Then, aggregate by minute to get the counts:

SELECT
    COUNT(*) AS `new inserts`,
    FROM_UNIXTIME(timestamp / 1000, '%Y-%m-%d %H:%i') AS ts_minute
FROM yourTable
GROUP BY ts_minute
ORDER BY ts_minute;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x