A2-04-11.MySQL DATA TYPES- MySQL TIMESTAMP

转载自：http://www.mysqltutorial.org/mysql-timestamp.aspx

Home / Basic MySQL Tutorial / MySQL TIMESTAMP

MySQL TIMESTAMP

 

Summary: in this tutorial, you will learn about MySQL TIMESTAMP and  TIMESTAMP column features such as automatic initialization and updating.

Introduction to MySQL TIMESTAMP

The MySQL TIMESTAMP is a temporal data type that holds the combination of date and time. The format of a TIMESTAMP column is YYYY-MM-DD HH:MM:SS which is fixed at 19 characters.

The TIMESTAMP value has a range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

When you insert a TIMESTAMP value into a table, MySQL converts it from your connection’s time zone to UTC for storage. When you query a TIMESTAMP value, MySQL converts the UTC value back to your connection’s time zone. Notice that this conversion does not take place for other temporal data types such as DATETIME.

By default, the connection time zone is the MySQL database server’s time zone. You can use a different time zone when you connect to MySQL database server.

When you retrieve a TIMESTAMP value that was inserted by a client in a different time zone, you will get a value that is not the same as the value stored in the database. As long as you don’t change the time zone, you can get the same TIMESTAMP value that you stored.

MySQL TIMESTAMP time zone example

Let’s look at an example to see how MySQL handles TIMESTAMP values.

First, created a new table named test_timestamp that has a TIMESTAMP column: t1;

 

1 2 3

CREATE TABLE IF NOT EXISTS test_timestamp ( t1  TIMESTAMP );

Second, set session the time zone to ‘+00:00’ UTC by using the SET time_zone statement.

 

1	SET time_zone='+00:00';

Third, insert a TIMESTAMP value into the test_timestamp table.

 

1 2	INSERT INTO test_timestamp VALUES('2008-01-01 00:00:01');

Fourth, select the TIMESTAMP value from the test_timestamp table.

 

1 2 3 4

SELECT     t1 FROM     test_timestamp;

Fifth, set the session’s time zone to a different time zone to see what value we get back from the database server:

 

1 2 3 4

SET time_zone ='+03:00';   SELECT t1 FROM test_timestamp;

As you see, we received a different time value adjusted to the new time zone.

Automatic initialization and updating for TIMESTAMP columns

Let’s start with an example.

The following statement creates a table named  categories:

 

1 2 3 4 5

CREATE TABLE categories (     id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(255) NOT NULL,     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

In the categories table, the created_at column is a TIMESTAMP column whose default value is set to CURRENT_TIMESTAMP.

The following statement inserts a new row into the categories table without specifying the value for the created_at column:

 

1 2	INSERT INTO categories(name) VALUES ('A');

 

 

1 2 3 4

SELECT     * FROM     categories;

As you can see, MySQL used the timestamp (at the time it inserted the row ) to initialize for the created_at column.

So a TIMESTAMP column can be automatically initialized to the current timestamp for inserted rows that specify no value for the column. This feature is called automatic initialization.

We will add a new column named updated_at to the categories table.

 

1 2	ALTER TABLE categories ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

The default value of the updated_at column is CURRENT_TIMESTAMP.  However, there is a clause ON UPDATE CURRENT_TIMESTAMP that follows the DEFAULT CURRENT_TIMESTAMP clause. We will find out its meaning soon.

The following statement inserts a new row into the categories table.

 

1 2	INSERT INTO categories(name) VALUES('B');

 

 

1	SELECT * FROM categories;

The default value of the created_at column is the timestamp when the row was inserted.

Now, we update the value in the name column of the row with id 2 and query data from the categoriestable .

 

1 2 3 4 5

UPDATE categories SET     name = 'B+' WHERE     id = 2;

 

 

1 2 3 4 5 6

SELECT     * FROM     categories WHERE     id = 2;

Notice that the value in the updated_at column changed to the timestamp at the time the row was updated.

The ability of a TIMESTAMP column to be automatically updated to the current timestamp when the value in any other column in the row changed from it current value is called automatic updating.

The updated_at column is known as an auto-updated column.

Note that if you execute the UPDATE statement to update a same value for the name column, the updated_at column will not be updated.

 

1 2 3 4 5

UPDATE categories SET     name = 'B+' WHERE     id = 2;

The value in the updated_at remains unchanged.

For more information on automatic initialized and updating, please check it out the time initialization on MySQL website.

As of MySQL 5.6.5, the DATETIME columns also have automatic initialization and updating features. In addition, the DEFAULT_CURRENT_TIMESTAMP and ON UPDATE CURRENT TIMESTAMP can be applied to multiple columns.

In this tutorial, we have introduced you to MySQL TIMESTAMP data type and shown you how to use automatic initialization and updating features of TIMESTAMP columns work.