MySQL query hulp
Kunnen jullie me even helpen om een juiste query op te bouwen: Dit is mijn tabel met evenementen:
(Weet dat de echte tabel ongeveer 3000 evenementen bevat!)
| id | title | start | end |
| 10 | evenement1 | 2016-11-05 08:00:00 | 2016-11-05 10:00:00 |
| 11 | evenement2 | 2016-11-06 09:00:00 | 2016-11-06 12:00:00 |
| 12 | evenement3 | 2016-11-06 14:00:00 | 2016-11-06 16:00:00 |
| 13 | evenement4 | 2016-11-08 10:00:00 | 2016-11-06 12:00:00 |
| 14 | evenement5 | 2016-11-08 10:00:00 | 2016-11-06 12:00:00 |
Ik zou een lijst willen verkrijgen van alle uren / gegroepeerd per dag dat er zich een evenement voordeed.
Dus: de lijst die ik hier wil verkrijgen is:
- Datum: 2016-11-05 - Aantal uren: 2:00:00
- Datum: 2016-11-06 - Aantal uren: 5:00:00
- Datum: 2016-11-08 - Aantal uren: 2:00:00
Merk op dat op datum 6/11 2 evenementen waren, die opgeteld zijn
Merk op dat op datum 8/11 ook 2 evenementen waren maar dat die overlapten, en deze zijn dus samengeteld !!
Kunnen jullie me een query bezorgen die dit resultaat geeft?
Alvast bedankt!
Groetjes!
Gewijzigd op 15/11/2016 08:54:43 door Liefhebber Laravel
Code (php)
1
sum( end-uur - start-uur )
en :
Code (php)
1
group by start-datum
Dit lijkt te werken, ik krijg dan wel de tijd terug als een integer. Bijvoorbeeld 5:30 uren verschil wordt getoond als 53000
Bestaat er een functie om dit terug om te zetten naar 05:30:00 of moet ik de : er via SUBSTRING tussenplaatsen?
Met behulp van date_format() kan je het verfijnen.
Hier een voorbeeld van een bestand (zonder datum) met begin- en eindtijd die de totaal uren opsomt per heel uur :
Code (php)
1
SELECT *, date_format(begin,'%H') AS uur, SUM(date_format(einde,'%H') - date_format( begin, '%H')) AS uren FROM afspraken GROUP BY uur
volgende SQL geeft niet het juiste resultaat:
Code (php)
1
SELECT DATE(start) AS datum, SUM( TIME(end) - TIME(start)) as tijd FROM events GROUP BY start
Resultaat:
| datum | tijd |
| 2016-11-05 | 20000 |
| 2016-11-06 | 30000 |
| 2016-11-06 | 20000 |
| 2016-11-08 | 40000 |
- datum 06/11 komt in 2 regels voor, en dit moet opgeteld zijn
- datum 08/11 wordt als 40000 (4:00h) weergegeven terwijl dit maar 2:00 moet zijn (deze overlappen)
Toevoeging op 15/11/2016 14:04:27:
Hier eventueel de code om de tabel lokaal te importeren. Om te testen indien nodig.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) DEFAULT NULL,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `events` VALUES ('1', 'evenement1', '2016-11-05 08:00:00', '2016-11-05 10:00:00');
INSERT INTO `events` VALUES ('2', 'evenement2', '2016-11-06 09:00:00', '2016-11-06 12:00:00');
INSERT INTO `events` VALUES ('3', 'evenement3', '2016-11-06 14:00:00', '2016-11-06 16:00:00');
INSERT INTO `events` VALUES ('4', 'evenement4', '2016-11-08 10:00:00', '2016-11-08 12:00:00');
INSERT INTO `events` VALUES ('5', 'evenement5', '2016-11-08 10:00:00', '2016-11-08 12:00:00');
CREATE TABLE `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) DEFAULT NULL,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `events` VALUES ('1', 'evenement1', '2016-11-05 08:00:00', '2016-11-05 10:00:00');
INSERT INTO `events` VALUES ('2', 'evenement2', '2016-11-06 09:00:00', '2016-11-06 12:00:00');
INSERT INTO `events` VALUES ('3', 'evenement3', '2016-11-06 14:00:00', '2016-11-06 16:00:00');
INSERT INTO `events` VALUES ('4', 'evenement4', '2016-11-08 10:00:00', '2016-11-08 12:00:00');
INSERT INTO `events` VALUES ('5', 'evenement5', '2016-11-08 10:00:00', '2016-11-08 12:00:00');
Gewijzigd op 15/11/2016 13:52:12 door Liefhebber Laravel
http://www.google.nl/search?q=how+to+calculate+the+time+difference+in+mysql
Dit gefabriekt :
Code (php)
1
2
2
select *, timediff(end,start) as uren from events;
select *, date_format(start,'%Y-%c-%d') as dag, sum(timediff(end,start)) as uren from events group by dag;
select *, date_format(start,'%Y-%c-%d') as dag, sum(timediff(end,start)) as uren from events group by dag;
Gewijzigd op 15/11/2016 14:56:20 door Adoptive Solution
Deze query geeft nog steeds de foute resultaten... bij 8/11 telt hij 4 uren, maar dat zou 2 moeten zijn omdat die overlappen...
bedankt voor je tijd alvast!
van 14 tot 16 uur en van 17 tot 20 uur is gewoon een optelling
van 14 tot 16 uur en van 15 uur tot 18 uur is het verschil tussen 18 en 14.
Daar kun je mogelijk nog een mooie formule op loslaten die bepaalt of het eerste tijdstip van de 2e event kleiner is dan de eindtijd van de eerste, maar dan zit je nog weer te kijken met de situatie waarbij event 1 pas begon na event 2
en zo'n formule moet overweg kunnen met
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--------
+++++++
en
------------
+++++++
en
-------------
+++++
en
-------
+++++++
en
-----
+++++
en
----
++++
+++++++
en
------------
+++++++
en
-------------
+++++
en
-------
+++++++
en
-----
+++++
en
----
++++
en is het ook nog mogelijk dat er meer dan 2 events op een dag zijn?
Toevoeging op 15/11/2016 17:07:59:
Naast DATE_FORMAT() bestaat ook de functie HOUR()
Die is misschien wat mooier om het uur te bepalen?
BTW: gewoon GROUP BY in een query gooien, zeker als er ook nog SELECT * in de query voorkomt, leidt al gauw tot rare resultaten die soms toevallig geven wat je zocht, maar mogelijk niet altijd
bedankt voor je reactie! Inderdaad, die overlap maakt het ingewikkeld. Ik denk dat ik best alle data per dag binnenhaal, en op PHP hoogte even vergelijk met elkaar, om zo het juiste aantal uren te bepalen.
Ik vul nu een array als volgt op:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[03-11-2016] => Array
(
[start] => Array
(
[0] => 12:25:00
[1] => 14:55:00
[2] => 19:00:00
)
[end] => Array
(
[0] => 14:30:00
[1] => 17:25:00
[2] => 20:45:00
)
)
(
[start] => Array
(
[0] => 12:25:00
[1] => 14:55:00
[2] => 19:00:00
)
[end] => Array
(
[0] => 14:30:00
[1] => 17:25:00
[2] => 20:45:00
)
)
start[0] en end[0] is bv de eerste evenementtijd.
bestaat er een logica/formule om deze te vergelijken en zo het aantal actieve uren te berekenen?
NB; de uren zijn steeds gesorteerd van vroeg naar laat.
Toevoeging op 16/11/2016 09:24:33:
Heren,
ik heb net deze link gevonden: Klik hier
Hier staat een mySQL query die doet wat ik nodig heb! Maar ik krijg hem niet aangepast naar mijn tabel: hier is mijn query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select DATE(start) as date, TIME_FORMAT(SEC_TO_TIME(sum(secs)), '%h:%i')
from (select t.*,
@time := if(@sum = 0, 0, TIME_TO_SEC(TIMEDIFF(TIME(start), @prevtime))) as secs,
@prevtime := TIME(start),
@sum := @sum + isstart
from ((select DATE(start), TIME(start), 1 as isstart
from events t
) union all
(select DATE(start), TIME(end), -1
from events t
)
) t cross join
(select @sum := 0, @time := 0, @prevtime := 0) vars
order by 1, 2
) t
group by date;
from (select t.*,
@time := if(@sum = 0, 0, TIME_TO_SEC(TIMEDIFF(TIME(start), @prevtime))) as secs,
@prevtime := TIME(start),
@sum := @sum + isstart
from ((select DATE(start), TIME(start), 1 as isstart
from events t
) union all
(select DATE(start), TIME(end), -1
from events t
)
) t cross join
(select @sum := 0, @time := 0, @prevtime := 0) vars
order by 1, 2
) t
group by date;
Ik krijg de foutmelding "Unknown column 'start' in 'field list'
Aan de SQL specialisten: Wat doe ik fout?
Gewijzigd op 16/11/2016 08:29:32 door Liefhebber Laravel
In de subquery met de union heb je geen aliassen op DATE(start) en TIME(start).
Hoe dan ook zal de oplossing van SO in jouw geval niet werken, want dan moet je gaan bijhouden wanneer de datum veranderd.
Daarnaast zullen events die op andere datum beginnen dan ze eindigen niet goed door gerekend worden.
Onderstaande methode werkt met datasets:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT DATE(t.period_start) adate, SEC_TO_TIME(SUM(t.secs)) atime
FROM
(SELECT p.period_start,
CASE WHEN COUNT(e.id) > 0 THEN 900 ELSE 0 END secs
FROM
(SELECT
@begin_period := @begin_period + interval 15 MINUTE period_start,
@end_period := @begin_period + INTERVAL 14 MINUTE period_end
FROM
information_schema.COLLATIONS
CROSS JOIN
information_schema.COLLATIONS x
CROSS JOIN
(SELECT @begin_period := CURRENT_DATE - INTERVAL 6 DAY - INTERVAL 15 MINUTE) var
WHERE @begin_period + INTERVAL 29 MINUTE < CURRENT_DATE + INTERVAL 1 DAY
) p
LEFT JOIN
events e ON p.period_start < e.end_time AND p.period_end > e.start_time
GROUP BY p.period_start
) t
GROUP BY adate
FROM
(SELECT p.period_start,
CASE WHEN COUNT(e.id) > 0 THEN 900 ELSE 0 END secs
FROM
(SELECT
@begin_period := @begin_period + interval 15 MINUTE period_start,
@end_period := @begin_period + INTERVAL 14 MINUTE period_end
FROM
information_schema.COLLATIONS
CROSS JOIN
information_schema.COLLATIONS x
CROSS JOIN
(SELECT @begin_period := CURRENT_DATE - INTERVAL 6 DAY - INTERVAL 15 MINUTE) var
WHERE @begin_period + INTERVAL 29 MINUTE < CURRENT_DATE + INTERVAL 1 DAY
) p
LEFT JOIN
events e ON p.period_start < e.end_time AND p.period_end > e.start_time
GROUP BY p.period_start
) t
GROUP BY adate
De subquery p genereert een dataset (virtuele tabel) met een interval van 15 minuten van de afgelopen 7 dagen
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
+ ------------------- + ------------------- +
| period_start | period_end |
+ ------------------- + ------------------- +
| 2016-11-13 00:00:00 | 2016-11-13 00:14:00 |
| 2016-11-13 00:15:00 | 2016-11-13 00:29:00 |
| 2016-11-13 00:30:00 | 2016-11-13 00:44:00 |
| 2016-11-13 03:00:00 | 2016-11-13 03:59:00 |
.....
| 2016-11-19 22:45:00 | 2016-11-19 22:59:00 |
| 2016-11-19 23:00:00 | 2016-11-19 23:14:00 |
| 2016-11-19 23:15:00 | 2016-11-19 23:29:00 |
| 2016-11-19 23:30:00 | 2016-11-19 23:44:00 |
| 2016-11-19 23:45:00 | 2016-11-19 23:59:00 |
+ ------------------- + ------------------- +
| period_start | period_end |
+ ------------------- + ------------------- +
| 2016-11-13 00:00:00 | 2016-11-13 00:14:00 |
| 2016-11-13 00:15:00 | 2016-11-13 00:29:00 |
| 2016-11-13 00:30:00 | 2016-11-13 00:44:00 |
| 2016-11-13 03:00:00 | 2016-11-13 03:59:00 |
.....
| 2016-11-19 22:45:00 | 2016-11-19 22:59:00 |
| 2016-11-19 23:00:00 | 2016-11-19 23:14:00 |
| 2016-11-19 23:15:00 | 2016-11-19 23:29:00 |
| 2016-11-19 23:30:00 | 2016-11-19 23:44:00 |
| 2016-11-19 23:45:00 | 2016-11-19 23:59:00 |
+ ------------------- + ------------------- +
Dan een left join op events die in dat bereik plaats hebben, en via COUNT(id) wordt dan bepaald of een bereik mee geteld moet worden.
SQL Fiddle
Gewijzigd op 19/11/2016 13:18:13 door Ger van Steenderen