Calculating total time for occurrences, within a time band 2

[maharg]

Hi,

I wonder if you can help steer me in the right direction.
I have a MySQL table which shows when a latching pushbutton was pressed and when it was released, in unix time...

`id`, `pressed`, `released`
128, 1389608227, 1389608232
129, 1389608352, 1389608559
130, 1389608794, 1389608871
131, 1389608884, 1389608898
132, 1389609086, 1389609195
133, 1389609568, 1389609788
134, 1389624513, 1389624517

I am trying to show the total amount of time the button was pressed, within a specified time range, for example
between 1389608400 and 1389619000. The time span can start and stop within a 'pressed' period our outside (between rows).

I have managed to achieve a working result with numerous stitched-together php and mysql combinations, which, although
it does the job, I am sure is over-complicated and will be a pain to maintain, as I have up to 150 buttons to monitor!

Any guidance on an efficient MySQL approach would be much appreciated.

Thanks

Graham

 

[jpadie]

not knowing exactly what you are looking for here (button pressed and released in the thresholds, or either pressed or released within the boundaries), here is a query which hopefully shows the approach for each of these criteria.

if you just want one of these criteria then move it from the case down to a where.

select  sum(
            case
                when released between 1389608400 and 1389619000 and pressed between 1389608400 and 1389619000
                then released - pressed
                else 0
                end
            ) as "pressed and released",
        sum(
            case
                when released between 1389608400 and 1389619000 and pressed not between 1389608400 and 1389619000
                then released - pressed
                else 0
                end
            ) as "released only", 
        sum(
            case
                when released not between 1389608400 and 1389619000 and pressed between 1389608400 and 1389619000
                then released - pressed
                else 0
                end
            ) as "pressed only"
from buttons

 

[feherke]

Hi

Not exhaustively tested, but seems to do it :

[blue]mysql>[/blue] [b]set[/b] [teal]@start[/teal]=1389608400;
[gray]Query OK, 0 rows affected (0.00 sec)[/gray]

[blue]mysql>[/blue] [b]set[/b] [teal]@stop[/teal]=1389619000;
[gray]Query OK, 0 rows affected (0.00 sec)[/gray]

[blue]mysql>[/blue] [b]select[/b] * [b]from[/b] Graham [b]order[/b] [b]by[/b] id;
+------+------------+------------+
| id   | pressed    | released   |
+------+------------+------------+
|  128 | 1389608227 | 1389608232 |
|  129 | 1389608352 | 1389608559 |
|  130 | 1389608794 | 1389608871 |
|  131 | 1389608884 | 1389608898 |
|  132 | 1389609086 | 1389609195 |
|  133 | 1389609568 | 1389609788 |
|  134 | 1389624513 | 1389624517 |
+------+------------+------------+
[gray]7 rows in set (0.00 sec)[/gray]

[blue]mysql>[/blue] [b]select[/b] * [b]from[/b] Graham [b]where[/b] pressed [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]or[/b] released [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]order[/b] [b]by[/b] id;
+------+------------+------------+
| id   | pressed    | released   |
+------+------------+------------+
|  129 | 1389608352 | 1389608559 |
|  130 | 1389608794 | 1389608871 |
|  131 | 1389608884 | 1389608898 |
|  132 | 1389609086 | 1389609195 |
|  133 | 1389609568 | 1389609788 |
+------+------------+------------+
[gray]5 rows in set (0.00 sec)[/gray]

[blue]mysql>[/blue] [b]select[/b] id,[b]case[/b] [b]when[/b] pressed<[teal]@start[/teal] [b]then[/b] [teal]@start[/teal] [b]else[/b] pressed [b]end[/b] pressed_cut,[b]case[/b] [b]when[/b] released>[teal]@stop[/teal] [b]then[/b] [teal]@stop[/teal] [b]else[/b] released [b]end[/b] released_cut [b]from[/b] Graham [b]where[/b] pressed [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]or[/b] released [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]order[/b] [b]by[/b] id;
+------+-------------+--------------+
| id   | pressed_cut | released_cut |
+------+-------------+--------------+
|  129 |  1389608400 |   1389608559 |
|  130 |  1389608794 |   1389608871 |
|  131 |  1389608884 |   1389608898 |
|  132 |  1389609086 |   1389609195 |
|  133 |  1389609568 |   1389609788 |
+------+-------------+--------------+
[gray]5 rows in set (0.00 sec)[/gray]

[blue]mysql>[/blue] [b]select[/b] id,[b]case[/b] [b]when[/b] released>[teal]@stop[/teal] [b]then[/b] [teal]@stop[/teal] [b]else[/b] released [b]end[/b]-[b]case[/b] [b]when[/b] pressed<[teal]@start[/teal] [b]then[/b] [teal]@start[/teal] [b]else[/b] pressed [b]end[/b] difference [b]from[/b] Graham [b]where[/b] pressed [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]or[/b] released [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]order[/b] [b]by[/b] id;
+------+------------+
| id   | difference |
+------+------------+
|  129 |        159 |
|  130 |         77 |
|  131 |         14 |
|  132 |        109 |
|  133 |        220 |
+------+------------+
[gray]5 rows in set (0.00 sec)[/gray]

[blue]mysql>[/blue] [b]select[/b] sum([b]case[/b] [b]when[/b] released>[teal]@stop[/teal] [b]then[/b] [teal]@stop[/teal] [b]else[/b] released [b]end[/b]-[b]case[/b] [b]when[/b] pressed<[teal]@start[/teal] [b]then[/b] [teal]@start[/teal] [b]else[/b] pressed [b]end[/b]) difference [b]from[/b] Graham [b]where[/b] pressed [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal] [b]or[/b] released [b]between[/b] [teal]@start[/teal] [b]and[/b] [teal]@stop[/teal];
+------------+
| difference |
+------------+
|        579 |
+------------+
[gray]1 row in set (0.00 sec)[/gray]

Feherke.
feherke.ga

 

[maharg]

Many thanks both, much better!

 

[ChrisHunt]

The two examples so far would ignore cases where the button was pressed before the start and released after the end. Shouldn't they still be counted?

How about something like this? (not tested)

SELECT SUM( LEAST(@stop,released) - GREATEST(@start,pressed) )
WHERE  pressed <= @stop
AND    released >= @start

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jpadie]

Chris

i thought my query would capture that scenario (in the second case). have i made a mistake?

 

[ChrisHunt]

@jpadie Either you've made a mistake in your query, or I've made a mistake in what the OP wants.

What happens if pressed < 1389608400 and released > 1389619000 ? Neither figure is between 1389608400 and 1389619000, so none of your cases will be triggered. The button was pressed before the time window, held down throughout that window, and released after the end of the window. I think that should still count - though admittedly the OP isn't all that clear on his requirements.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jpadie]

yes - I see. As you say, impossible to know without a precise articulation of the use case.

sum(
            case
                when pressed <= 1389608400 and released >= 1389619000 
                then released - pressed
                else 0
                end
            ) as "active over at least entire period"