MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Database Structure / Data Types / Question No: 114

What is TIMESTAMP Data Type in MySQL?


Answer No: 114

In MySQL 5 Certification Study Guide, the TIMESTAMP data type is described in detailed. Some useful text is as below:

The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range and some special properties that make it especially suitable for tracking data modification times.

MySQL displays TIMESTAMP values using the same format as DATETIME values; that is, 'YYYY-MM-DD hh:mm:ss'. The range of TIMESTAMP values begins at 1970-01-01 00:00:00 (UTC) and extends partway into the year 2037. TIMESTAMP values actually represent the number of seconds elapsed since the beginning of 1970 and are stored using four bytes. This provides room for sufficient seconds to represent a date in the year 2037. MySQL Server stores TIMESTAMP values internally in UTC. It converts TIMESTAMP values from the server's current time zone for storage, and converts back to the current time zone for retrieval.

The TIMESTAMP data type in MySQL is special in that you can cause a TIMESTAMP column to be initialized or updated automatically to the current date and time without explicitly assigning it a value. That is, you can specify that any single TIMESTAMP column in a table should be initialized with the current timestamp when the record is created with INSERT or REPLACE, updated with the current timestamp when the record is changed with UPDATE, or both. (Setting a column to its current value doesn't count as updating it.)

It's important to know about the automatic initialization and update properties of TIMESTAMP. These properties make TIMESTAMP columns useful for tracking record modification times, but can be a source of confusion if you're not aware of them. Do not choose TIMESTAMP for a column on the basis of the fact that it stores date-and-time values unless you also understand the circumstances under which the column will update automatically when other columns in a record change.

To control the initialization and update behavior of a TIMESTAMP column, you add either or both of the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes to the column definition when creating the table with CREATE TABLE or changing it with ALTER TABLE.

The DEFAULT CURRENT_TIMESTAMP attribute causes the column to be initialized with the current timestamp at the time the record is created. The ON UPDATE CURRENT_TIMESTAMP attribute causes the column to be updated with the current timestamp when the value of another column in the record is changed from its current value.

For backward compatibility with older versions of MySQL (before 4.1), if you do not specify either of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes when creating a table, the MySQL server automatically assigns both attributes to the first TIMESTAMP column:

 mysql> CREATE TABLE ts_test1 (
-> ts1 TIMESTAMP,
-> ts2 TIMESTAMP,
-> data CHAR(30)
-> );

mysql> DESCRIBE ts_test1;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-------+
| ts1 | timestamp | YES | | CURRENT_TIMESTAMP | |
| ts2 | timestamp | YES | | 0000-00-00 00:00:00 | |
| data | char(30) | YES | | NULL | |
+-------+-----------+------+-----+---------------------+-------+

mysql> INSERT INTO ts_test1 (data) VALUES ('original_value');

mysql> SELECT * FROM ts_test1;
+---------------------+---------------------+----------------+
| ts1 | ts2 | data |
+---------------------+---------------------+----------------+
| 2005-01-04 14:45:51 | 0000-00-00 00:00:00 | original_value |
+---------------------+---------------------+----------------+

mysql> . . . time passes . . .

mysql> UPDATE ts_test1 SET data='updated_value';

mysql> SELECT * FROM ts_test1;
+---------------------+---------------------+---------------+
| ts1 | ts2 | data |
+---------------------+---------------------+---------------+
| 2005-01-04 14:46:17 | 0000-00-00 00:00:00 | updated_value |
+---------------------+---------------------+---------------+
The same behavior occurs if you specify both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP explicitly for the first TIMESTAMP column. It is also possible to use just one of the attributes. The following example uses DEFAULT CURRENT_TIMESTAMP, but omits ON UPDATE CURRENT_TIMESTAMP. The result is that the column is initialized automatically, but not updated when the record is updated:
mysql> CREATE TABLE ts_test2 ( 
-> created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> data CHAR(30) -> );

mysql> INSERT INTO ts_test2 (data) VALUES ('original_value');

mysql> SELECT * FROM ts_test2;
+---------------------+----------------+
| created_time | data |
+---------------------+----------------+
| 2005-01-04 14:46:39 | original_value |
+---------------------+----------------+

mysql> . . . time passes . . .
mysql> UPDATE ts_test2 SET data='updated_value';

mysql> SELECT * FROM ts_test2;
+---------------------+---------------+
| created_time | data |
+---------------------+---------------+
| 2005-01-04 14:46:39 | updated_value |
+---------------------+---------------+

Note that even though the record is updated, the created_time column is not. In versions of MySQL Server before 4.1, the UPDATE statement would have caused the created_time column to be updated as well.

The next example demonstrates how to create a TIMESTAMP column that is not set to the current timestamp when the record is created, but only when it is updated. In this case, the column definition includes ON UPDATE CURRENT_TIMESTAMP but omits DEFAULT CURRENT_TIMESTAMP:

 mysql> CREATE TABLE ts_test3 ( 
-> updated_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30) -> );

