You will often want to find the time elapsed between two date/time values.

In some cases, there are built-in values in the tickets table which provide this information for you. For example, tickets.total_to_first_reply gives you the number of seconds between the time the ticket was created and the first agent reply.

A time value expressed as a number of seconds is not a very easy format to interpret, so you can convert to hours by doing this:

tickets.total_to_first_reply / (60 * 60)
 copy

What you’re doing here is simply dividing the number of seconds by 60 to get a number of minutes, and 60 again to get a number of hours.

In other cases, you may want to find the time elapsed between two datetimes where a value like total_to_first_reply has not been provided.

For example, say you want to get the time between a ticket being created and resolved.

You might try (tickets.date_resolved - tickets.date_created), but this does not work. In the tickets table in the appendix, you’ll see that the format for both these values is datetime, and you can’t subtract datetime values and get a meaningful answer.

The solution is to use the MySQL function UNIX_TIMESTAMP() to convert the datetime values to a Unix timestamp: a way of expressing time as the number of seconds elapsed since an arbitrary point in time (midnight Jan 1 1970 UTC). With two points in time expressed as Unix timestamps, you can subtract the earlier one from the later one and get the interval as a number of seconds.

So in our example, you would use:

(UNIX_TIMESTAMP(tickets.date_resolved) - UNIX_TIMESTAMP (tickets.date_created)) / (60 * 60)
 copy