Average of a difference in Timestamps

renee12345

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?

marcel_luthi

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.

trafalger

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

marcel_luthi

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.