mysql> INSERT INTO ts_test3 (data) VALUES ('original_value');

mysql> SELECT * FROM ts_test3;
+---------------------+----------------+
| updated_time | data |
+---------------------+----------------+
| 0000-00-00 00:00:00 | original_value |
+---------------------+----------------+

mysql> UPDATE ts_test3 SET data='updated_value';

mysql> SELECT * FROM ts_test3;
+---------------------+---------------+
| updated_time | data |
+---------------------+---------------+
| 2005-01-04 14:47:10 | updated_value |
+---------------------+---------------+

Note that you can choose to use CURRENT_TIMESTAMP with neither, either, or both of the attributes for a single TIMESTAMP column, but you cannot use DEFAULT CURRENT_TIMESTAMP with one column and ON UPDATE CURRENT_TIMESTAMP with another:

mysql> CREATE TABLE ts_test4 ( 
-> created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30) -> );

ERROR 1293 (HY000): Incorrect table definition;
there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Nevertheless, you can achieve the effect of having one column with the creation time and another with the time of the last update. To do this, create two TIMESTAMP columns. Define the column that should hold the creation time with DEFAULT 0 and explicitly set it to NULL whenever you INSERT a new record. Define the column that should hold the updated time with DEFAULT CURRENT_TIMESTAMP:

mysql> CREATE TABLE ts_test5 ( 
-> created TIMESTAMP DEFAULT 0,
-> updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> data CHAR(30) -> ); mysql> INSERT INTO ts_test5 (created, data)
-> VALUES (NULL, 'original_value');

mysql> SELECT * FROM ts_test5;
+---------------------+---------------------+----------------+
| created | updated | data |
+---------------------+---------------------+----------------+
| 2005-01-04 14:47:39 | 0000-00-00 00:00:00 | original_value |
+---------------------+---------------------+----------------+


mysql> UPDATE ts_test5 SET data='updated_value';

mysql> SELECT * FROM ts_test5;
+---------------------+---------------------+---------------+
| created | updated | data |
+---------------------+---------------------+---------------+
| 2005-01-04 14:47:39 | 2005-01-04 14:47:52 | updated_value |
+---------------------+---------------------+---------------+

By default, MySQL defines TIMESTAMP columns as NOT NULL and stores the current timestamp in the column if you assign it a value of NULL. If you want to be able to store NULL in a TIMESTAMP column, you must explicitly write the column definition to allow NULL when creating or altering the column:

mysql> CREATE TABLE ts_null (ts TIMESTAMP NULL); 

mysql> DESCRIBE ts_null;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| ts | timestamp | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+

Note that specifying NULL for a TIMESTAMP column implicitly changes its default value from CURRENT_TIMESTAMP to NULL if no explicit default value is given.

Related MySQL FAQs to the Above FAQ

What-is-BIT-Data-Type-in-MySQL What is BIT Data Type in MySQL?

What-are-numeric-data-types-in-MySQL What are numeric data types in MySQL?

What-are-integer-data-types-in-MySQL What are integer data types in MySQL?

What-are-float-data-types-in-MySQL What are float data types in MySQL?

What-is-fixed-point-and-decimal-data-type-in-MySQL What is fixed point and decimal data type in MySQL?

What-is-MySQL-default-character-set What is MySQL default character set?

What-is-difference-between-binary-and-non-binary-string-data-types What is difference between binary and non-binary string data types?

What-are-ENUM-and-SET-Data-Types-in-MySQL What are ENUM and SET Data Types in MySQL?

What-is-difference-between-CHAR-AND-VARCHAR-data-types What is difference between CHAR AND VARCHAR data types?

What-are-string-data-types-in-MySQL-5 What are string data types in MySQL 5?

How-to-reformat-date-values-in-MySQL How to reformat date values in MySQL?

How-to-reformat-Time-values-in-MySQL How to reformat Time values in MySQL?

What-are-Temporal-Data-Types-in-MySQL What are Temporal Data Types in MySQL?

What-are-DATE-TIME-DATETIME-and-YEAR-Data-Types-in-MySQL What are DATE, TIME, DATETIME and YEAR Data Types in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.