patternMinor
List minutes instead of grouping by them
Viewed 0 times
groupinginsteadminuteslistthem
Problem
Looking how to list minutes 1 to 120 from a 2 hour interval timestamp field instead of getting each minute from both hours together.
What I want is something like this:
I was doing the GROUP BY but I'm only getting 60 rows collecting the result of each hour.
sorry if it's silly but I can't get the keyword to find it out ... 'group by minutes without collecting, list by minutes query mysql, order more than 60 minutes' were some searches I made.
I'm using this for now, of course I know it doesn't work but I want to initialize from something,
What I want is something like this:
min COUNT(*)
1 300
2 320
3 294
... ...
... ...
61 341
62 210
63 258
... ...
... ...
119 196
120 347I was doing the GROUP BY but I'm only getting 60 rows collecting the result of each hour.
min COUNT(*)
1 641
2 530
3 552
... ...
... ...sorry if it's silly but I can't get the keyword to find it out ... 'group by minutes without collecting, list by minutes query mysql, order more than 60 minutes' were some searches I made.
I'm using this for now, of course I know it doesn't work but I want to initialize from something,
SELECT MIN(timestamp) AS min, COUNT(field) FROM table WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY minSolution
You can use the timestampdiff function. You need to use the start date as a second argument, like this:
SELECT TIMESTAMPDIFF(MINUTE,'2014-06-12 10:00:00', timestamp) AS mins,
COUNT(field)
FROM table
WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY mins;Code Snippets
SELECT TIMESTAMPDIFF(MINUTE,'2014-06-12 10:00:00', timestamp) AS mins,
COUNT(field)
FROM table
WHERE timestamp BETWEEN '2014-06-12 10:00:00' AND '2014-06-12 12:00:00' GROUP BY mins;Context
StackExchange Database Administrators Q#68185, answer score: 3
Revisions (0)
No revisions yet.