INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Calculating total time for occurrences, within a time band
2

Calculating total time for occurrences, within a time band

Calculating total time for occurrences, within a time band

(OP)
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

RE: Calculating total time for occurrences, within a time band

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.

CODE

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 

RE: Calculating total time for occurrences, within a time band

Hi

Not exhaustively tested, but seems to do it :

CODE --> mysql terminal

mysql> set @start=1389608400;
Query OK, 0 rows affected (0.00 sec)

mysql> set @stop=1389619000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from Graham order by 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 |
+------+------------+------------+
7 rows in set (0.00 sec)

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

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

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

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

Feherke.
feherke.ga

RE: Calculating total time for occurrences, within a time band

(OP)
Many thanks both, much better!

RE: Calculating total time for occurrences, within a time band

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)

CODE

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

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

RE: Calculating total time for occurrences, within a time band

Chris

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

RE: Calculating total time for occurrences, within a time band

@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

RE: Calculating total time for occurrences, within a time band

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close