Subtracting a start time and end time in mysql

Databases
1

I have a table of activities where I store a start time and and end time for a particular activity. The format for both is datetime(If you ran a query for one of the them you would get something like 2004-06-16 15:05:13).

First part of the problem I have a feeling should be quite simple but I just can’t seem to get the right query going: I am just trying to subtract the end time from the start time and I am just not sure how to do that in the mysql query. If I do something like sum(endtime-starttime) I get some weird numbers…If any body has an an idea of what native mysql datetime functions I am supposed to use here on these two columns, I would be thrilled and delighted and I’ll toast my morning coffee to them.

there is a second part to this problem, but the first problem is I should go to sleep…
Thanks, Dan

2

Hi,

With the query below, you will get the number of seconds between the two dates.

SELECT UNIX_TIMESTAMP(end) - UNIX_TIMESTAMP(start) AS secdiff FROM table

Yours, Erik.

3

just to enhance erik’s suggestion, you have to watch out for the difference in time being greater than 24 hours if you want to convert the difference in seconds back to HH:MM:SS format

select unix_timestamp(endtime)
      -unix_timestamp(starttime)   as secdiff
     , floor(
       ( unix_timestamp(endtime)
        -unix_timestamp(starttime) ) / 86400
            )                      as daysdiff
     , unix_timestamp(endtime)
      -unix_timestamp(starttime)
      -floor(
       ( unix_timestamp(endtime)
        -unix_timestamp(starttime) ) / 86400
            ) * 86400              as extraseconds
     , sec_to_time(
       unix_timestamp(endtime)
      -unix_timestamp(starttime)
      -floor(
       ( unix_timestamp(endtime)
        -unix_timestamp(starttime) ) / 86400
            ) * 86400
                  )                as extratime
  from yourtable

in the above, various calculations are returned so that you can see the intermediate values

you’d keep the daysdiff and extratime columns

4

Thanks!! I’ll try it as soon as I get to work.And a cheers with my morning espresso to both of you.
Thanks, Dan