SQL Timestamp – List Minutes Instead of Grouping by Them

timestamp

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

Best Answer

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;

PROPOSED QUERY

SELECT B.* FROM
(SELECT MAX(changes1.ID) ID,changes1.issue_key
FROM  `issue_changes` AS changes1
INNER JOIN  `issues` AS issue1 ON issue1.`ID` = changes1.`issue_key` 
WHERE issue1.`project_key` =  "bug_tracker"
AND changes1.`change_field` =  "Status"
AND changes1.`change_time` <  "1400110321"
GROUP BY changes1.issue_key) A
LEFT JOIN issue_changes B USING (ID);

SAMPLE DATA

drop database if exists jamie;
create database jamie;
use jamie

projects

create table projects
(
    ID int not null auto_increment,
    project_key varchar(32),
    primary key (ID)
);
insert into projects (project_key) values ('bug_tracker');

issues

create table issues
(
    ID int not null auto_increment,
    project_key varchar(32),
    issue_key varchar(32),
    primary key (ID),
    KEY (project_key)
);
insert into issues (project_key,issue_key) values
('bug_tracker','bug_tracker-1'),
('bug_tracker','bug_tracker-2'),
('bug_tracker','bug_tracker-3');

issue_changes

create table issue_changes
(
    ID int not null auto_increment,
    change_by    varchar(32),
    change_time  bigint not null,
    issue_key    int not null,
    change_field varchar(32),
    change_value varchar(32),
    primary key (ID)
);
insert into issue_changes
(change_by,change_time,issue_key,change_field,change_value) values
('johny_vegas'        ,1346422320,1,'Status','Created         '),
('edward_scissorhands',1346497920,1,'Status','Open            '),
('harry_potter'       ,1346546460,1,'Status','Resolved        '),
('harry_potter'       ,1346682780,1,'Status','Closed          '),
('harry_potter'       ,1346252021,2,'Status','Created         '),
('harry_potter'       ,1346252023,2,'Status','Open            '),
('cmdr_data'          ,1346338440,2,'Status','Owned           '),
('luke_skywalker'     ,1346404980,2,'Status','Resolved        '),
('johny_rico'         ,1346419560,2,'Status','Open            '),
('johny_rico'         ,1346419560,2,'Status','Waive Requested '),
('edward_scissorhands',1346433300,2,'Status','Open            '),
('edward_scissorhands',1346433300,2,'Status','Waive Requested '),
('edward_scissorhands',1346433300,2,'Status','Waived          '),
('roger_rabit'        ,1346252052,3,'Status','Created         '),
('roger_rabit'        ,1346252054,3,'Status','Open            '),
('roger_rabit'        ,1346255700,3,'Status','Owned           '),
('cmdr_data'          ,1346261220,3,'Status','Resolved        '),
('luke_skywalker'     ,1346405040,3,'Status','Open            '),
('johny_vegas'        ,1346423460,3,'Status','Owned           ');

PROPOSED QUERY EXECUTED

mysql> SELECT B.* FROM
    -> (SELECT MAX(changes1.ID) ID,changes1.issue_key
    -> FROM  `issue_changes` AS changes1
    -> INNER JOIN  `issues` AS issue1 ON issue1.`ID` = changes1.`issue_key`
    -> WHERE issue1.`project_key` =  "bug_tracker"
    -> AND changes1.`change_field` =  "Status"
    -> AND changes1.`change_time` <  "1400110321"
    -> GROUP BY changes1.issue_key) A
    -> LEFT JOIN issue_changes B USING (ID);
+------+---------------------+-------------+-----------+--------------+------------------+
| ID   | change_by           | change_time | issue_key | change_field | change_value     |
+------+---------------------+-------------+-----------+--------------+------------------+
|    4 | harry_potter        |  1346682780 |         1 | Status       | Closed           |
|   13 | edward_scissorhands |  1346433300 |         2 | Status       | Waived           |
|   19 | johny_vegas         |  1346423460 |         3 | Status       | Owned            |
+------+---------------------+-------------+-----------+--------------+------------------+
3 rows in set (0.00 sec)

mysql>

EPILOGUE

The main part of the query is the subquery because it retrieves the maximum ID for each issue_key

SELECT MAX(changes1.ID) ID,changes1.issue_key
FROM  `issue_changes` AS changes1
INNER JOIN  `issues` AS issue1 ON issue1.`ID` = changes1.`issue_key` 
WHERE issue1.`project_key` =  "bug_tracker"
AND changes1.`change_field` =  "Status"
AND changes1.`change_time` <  "1400110321"
GROUP BY changes1.issue_key

Then, I take the result of the subquery and LEFT JOIN the ID to issue_changes

Best Answer

Related Solutions

How to get the difference in minutes from 2 timestamp columns

Mysql – Select the last logged change before a specified time from a change list