MySQL TIMESTAMP() function

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.

This function is useful in -

• It helps maintain data integrity by ensuring that date and time information is stored in a consistent format across records.
• TIMESTAMP can store date and time values in the local time zone or in Coordinated Universal Time (UTC), allowing for proper handling of time zones.
• TIMESTAMP values can be compared to each other, allowing you to determine which event or record occurred first.
• TIMESTAMP is essential for applications that involve scheduling events or tasks based on specific dates and times.
• It's useful for storing and analyzing time series data, such as stock prices, weather data, or any data with a time component.
• You can set a TIMESTAMP field to automatically update to the current date and time whenever a record is created or modified.
• With TIMESTAMP, you can perform date arithmetic operations, such as addition and subtraction, directly in the database.

Syntax:

TIMESTAMP(expr); TIMESTAMP(expr1,expr2)

Arguments:

Syntax Diagram: 1

Syntax Diagram: 2

MySQL Version: 8.0

Pictorial Presentation:

Example:

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

Code:

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

Output:

mysql> SELECT TIMESTAMP('2009-05-18');
+-------------------------+
| TIMESTAMP('2009-05-18') |
+-------------------------+
| 2009-05-18 00:00:00     | 
+-------------------------+
1 row in set (0.00 sec)

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');

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').

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: TIMEDIFF()
Next: TIMESTAMPADD()