Home > Database > Mysql Tutorial > How to Calculate Time Differences in MySQL as a Decimal?

How to Calculate Time Differences in MySQL as a Decimal?

Mary-Kate Olsen
Release: 2024-11-02 09:57:02
Original
1015 people have browsed it

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>
Copy after login

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>
Copy after login

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

<code class="mysql">420 / 60</code>
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template