List minutes instead of grouping by them

Question

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:

min      COUNT(*)  
1        300
2        320
3        294
...      ...
...      ...
61       341
62       210
63       258
...      ...
...      ...
119      196
120      347

I 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 min

Answer 1

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;

Answer 2

A thought struck me - timestamp (in MySQL anyway) is a Unix epoch - i.e. number of seconds since 01/01/1970 which you can convert to minutes. You have to subtract the number of minutes between your two dates and group by that. Something like this perhaps - post data (DDL + DML or onto SQLFiddle) and I'll test it. SELECT TIMESTAMPDIFF(MINUTE, end_timestamp, begin_timestamp) AS minutes, COUNT(your_field) from your_table GROUP BY minutes ORDER BY minutes ASC;