MySQL date difference from dates in one column-CodePudding

I have a table "nasypka", with structure:

ID|datetime        |height|refilled
1 |2022-09-01 12:00|101   |1
2 |2022-09-01 12:01|96    |0
3 |2022-09-01 12:02|50    |0
4 |2022-09-01 12:03|10    |0
...
50|2022-09-05 17:04|105   |1
51|2022-09-05 17:05|104   |0
...
80|2022-09-15 10:04|99   |1
81|2022-09-15 10:05|98   |0

This table holds data about amount of coal in reservoir (height in centimeters) of coal boiler. I want to know time (date) difference between refillements of coal in reservoir. Moment of refilling is marked by (boolean) "1" in "refilled" column.

ID column does not have to be contiguous.

Result will show how long the reservoir lasted to fill the boiler with coal. Expected result should be:

begin_date      |end_date        |difference
2022-09-01 12:00|2022-09-05 17:04|calculated difference in some format or in hours
2022-09-05 17:04|2022-09-15 10:04|calculated difference in some format or in hours
...
(limited to last X selected periods)

It seems that my hosting have MySQL version 5.5.59

I tried to google out any solution that I could modify to my case, but even that I found something similar, I was unable to modify it. I am not skilled in SQL. :-(

CodePudding user response：

Without 'modern' functionality of window functions (nearly two decades of support outside of MySQL), this will be slow.

First, generate an id for each 'group' of rows, based on the event flag in refilled.

I used a correlated sub-query

Then aggregate as usual.

SELECT
  group_id,
  MIN(date),
  MAX(date),
  TIMEDIFF(MIN(date), MAX(date))
FROM
(
  SELECT
    *,
    (
      SELECT
        SUM(refilled)
      FROM
        your_table AS lookup
      WHERE
        lookup.datetime <= your_table.datetime
    )
      AS group_id
  FROM
    your_table
)
  AS grouped
GROUP BY
  group_id
ORDER BY
  group_id

The max date will be the last date in the group, not the first date of the subsequent group. If that's needed, you need yet another correlated sub-query...

SELECT
  group_id,
  MIN(date),
  MAX(end_date),
  TIMEDIFF(MIN(date), MAX(end_date))
FROM
(
  SELECT
    *,
    (
      SELECT
        COALESCE(MIN(lookup.date), your_table.date)
      FROM
        your_table AS lookup
      WHERE
        lookup.date > your_table.date
    )
      AS end_date,
    (
      SELECT
        SUM(refilled)
      FROM
        your_table AS lookup
      WHERE
        lookup.datetime <= your_table.datetime
    )
      AS group_id
  FROM
    your_table
)
  AS grouped
GROUP BY
  group_id
ORDER BY
  group_id

CodePudding user response：

Take a look at the datediff function built into MySQL (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff)

This is for MySQL 8.0 but you can still use this in 5.5

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html