Question 21576 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL date difference from dates in one column

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 EDIT: webhosting confirmed that, also offered migration to v5.7 or v8.0

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. 🙁

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Meanwhile I realised few things:

        SELECT (SELECT datetime FROM nasypka WHERE refilled=1 AND (datetime<(SELECT datetime FROM nasypka WHERE refilled=1 ORDER BY datetime DESC LIMIT 1)) ORDER BY datetime DESC LIMIT 1 OFFSET 0) AS zacatek
       , (SELECT datetime FROM nasypka WHERE refilled=1 ORDER BY datetime DESC LIMIT 1 OFFSET 0) AS konec
       , TIMESTAMPDIFF(hour,(SELECT datetime FROM nasypka WHERE refilled=1 AND (datetime<(SELECT datetime FROM nasypka WHERE refilled=1 ORDER BY datetime DESC LIMIT 1)) ORDER BY datetime DESC LIMIT 1 OFFSET 0),(SELECT datetime FROM nasypka WHERE refilled=1 ORDER BY datetime DESC LIMIT 1 OFFSET 0)) AS vydrz_hodin
    FROM nasypka
    WHERE refilled=1
    ORDER BY datetime DESC
    LIMIT 1;

    This works fine, throws one row. Nasypka is the table name. The only thing here I need is to loop it and shift OFFSET value. I tried REPEAT-UNTIL sample codes, WHILE loop sample code, but nothing work in MySQL 5.5 I have at webhosting.

    So I assumed it is a bit problem with that old version of MySQL. I contacted helpdesk od webhosting and got prompt reply, that I have MySQL 5.5 and if I want, I can be migrated to v5.7 or v8.0. Migration means I have to backup all tables and databases, then delete them, then I will be migrated and then I have to recreate structures and restore data. I have to google out, what else would it mean for me, if previous codes/queries (i have in PHP) will be the same, I assume yes, v8.0 will be backwards compatible, especially for my simple queries and the only code I have to change is mysql server address in "mysqli_connect".


  2. 0

    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
    Login or Signup to reply.
  3. 0

    This is a working solution from @MatBailie [MatBailie]1
    and I want to explicitly post it here for a case, that dbfiddle.uk/qCs4xwqT won’t work, so other should find solution easily:

        CREATE TABLE your_table (
  id         INT,
  date       TIMESTAMP,
  height     INT,
  refilled   BOOLEAN
)

    INSERT INTO
  your_table
VALUES
(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) 

    SELECT
  start_date,
  end_date,
  TIMEDIFF(end_date, start_date) AS difference
FROM
(
  SELECT
    date AS start_date,
    (
      SELECT date
        FROM your_table AS lookup
       WHERE date > your_table.date
    ORDER BY refilled DESC, date
       LIMIT 1
    )
      AS end_date
  FROM
    your_table
  WHERE
    refilled=1
) 
  AS windows
ORDER BY
  start_date

    TIMEDIFF can be changed to

    TIMESTAMPDIFF(hour,start_datee, end_date) AS difference

    Thank you, MatBailie!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search