How to Calculate Time Differences in MySQL as a Decimal?

Calculating Time Differences in MySQL

You've set up a MySQL database with a 'time' field that represents the time remaining in a game when an event occurs. To determine the time elapsed between two events, you want to calculate the difference between their 'time' field values and express it as a decimal.

It is possible using the TIMESTAMPDIFF() function. Here's how:

<code class="mysql">TIMESTAMPDIFF(MINUTE, T0.created, T0.modified)</code>

In this query, T0 is the table containing the 'time' field. T0.created represents the time of the first event, and T0.modified represents the time of the second event. The TIMESTAMPDIFF() function computes the difference between these two times in minutes.

For example, if an event occurred at 12:25 (12 minutes and 25 seconds left) and the subsequent event occurred at 5:45 (5 minutes and 45 seconds left), the difference would be calculated as:

<code class="mysql">TIMESTAMPDIFF(MINUTE, '12:25:00', '05:45:00')</code>

This would return a result of 420 minutes (7 hours), which can then be converted to a decimal:

<code class="mysql">420 / 60</code>

This would give you the decimal value of 7.

Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff

The above is the detailed content of How to Calculate Time Differences in MySQL as a Decimal?. For more information, please follow other related articles on the PHP Chinese website!