Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
count time interval
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
drop view if exists overlaped_activities; drop table if exists incidentes; drop table if exists calendar; drop procedure if exists filldates; DROP TABLE IF EXISTS incidentes; CREATE TABLE IF NOT EXISTS incidentes ( start datetime DEFAULT NULL, end datetime DEFAULT NULL, status varchar(20) ); INSERT INTO incidentes (start, end, status) VALUES ('2019-09-19 12:10:30', '2019-09-19 14:10:30', 'fechado'), ('2019-09-19 22:10:30', '2019-09-20 01:10:30', 'fechado'), ('2019-09-20 01:05:30', '2019-09-20 02:10:30', 'fechado'), ('2019-09-20 01:05:30', null, 'aberto'); CREATE TABLE if not exists calendar ( mdate date PRIMARY KEY NOT NULL ); CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE) BEGIN DECLARE adate date; WHILE dateStart <= dateEnd DO SET adate = (SELECT mdate FROM calendar WHERE mdate = dateStart); IF adate IS NULL THEN BEGIN INSERT INTO calendar (mdate) VALUES (dateStart); END; END IF; SET dateStart = date_add(dateStart, INTERVAL 1 DAY); END WHILE; END; CALL filldates('2019-09-01','2019-09-22'); create view overlaped_activities as SELECT status, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time FROM ( SELECT x.status, x.start, min(y.end) AS end FROM incidentes AS x JOIN incidentes AS y ON x.status = y.status AND x.start <= y.end AND x.status = 'fechado' AND NOT EXISTS ( SELECT 1 FROM incidentes AS z WHERE y.status = z.status AND y.end >= z.start AND y.end < z.end ) WHERE NOT EXISTS ( SELECT 1 FROM incidentes AS u WHERE x.status = u.status AND x.start > u.start AND x.start <= u.start ) GROUP BY x.status, x.start ) AS v GROUP BY status, end; #final ########################### select status, date_activity, sum(minutes) min_activity, sum(hour) hour_activity from ( select status, date(start) date_activity, if(date(start) = date(end), time_to_sec(timediff(end, start)) / 60, (1440 - time_to_sec(time(start)) / 60)) minutes, if(date(start) = date(end), time_to_sec(timediff(end, start)) / 3600, (24 - time_to_sec(time(start)) / 3600)) hour from overlaped_activities UNION ALL select status, date(end) date_activity, if(date(start) = date(end), 0, time_to_sec(time(end)) / 60) minutes, if(date(start) = date(end), 0, time_to_sec(time(end)) / 3600) hour from overlaped_activities UNION ALL select status, mdate date_activity, sum(1440) minutes, sum(24) hour from calendar join overlaped_activities on calendar.mdate > date(start) and calendar.mdate < date(end) where datediff(end, start) > 1 group by status, mdate ) act where date_activity between '2019-09-19' and '2019-09-19' group by status, date_activity; drop table if exists incidentes; drop table if exists calendar; drop view if exists overlaped_activities; drop procedure if exists filldates;
absolute service time: 0,43 sec
edit mode
|
history
|
discussion
status
date_activity
min_activity
hour_activity
1
fechado
19.09.2019 00:00:00
229,5000
3,8250