w3resource logo


MySQL timestampdiff function

MySQL TIMESTAMPDIFF() function

Secondary Nav

TIMESTAMPDIFF() function

MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another.

It is not necessary that both the expression are of same type. One may be a date and another is datetime. A date value is treated as a datetime with a default time part '00:00:00'. The unit for the result is given by another argument.

The unit should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

Syntax :

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);

Arguments :

Name Description
datetime_expr1 A datetime expression.
datetime_expr1 A datetime expression.
unit An unit, as described in the description.

Video Presentation

Pictorial Presentation

Pictorial Presentation of MySQL TIMESTAMPDIFF() function

Example :

The following statement will return a value in months by subtracting 2009-05-18 from 2009-07-29.

SELECT TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29');  

Output :

mysql> SELECT TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29');
+------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29') |
+------------------------------------------------+
|                                              2 | 
+------------------------------------------------+
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-timestampdiff-function - php mysql examples | w3resource</title>
<meta name="description" content="example-timestampdiff-function - php mysql examples | w3resource">
<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>Difference of dates in terms of month between 2009-05-18 and 2009-07-29 : </h2>
<table class='table table-bordered'>
<tr>
<th>Difference in months</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 TIMESTAMPDIFF(MONTH,"2009-05-18","2009-07-29")') as $row) {
echo "<tr>";
echo "<td>" . $row['TIMESTAMPDIFF(MONTH,"2009-05-18","2009-07-29")'] . "</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-timestampdiff-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 TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Difference in months</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29')")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example : TIMESTAMPDIFF() function in MINUTE

The following statement will return a value in minutes after subtracting 2009-05-18 11:45:42 from 2009-05-20 15:16:39.

SELECT TIMESTAMPDIFF(MINUTE,'2009-05-18 11:45:42','2009-05-20 15:16:39');

Output :

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2009-05-18 11:45:42','2009-05-20 15:16:39');
+-------------------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2009-05-18 11:45:42','2009-05-20 15:16:39') |
+-------------------------------------------------------------------+
|                                                              3090 | 
+-------------------------------------------------------------------+
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>example1-timestampdiff-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-timestampdiff-function - php mysql examples | w3resource">
<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>Difference in minutes between 2009-05-18 11:45:42 and 2009-05-20 15:16:39:</h2>
<table class='table table-bordered'>
<tr>
<th>Difference in months</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 TIMESTAMPDIFF(MINUTE,"2009-05-18 11:45:42","2009-05-20 15:16:39")') as $row) {
echo "<tr>";
echo "<td>" . $row['TIMESTAMPDIFF(MINUTE,"2009-05-18 11:45:42","2009-05-20 15:16:39")'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

All Date and Time Functions :

Click here to see the MySQL Date and time functions.



Is this content useful for you?

 


You might be using Adblocker. Since w3resource does not charge anything from users, all of our expenses (e.g. Hosting, Content Creation, Development etc.) are met by advertisement. Please whitelist w3resource.