CODEWITHSUNDEEP
mysqlsqldatabase
vs_lala
vs_lala

Reputation: 745

How to find the difference of dates in consecutive rows and update the table column respectively in MySQL?

I have a table with below-mentioned columns:

I want to subtract row 1 PATIENT_IN_TIME with row 2 PATIENT_IN_TIME and save the result in minutes to AVG_CHECKUP_TIME.

Suppose there are 5 entries in the table.

|1|2|2018-03-22 02:49:51|NULL|

|2|2|2018-03-22 02:56:37|NULL|

So I want to find the difference of both the rows and save the minutes in the last column. So, the output would look like,

|1|2|2018-03-22 02:49:51|7|

|2|2|2018-03-22 02:56:37|NULL|

Please let me know if you need more information.

Upvotes: 1

Views: 32

Answers (1)

McNets
McNets

Reputation: 10807

create table tbl 
(
  id int auto_increment primary key, 
  doctor_id int, 
  patient_in_time datetime, 
  avg_checkup_time datetime
);

insert into tbl values
(1, 2, '2018-03-22 02:49:51', null),
(2, 2, '2018-03-22 02:56:37', null),
(3, 2, '2018-03-22 03:00:15', null),
(4, 2, '2018-03-22 03:03:37', null);

select t1.id, t1.doctor_id, t1.patient_in_time, 
       timestampdiff(minute, t1.patient_in_time, 
                      (select patient_in_time 
                       from tbl where id = t1.id +1)) diff
from tbl t1

id | doctor_id | patient_in_time     | diff
-: | --------: | :------------------ | ---:
 1 |         2 | 2018-03-22 02:49:51 |    6
 2 |         2 | 2018-03-22 02:56:37 |    3
 3 |         2 | 2018-03-22 03:00:15 |    3
 4 |         2 | 2018-03-22 03:03:37 | null

dbfiddle here

As per comments if order is set by patient_in_time then you can use an scalar subquery that returns next row in this way:

select t1.id,
       t1.doctor_id,
       t1.patient_in_time,
       timestampdiff(minute, 
                     t1.patient_in_time, 
                     (select   patient_in_time
                      from     tbl 
                      where    patient_in_time > t1.patient_in_time
                      order by patient_in_time asc
                      limit 1)) diff
from   tbl t1
order by patient_in_time

id | doctor_id | patient_in_time     | diff
-: | --------: | :------------------ | ---:
 1 |         2 | 2018-03-22 02:49:51 |    6
 2 |         2 | 2018-03-22 02:56:37 |    3
 3 |         2 | 2018-03-22 03:00:15 |    3
 4 |         2 | 2018-03-22 03:03:37 | null

dbfiddle here

Upvotes: 3

Related Questions