How To Set Timestamp In Sql

Summary: in this tutorial, you will acquire nearly MySQL TIMESTAMP information type andTIMESTAMP cavalcade features such as automatic initialization and updating.

Introduction to MySQL `TIMESTAMP` information type

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

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

When yous insert a TIMESTAMP value into a table, MySQL converts it from your connection's time zone to UTC for storing.

When yous query a TIMESTAMP value, MySQL converts the UTC value back to your connectedness'south time zone. Note that this conversion does not take place for other temporal data types such as DATETIME.

Past default, the connection time zone is the MySQL Server's time zone. And yous can utilize a different time zone when you lot connect to MySQL Server.

When y'all call back 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. Every bit long equally you don't alter the fourth dimension zone, yous can go the same TIMESTAMP value that you stored.

MySQL `TIMESTAMP` fourth dimension zone example

Let's take an example to see how MySQL handles TIMESTAMP values.

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

CREATE Tabular array test_timestamp ( t1 TIMESTAMP );

Code language: SQL (Structured Query Language) ( sql )

Second, fix the session'southward time zone to '+00:00' UTC by using the SET time_zone statement.

Set up time_zone='+00:00';

Code language: SQL (Structured Query Linguistic communication) ( sql )

Third, insert a TIMESTAMP value into the test_timestamp table.

                                          INSERT                INTO                test_timestamp(t1)                VALUES('2008-01-01 00:00:01');            
                          Code linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )

Fourth, select the TIMESTAMP value from the test_timestamp tabular array.

                                          SELECT                t1                FROM                test_timestamp;            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

5th, set up the session'due south time zone to a unlike time zone to see what value we will go from the database server:

Fix time_zone ='+03:00';

Code linguistic communication: SQL (Structured Query Language) ( sql )

Finally, query data from the tabular array:

                                          SELECT                t1                FROM                test_timestamp;            
                          Code linguistic communication:              SQL (Structured Query Language)              (              sql              )

As you see, nosotros received a different time value adjusted to the new fourth dimension zone.

Automatic initialization and updating for `TIMESTAMP` columns

Consider the following example.

First, creates a table namedcategories:

CREATE Tabular array categories ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) Not Goose egg, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Code language: SQL (Structured Query Language) ( sql )

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

2d, inserts a new row into the categories table without specifying the value for the created_at column:

                                          INSERT                INTO                categories(name)                VALUES                ('A');            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

                                          SELECT                *                FROM                categories;            
                          Lawmaking language:              SQL (Structured Query Language)              (              sql              )

Every bit yous can see from the output, MySQL used the timestamp at the time of inserting as a default value for the created_at column.

So aTIMESTAMP column can be automatically initialized to the electric current timestamp for inserted rows that specify no value for the column. This characteristic is chosen automated initialization.

Third, add together a new cavalcade named updated_at to the categories tabular array.

Alter TABLE categories ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Lawmaking language: SQL (Structured Query Language) ( sql )

The default value of the updated_at cavalcade is CURRENT_TIMESTAMP.

And, nosotros have a new clause ON UPDATE CURRENT_TIMESTAMP that follows the DEFAULT CURRENT_TIMESTAMP clause. Let's see its event.

Fourth, inserts a new row into the categories table.

                                          INSERT                INTO                categories(name)                VALUES('B');            
                          Lawmaking linguistic communication:              SQL (Structured Query Linguistic communication)              (              sql              )

Fifth, query data from the categories table:

                                          SELECT                *                FROM                categories;            
                          Lawmaking linguistic communication:              SQL (Structured Query Language)              (              sql              )

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

6th, update the value in the column name of the row id 2:

UPDATE categories Set proper noun = 'B+' WHERE id = two;

Lawmaking language: SQL (Structured Query Language) ( sql )

Seventh, query data from the categories table to check the update:

                                          SELECT                *                FROM                categories                WHERE                id                =                ii;            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )

Detect that the value in the updated_at column changed to the timestamp at the fourth dimension the row was updated.

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

The column updated_at is referred to equally an auto-updated column.

Annotation that if you lot execute the UPDATE statement to update the same value for the proper name column, the updated_at column will non be updated.

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

Lawmaking language: SQL (Structured Query Language) ( sql )

The value in the updated_at remains unchanged.

For more information on automatic initialized and updating, delight check it out the fourth dimension initialization on MySQL website.

Equally of MySQL v.6.5, the DATETIME columns besides have automated initialization and updating features. In addition, the DEFAULT_CURRENT_TIMESTAMP and ON UPDATE CURRENT TIMESTAMP can be applied to multiple columns.

In this tutorial, you accept learned about MySQL TIMESTAMP data type and how to apply automated initialization and updating features of TIMESTAMP columns.

Was this tutorial helpful?