qqqwww qqqwww - 2 months ago 15
MySQL Question

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

Answer

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;