How can I find out the time during which a parameter had a certain value from the event log?

Question

MySQL

There is an event log table for trigger metric changes (on/off) with the following structure:

Table `log`:
+-------------+----------+--------------------------+
| metric_type | int      | Тип метрики              |
+-------------+----------+--------------------------+
| date_change | datetime | Дата изменения состояния |
+-------------+----------+--------------------------+
| state       | tinyint  | Состояние (вкл./выкл.)   |
+-------------+----------+--------------------------+

The table records the state of certain metrics. Moreover, registration occurs only at the time of metric change.
Example data: metric "1" was in state "1" from 2017-04-27 22:00 to 2017-06-02 10:00:

+-------------+---------------------+-------+
| metric_type | date_change         | state |
+-------------+---------------------+-------+
| 1           | 2017-04-20 10:00:00 | 0     |
+-------------+---------------------+-------+
| 1           | 2017-04-27 22:00:00 | 1     |
+-------------+---------------------+-------+
| 1           | 2017-06-02 10:00:00 | 0     |
+-------------+---------------------+-------+

How can SQL be used to read how long a metric was in state "1" in an arbitrarily given range of time?

Answer 1

You need to join this table to itself, but with a shift of one line. Here is a clumsy but working solution in ORACLE:

select t1.metric_type, t1.state, (t2.date_change - t1.date_change) as diff
from
(
select rownum as n, metric_type, state, date_change
from
(
select *
from someTable
order by metric_type, date_change
)) t1
left join (select rownum as n, metric_type, state, date_change
from
(
select *
from someTable
order by metric_type, date_change
)) t2 on (t1.n = t2.n+1 and t1.metric_type = t2.metric_type)