4

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
  • Where is your query? – ypercubeᵀᴹ Jun 12 '14 at 23:20
  • There is it, thought it wasn't necessary – tachomi Jun 12 '14 at 23:23
  • Give us DDL and DML with sample data - maybe SQLFiddle?, desired result and logic you used to derive the result. The server (and version) you're using would also be helpful. If it's over two hours, do you want something like SELECT HOUR(my_timestampt) as my_hour, min(my_timestamp) as my_hour ... group by HOUR, MIN? Also, it's good practice to avoid using keywords in your SQL. – Vérace Jun 12 '14 at 23:47
  • The desired result is a list from 1 to 120 with the count of the field of each minute. I don't want to only have 60 minute where the minute 1 includes the minute 1 of each hour, the GROU BY hour, minute is a good idea to get it, but I wanna know if the list could be possible like counting the minutes. – tachomi Jun 13 '14 at 0:02
3

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;
|improve this answer|||||
  • Tank you very much, you got it :D – tachomi Jun 13 '14 at 13:10
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;

|improve this answer|||||
  • thanks for your help, this query takes a while (almost 1 min) while cha's answer takes over 5 secs. Besides, the result of this query is not printing my desired result min / -120 , COUNT(*) / 66508033 – tachomi Jun 13 '14 at 13:09
  • 1
    @Cha's is undoubtedly the correct one - I didn't have MySQL running on my machine (just installed Ubuntu 14.04 LTS - was configuring it last night), so the sign of the answer is incorrect &c. For the next time, if you want better answers, post the DDL and some sample DML, your desired result and the logic you used to get that result. You could also set up an SQLFiddle demo. And always post your server and version. "SQL" as a tag is very vague. HTH. – Vérace Jun 13 '14 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.