HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

List minutes instead of grouping by them

Submitted by: @import:stackexchange-dba··
0
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:

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

Solution

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.