I have a table with several million entries. I want to find out when the maximum number of entries that occur within a given minute during a year(2018).
My initial strategy was to find out which month had the largest number of entries. Like this:
--Month
SELECT date_part('month', create_time), count(*)
FROM mydb.mytable
WHERE date_part('year', create_time) = 2018
GROUP BY date_part('month', create_time)
This would then give me a certain month during the year with maximum. I would the proceed with day, hour month and eventually end up with e.g.:
--Minute
SELECT date_part('minute', create_time), count(*)
FROM mydb.mytable
WHERE date_part('year', create_time) = 2018
AND date_part('month', create_time) = 6
AND date_part('day', create_time) = 19
AND date_part('hour', create_time) = 11
GROUP BY date_part('minute', create_time)
ORDER BY count DESC
With this strategy I found that the max number of entries per minute would be e.g. 250.
However this has turned out to be a wrong strategy.
By coincidence I found out that one of the months that had significantly less entries per month actually had a higher entry rate per minute e.g. 700.
So the question is, how can I find the maximum number of entries per minute for a year, without having to "probe" my way around?
Truncate the time to the minute, group by that, and count:
SELECT
date_trunc('minute', create_time),
count(*)
FROM mydb.mytable
GROUP BY 1
ORDER BY 2 DESC