CODEWITHSUNDEEP
mysqlsql
tainaamoraes
tainaamoraes

Reputation: 31

Calculate time difference between 2 (non consecutive) rows

I'm newbie in SQL and I'm trying to compare two hours and see the difference between rows in columns "diff plan" and "diff done".

How can I achieve this?

Thank you! :)

date                id          planned     done       diff plan    diff done
21/10/2019 00:00    3252250 A   05:00:00    04:59:04        NULL      NULL
21/10/2019 00:00    3251855 A   05:00:00    05:00:18    00:00:00    00:01:14
21/10/2019 00:00    3251868 A   05:07:00    05:07:30    00:07:00    00:07:12
21/10/2019 00:00    3252291 A   05:10:00    05:10:47    00:03:00    00:03:17

Upvotes: 2

Views: 462

Answers (2)

GMB
GMB

Reputation: 222482

Here is a solution for MySQL < 8.0, where window functions are not available. This assumes that column done can be used to order the records (you can change every occurence of done to some other column if that's more relevant for your use case):

select
    t.*,
    timediff(t.planned, tlag.planned) diff_plan,
    timediff(t.done,    tlag.done) diff_done
from mytable t
left join mytable tlag
    on tlag.done < t.done
    and not exists (
        select 1
        from mytable t1
        where t1.done < t.done and t1.done > tlag.done
    )
order by t.done

This works by self-joining each record with its preceeding record (for this, we use a not exists condition with a correlated subquery, that ensures that there is no record in between the current record and the one being joined). Then, we use function timediff() to compute the time difference for columns planned and done.

Demo on MySQL 5.6 DB Fiddle:

Sample data:

| date       | id      | planned  | done     |
| ---------- | ------- | -------- | -------- |
| 2019-10-21 | 3252250 | 05:00:00 | 04:59:04 |
| 2019-10-21 | 3251855 | 05:00:00 | 05:00:18 |
| 2019-10-21 | 3251868 | 05:07:00 | 05:07:30 |
| 2019-10-21 | 3252291 | 05:10:00 | 05:10:47 |

Results:

| date       | id      | planned  | done     | diff_plan | diff_done |
| ---------- | ------- | -------- | -------- | --------- | --------- |
| 2019-10-21 | 3252250 | 05:00:00 | 04:59:04 |           |           |
| 2019-10-21 | 3251855 | 05:00:00 | 05:00:18 | 00:00:00  | 00:01:14  |
| 2019-10-21 | 3251868 | 05:07:00 | 05:07:30 | 00:07:00  | 00:07:12  |
| 2019-10-21 | 3252291 | 05:10:00 | 05:10:47 | 00:03:00  | 00:03:17  |

Upvotes: 1

Ishpreet
Ishpreet

Reputation: 667

Please have a look at link. It might help you.

TIMEDIFF function will find difference between two times.

Upvotes: 0

Related Questions