I have a database that collects some data at a pretty high frequency and records the timestamp of each entry (stored as epoch time, not mysql datetime). E.g.
timestamp rssi sender
-------------------------------------------
1592353967.171600 -67 9EDA3DBFFE15
1592353967.228000 -67 9EDA3DBFFE15
1592353967.282900 -62 E2ED2569BE2
1592353971.892600 -67 9EDA3DBFFE15
1592353973.962900 -61 2ADE2E4597B2
...
My goal is to be able to get a count of all the rows within 15 minute time intervals, which after research can be obtained with GROUP BY. Ideally, the final output would look like this
timestamp count
------------------------------
1592352000 (8:00pm EST) 38
1592352900 (8:15pm EST) 22
1592353800 (8:30pm EST) 0 <----- Important, must include periods with 0 entries
1592354700 (8:45pm EST) 61
...
I'm mainly having issues with 2 things here: 1. Being able to show the timestamp intervals in the results 2. Showing intervals with 0 rows within a time period
My current attempt is as follows, and it's on the right track because the data in there time periods is actually correct
Showing rows 0 - 23 (24 total, Query took 0.0264 seconds.)
SELECT count(*) AS total, MINUTE(FROM_UNIXTIME(timestamp)) AS minute
FROM requests
WHERE timestamp >= 1592352000 AND timestamp < (1592352000 + 3600)
GROUP BY MINUTE(FROM_UNIXTIME(timestamp))
total minute
55 32
89 33
64 34
55 35
87 36
82 37
90 38
69 39
74 40
47 41
89 42
53 43
71 44
87 45
72 46
83 47
86 48
83 49
113 50
76 51
77 52
88 53
81 54
28 55
This data is correct, however there are periods that are not shown here (the first 30 minutes of this hour has no data and thus the first minute entry starts at 32). Also attempting to get every 15 minutes by using
SELECT count(*) AS total, MINUTE(FROM_UNIXTIME(timestamp)) AS minute
FROM requests
WHERE timestamp >= 1592352000 AND timestamp < (1592352000 + 3600)
GROUP BY MINUTE(FROM_UNIXTIME(timestamp)) DIV 15
#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wave_master.requests.timestamp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Any help would be greatly appreciated.
You can use a "numbers" table to generate the 15-minute periods within the hour (0-3), and then LEFT JOIN that to your counts, grouped by 15 minute periods, using COALESCE to replace NULL values with 0:
SELECT periods.period * 900 + 1592352000 AS timestamp,
FROM_UNIXTIME(periods.period * 900 + 1592352000) AS time,
COALESCE(counts.total, 0) AS total
FROM (
SELECT 0 period
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
) periods
LEFT JOIN (
SELECT (timestamp - 1592352000) DIV 900 AS period,
COUNT(*) AS total
FROM requests
WHERE timestamp >= 1592352000 AND timestamp < 1592352000 + 3600
GROUP BY period
) counts ON counts.period = periods.period
Output (for the data in your question plus a couple of other values):
timestamp time total
1592352000 2020-06-17 00:00:00 1
1592352900 2020-06-17 00:15:00 1
1592353800 2020-06-17 00:30:00 5
1592354700 2020-06-17 00:45:00 0