MYSQL get diffrence between two dattimes in minutes and update in field

Question

I have table with 3 fileds id, startdate, enddate. Now I have added new field called mins as mentioned in image.

I need query to get diffrence between startdate and enddate in minutes and store in fields mysql.

Please look here for answer on the same question: http://stackoverflow.com/a/40409810/6756033 — massko, Nov 07 '16 at 07:51

sagi · Accepted Answer · 2016-11-07T08:33:24.290

0

Use MySQL TIMESTAMPDIFF() :

UPDATE YourTable t 
set t.mins =  ABS(TIMESTAMPDIFF(MINUTE,t.startdate,t.enddate)) ;

edited Nov 07 '16 at 08:33

answered Nov 07 '16 at 07:49

sagi

40,026
6
59
84

when differences not proper it stores value in minus – Nov 07 '16 at 08:31
@MasterDev Updated. – sagi Nov 07 '16 at 08:33

score 0 · Answer 2 · answered Nov 07 '16 at 08:03

Given you have added the minutes column later, you should update your table using the following query.

 INSERT INTO yourTable 
    SELECT 
      inn.id,
      inn.StartDate,
      inn.EndDate,
      TIMESTAMPDIFF(
        MINUTE,
        inn.StartDate,
        inn.EndDate
      ) AS diff 
    FROM
      `yourTable` inn 
      ON DUPLICATE KEY 
      UPDATE 
        id = inn.id,
        `StartDate` = inn.StartDate,
        EndDate = inn.EndDate,
        Mins = TIMESTAMPDIFF(
          MINUTE,
          inn.StartDate,
          inn.EndDate
        ) ;

Suppose this is the initial state of your table

  id  StartDate            EndDate                Mins  
------  -------------------  -------------------  --------
     1  2016-11-07 12:53:27  2016-11-30 12:53:35         0
     2  2016-11-02 12:53:41  2016-12-07 12:53:45         0
     3  2016-11-16 12:53:52  2017-02-16 12:53:56         0

Executing the above query transforms the table like this :

    id  StartDate            EndDate                Mins  
------  -------------------  -------------------  --------
     1  2016-11-07 12:53:27  2016-11-30 12:53:35     33120
     2  2016-11-02 12:53:41  2016-12-07 12:53:45     50400
     3  2016-11-16 12:53:52  2017-02-16 12:53:56    132480

MYSQL get diffrence between two dattimes in minutes and update in field

2 Answers2