CODEWITHSUNDEEP
mysqlsql
g-pane
g-pane

Reputation: 61

MySQL calculate ON-OFF time from datetime

I have a simple schema

CREATE TABLE Stack (
  `id` INTEGER,
  `datetime` DATETIME,
  `status` VARCHAR(3)
);

INSERT INTO Stack
  (`id`, `datetime`, `status`)
VALUES
  ('1', '2020-01-01 10:00:00', 'ON'),
  ('2', '2020-01-01 11:00:00', 'ON'),
  ('3', '2020-01-01 12:00:00', 'OFF'),
  ('4', '2020-01-01 12:30:00', 'OFF'),
  ('5', '2020-01-01 15:00:00', 'ON'),
  ('6', '2020-01-01 16:00:00', 'ON'),
  ('7', '2020-01-01 17:30:00', 'OFF'),
  ('8', '2020-01-01 18:00:00', 'ON');

Pratically is a device. This device send me datetime and the status of a machine. I need to get/calculate the ON-OFF time of the machine in format like 00:00:00, checking every line of the DB.

I have made a DB Fiddle for simplify: https://www.db-fiddle.com/f/dpypuY7qc8apmJfC3qrovf/2

Any help? Thanks

Upvotes: 2

Views: 638

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

You say this is about one device. This is the table you have:

+----+---------------------+--------+
| ID |  DATETIME           | STATUS |
+----+---------------------+--------+
|  1 | 2020-01-01 10:00:00 | ON     |
|  2 | 2020-01-01 11:00:00 | ON     |
|  3 | 2020-01-01 12:00:00 | OFF    |
|  4 | 2020-01-01 12:30:00 | OFF    |
|  5 | 2020-01-01 15:00:00 | ON     |
|  6 | 2020-01-01 16:00:00 | ON     |
|  7 | 2020-01-01 17:30:00 | OFF    |
|  8 | 2020-01-01 18:00:00 | ON     |
+----+---------------------+--------+

These are my conclusions:

  1. We see the device is on at 10:00. Was it on before? We don't know. Hence we consider this the first switch-on time.
  2. Why are we getting this? Has the device failed to send an OFF in between? Well, let's consider this "still on".
  3. Switched off.
  4. Why are we getting this? Has the device failed to send an ON in between? Well, let's consider this "still off".
  5. Switched on.
  6. Why are we getting this? Has the device failed to send an OFF in between? Well, let's consider this "still on".
  7. Switched off.
  8. Switched on.

This means we must always find the next status change. ON directly after ON gets ignored. Same for OFF after OFF.

Steps:

  1. Add a row with the current time and OFF for easier calculation later.
  2. Look into the previous row. Only keep rows that have a different status from the previous row.
  3. For easier calculation, I convert the timestamps to seconds since a fixed date in the part.
  4. Now it's mere aggregation and getting back from seconds only to hours, minutes, and seconds.

The query:

select
  sec_to_time(sum(case when 'OFF' then secs else -secs end))
from
(
  select
    timestampdiff(second, date '1990-01-01', datetime) as secs,
    status, 
    lag(status) over (order by datetime) as prev_status
  from 
  (
    select datetime, status from Sack
    union all
    select now(), 'OFF'
  ) end_row_added
) with_previous_status
where status <> prev_status or prev_status is null;

You need MySQL 8 for this. Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9a3e500bafb09a9b3b5295ac715b6236

Upvotes: 1

KIKO Software
KIKO Software

Reputation: 16751

I got a basic one for the duration except for the last bit from 'ON' to 'NOW()'. It looks like this:

SELECT SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, S1.datetime, S2.datetime))) AS duration
FROM Stack AS S1,
     Stack AS S2
WHERE S1.id + 1 = S2.id AND
      S1.status = 'ON'

This can probably be written as a JOIN as well, most people prefer that, but that's equivalent to this. There's a bit of juggling with the times, as you can see. The result is:

06:30:00

I spotted an error in your database, there are two id's with the value 7. I changed the second one to 8 and now the result is:

04:30:00

That cannot be right. Haha... sorry, let me think. OK, checked it, and it is correct. The 06:30:00 was caused by the error in the database.

The next query will compute the remaining time at the end of the database table:

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, `datetime`, NOW())) AS `duration`
FROM `Stack` 
WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
      `status` = 'ON'

It now return the ridiculous:

838:59:59

This is not the right result, so ignore it. The date is simply too far back in the past for this query to work. To get a reasonable result from the database I changed all the dates from 2020-01-01 to 2021-01-22.

Finally we need to combine these two. And that's relatively simple:

SELECT
  SEC_TO_TIME(
    (SELECT SUM(TIMESTAMPDIFF(SECOND, S1.datetime, S2.datetime)) AS duration
     FROM Stack AS S1,
          Stack AS S2
     WHERE S1.id + 1 = S2.id AND
           S1.status = 'ON')
     +
     (SELECT TIMESTAMPDIFF(SECOND, `datetime`, NOW()) AS `duration`
      FROM `Stack` 
      WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
            `status` = 'ON')
   );

And that should do it. Now I am sure there must be a better way to do this, but hey, it works!

Oh, if the last status is 'OFF' it results NULL. Let me work on that. This should do something:

SELECT
  SEC_TO_TIME(CAST(
    (SELECT SUM(TIMESTAMPDIFF(SECOND, `S1`.`datetime`, `S2`.`datetime`)) AS duration
     FROM Stack AS `S1`,
          Stack AS `S2`
     WHERE `S1`.`id` + 1 = `S2`.`id` AND
           `S1`.`status` LIKE 'ON')
     +
     IFNULL((SELECT TIMESTAMPDIFF(SECOND, `datetime`, NOW()) AS `duration`
             FROM `Stack` 
             WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
                   `status` LIKE 'ON'), 0)
   AS UNSIGNED));

I added the CAST(.... AS UNSIGNED) to remove the anything after the second.

Upvotes: 1

Related Questions