MySQL TIMESTAMP() function

Last update on August 19 2022 21:50:42 (UTC/GMT +8 hours)

TIMESTAMP() function

MySQL TIMESTAMP() returns a datetime value against a date or datetime expression.

If two arguments are used with this function, first it adds the second expression with the first and then returns a datetime.

Syntax:

TIMESTAMP(expr); TIMESTAMP(expr1,expr2)

Arguments:

Name	Description
expr	A date or datetime value.
expr1	A date or datetime value.
expr2	A time expression.

Syntax Diagram: 1

MySQL TIMESTAMP() Function - Syntax Diagram

Syntax Diagram: 2

MySQL Version: 5.6

Video Presentation:

Pictorial Presentation:

MySQL Version 5.6

Example:

The following MySQL statement will return a datetime value for the given date expression 2009-05-18.

Code:

SELECT TIMESTAMP('2009-05-18');

Sample Output:

mysql> SELECT TIMESTAMP('2009-05-18');
+-------------------------+
| TIMESTAMP('2009-05-18') |
+-------------------------+
| 2009-05-18 00:00:00     | 
+-------------------------+
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.0">
<title>example-timestamp-function - php mysql examples | w3resource</title>
<meta name="description" content="example-timestamp-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>Date value for date expression 2009-05-18:</h2>
<table class='table table-bordered'>
<tr>
<th>Date expressions</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 TIMESTAMP("2009-05-18")') as $row) {
echo "<tr>";
echo "<td>" . $row['TIMESTAMP("2009-05-18")'] . "</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-timestamp-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 TIMESTAMP('2009-05-18')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Date expressions</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("TIMESTAMP('2009-05-18')")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example: TIMESTAMP() function using datetime

The following MySQL statement will return a datetime value after adding 1:48:49 with 2009-05-18 22:11:11.

Code:

SELECT TIMESTAMP('2009-05-18 22:11:11','1:48:49');

Sample Output:

mysql> SELECT TIMESTAMP('2009-05-18 22:11:11','1:48:49');
+--------------------------------------------+
| TIMESTAMP('2009-05-18 22:11:11','1:48:49') |
+--------------------------------------------+
| 2009-05-19 00:00:00                        | 
+--------------------------------------------+
1 row in set (0.00 sec)

View the example in browser

MySQL Datetime vs Timestamp

The MySQL DATETIME type is used to return values that contain both date and time parts. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range of MySQL DATETIME type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used to return value which also contains both date and time parts. The range of MySQL TIMESTAMP type is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

The MySQL TIMESTAMP values are converted from the current time zone to UTC while storing and converted back from UTC to the current time zone when retrieved. The default, current time zone for each connection is the server's time. Suppose you stored a TIMESTAMP value, then change the time zone and try to retrieve the value, it returns the different value as you stored earlier. It happens because the time zone used for conversion is not same.

But the in case of DATETIME data type, the value is unchanged.

Invalid DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: TIMEDIFF()
Next: TIMESTAMPADD()