CODEWITHSUNDEEP
mysql
Gayan
Gayan

Reputation: 2935

MySQL How to substract two DATETIME and INT value

I store "start_time", "finish_time" and "meal" time as

start_time = DATETIME
finish_time = DATETIME
meal = INT - store minitues

Now I want to get deference between start_time , finish_time after that i want to subtract meal time form hours how to do that,

eg:
start_time             finish_time           meal
2014-10-24 08:30:00    2014-10-24 18:30:00   45

output should be: 9:15

How to get that using MySQL only,

I have try to use TIMESTAMPDIFF but I don't know how to subtract meal and convert it to H:m format : 

TIMESTAMPDIFF(MINUTE,start_time, finish_time)

Upvotes: 0

Views: 86

Answers (3)

Adam Fentosi
Adam Fentosi

Reputation: 1268

There is a better way:

SEC_TO_TIME(TIMESTAMPDIFF(MINUTE,start_time, finish_time))

Upvotes: 1

Sebas
Sebas

Reputation: 21532

SELECT 
    SEC_TO_TIME(
        (
            (
                UNIX_TIMESTAMP(`finish_time`) - UNIX_TIMESTAMP(`start_time`) /* difference in seconds */
            ) / 60 - `meal` /* transform in minutes, substract minutes */
        ) * 60 /* seconds again */
    ) AS `timediff` /* to time :) */
FROM `table`

Upvotes: 0

Barmar
Barmar

Reputation: 781058

Just do ordinary subtraction:

TIMESTAMPDIFF(MINUTE,start_time, finish_time) - meal

To convert this to H:m format, divide it by 60 and get the modulus from 60:

CONCAT(FLOOR((TIMESTAMPDIFF(MINUTE,start_time, finish_time) - meal)/60),
      ':', 
      (TIMESTAMPDIFF(MINUTE,start_time, finish_time) - meal) % 60)

Upvotes: 1

Related Questions