Average of a difference in Timestamps

I have two timestamp fields: completed at and delivered at. I want to find the difference of both and take the average.

The dates include a window that is sometimes over a day. I read this

By @GrantSmith which is useful. Is there an easy way to do this without breaking up each segment (day, hour, minute, second) then taking the average of each and combining?

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Working with dates can be tricky, and while TIMEDIFF can work, I've found using UNIX_TIMESTAMP to work best.

    UNIX_TIMESTAMP(Date1)-UNIX_TIMESTAMP(Date2)
    

    This will give you the time difference in seconds, if you want it to be minutes you just divide by 60, for hours by 3600 (60*60) or 86400 (60*60*24) for days.

    You might want to perform additional checks to see if a date is NULL or not, as NULLs will make the operation to return NULL which might not always be the desired outcome.

Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Working with dates can be tricky, and while TIMEDIFF can work, I've found using UNIX_TIMESTAMP to work best.

    UNIX_TIMESTAMP(Date1)-UNIX_TIMESTAMP(Date2)
    

    This will give you the time difference in seconds, if you want it to be minutes you just divide by 60, for hours by 3600 (60*60) or 86400 (60*60*24) for days.

    You might want to perform additional checks to see if a date is NULL or not, as NULLs will make the operation to return NULL which might not always be the desired outcome.