w3resource

MySQL TIMEDIFF() function

TIMEDIFF() function

MySQL TIMEDIFF() returns the differences between two time or datetime expressions. It is to be noted that two expressions must be the same type.

Syntax:

TIMEDIFF(expr1,expr2)

Arguments

Name Description
expr1 A datetime value.
expr2 A datetime value.

Syntax Diagram:

MySQL TIMEDIFF() Function - Syntax Diagram

MySQL Version: 5.6


Video Presentation

Pictorial Presentation

Pictorial Presentation of MySQL TIMEDIFF() function

Example:

The following statement will return the difference between two datetime values 2009-05-18 15:45:57.005678 and 2009-05-18 13:40:50.005670.

Code:

SELECT TIMEDIFF('2009-05-18 15:45:57.005678','2009-05-18 13:40:50.005670');

Sample Output:

mysql> SELECT TIMEDIFF('2009-05-18 15:45:57.005678','2009-05-18 13:40:50.005670');
+---------------------------------------------------------------------+
| TIMEDIFF('2009-05-18 15:45:57.005678','2009-05-18 13:40:50.005670') |
+---------------------------------------------------------------------+
| 02:05:07.000008                                                     | 
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-TIMEDIFF-function - php mysql examples | w3resource</title>
<meta name="description" content="PHP MySQL PDO Example">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Time difference between 2009-05-18 15:45:57.005678 and 2009-05-18 13:40:50.005670:</h2>
<table class='table table-bordered'>
<tr>
<th>Time difference</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT TIMEDIFF("2009-05-18 15:45:57.005678","2009-05-18 13:40:50.005670")') as $row) {
echo "<tr>";
echo "<td>" . $row['TIMEDIFF("2009-05-18 15:45:57.005678","2009-05-18 13:40:50.005670")'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-timediff-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT TIMEDIFF('2009-05-18 15:45:57.005678','2009-05-18 13:40:50.005670')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Time difference</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("TIMEDIFF('2009-05-18 15:45:57.005678','2009-05-18 13:40:50.005670')")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: TIME()
Next: TIMESTAMP()