CODEWITHSUNDEEP
mysqldatedatediff
qqqwww
qqqwww

Reputation: 531

MySQL - datediff days (keep several decimal)

Please set the table below. When I use datediff(StartDate, SubmittedOn), I get only int type days, which is not accurate. So how to get the late one? Thank you. 

StartDate        SubmittedOn        what I get using datediff   what I am tring to get
9/7/2016 13:12  9/1/2016 0:00        6                          6.550520833
9/1/2016 16:22  9/1/2016 0:00        0                          0.682048611
9/9/2016 13:30  9/1/2016 0:00        8                          8.562708333
9/9/2016 13:31  9/1/2016 0:00        8                          8.563472222
9/9/2016 16:08  9/1/2016 0:00        8                          8.672407407
9/2/2016 16:08  9/1/2016 0:00        1                          1.672685185
9/2/2016 16:01  9/1/2016 0:00        1                          1.667465278

Upvotes: 2

Views: 4571

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The previous question did seem to be incorrectly closed.

In any case, you want to take the difference in a smaller time unit and convert to days. For instance:

select t.*,
       timestampdiff(second, startdate, submittedon) / (24 * 60 * 60) as days_with_fraction
from t;

Upvotes: 8

psj01
psj01

Reputation: 3245

Try doing TIMEDIFF instead of DATEDIFF.

more here

Upvotes: 0

Related Questions