Average of a difference in Timestamps

helpme12345
helpme12345 Member
in Beast Mode

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

https://community-forums.domo.com/main/discussion/49240/date-calculations-minutes-and-seconds

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

  • trafalger
    trafalger Coach

    Have you considered using TIMEDIFF? https://www.w3schools.com/sql/func_mysql_timediff.asp

  • 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.

Categories