Summary: The MySQL TIMESTAMP is a temporal data type that store combination of date and time values. In this tutorial you will learn how MySQL TIMESTAMP values are stored in the database and how to use automatic initialization and automatic update to create created on and last changed on column in a table.
How MySQL TIMESTAMP stored in the database
The format of MySQL TIMESTAMP column is ‘YYYY-MM-DD HH:MM:SS’ which is fixed at 19 characters. The MySQL TIMESTAMP are stored as four bytes number of second since the first day of UNIX time which is ‘1970-01-01 00:00:01’. The upper end of range correspond to maximum four bytes value of UNIX time which is ‘2038-01-19 03:14:07’. Therefore the TIMESTAMP column has a range of values from ‘1970-01-01 00:00:01’ to ’2038-01-19 03:14:07′.
The values of the MySQL TIMESTAMP columns depend on connection’s time zone. When insert values for MySQL TIMESTAMP columns, they are converted to Universal Coordinated Time (UTC) from connection’s time zone. When you select the value, the server converts it back from UTC to the connection’s time zone so you have the same value that you inserted. However if another client with different time zone connects to the server to select value from MySQL TIMESTAMP column, it will see the value adjusted to its time zone. MySQL allows you to change your own time zone when you connect to it so you can see this effect by using a single connection.
Let’s try in our sample database to see this effect.
1 |
CREATE TABLE test_timestamp('t1' TIMESTAMP); |
3 |
SET time_zone='+00:00'; |
5 |
INSERT INTO test_timestamp VALUES('2008-01-01 00:00:01'); |
+---------------------+
| t1 |
+---------------------+
| 2008-01-01 07:00:01 |
+---------------------+
1 row in set (0.00 sec)
The SQL commands can be explained as follows:
- First we created a table called test_TIMESTAMP.
- Next we set our time zone to UTC.
- Then we insert a TIMESTAMP value into the table test_timestamp.
- Finally we select it to see the value we inserted.
Now can set our time zone to a different time zone and see what value we get from database server:
1 |
SET time_zone =’+03:00’; |
+---------------------+
| t1 |
+---------------------+
| 2008-01-01 03:00:01 |
+---------------------+
1 row in set (0.00 sec)
As you see, we get adjusted value to our new time zone.
INSERT and UPDATE TIMESTAMP column
If you omit the MySQL TIMESTAMP column’s value in the INSERT statement or you set it to NULL, it will be automatically set to current TIMESTAMP. This characteristic of MySQL TIMESTAMP is known as automatic initialization.
In the table which has a MySQL TIMESTAMP column, if you change other columns’ value, the MySQL TIMESTAMP column will be automatic updated to the current TIMESTAMP. The change only accepted if you change its current value to different values in order to have TIMESTAMP column updated. This characteristic of TIMESTAMP called automatic update. Note that only one column can be designated as a TIMESTAMP column which has automatic update.
The MySQL TIMESTAMP columns are design in a table that you need to save the created date and last change date for the records. By applying automatic initialization and automatic update of MySQL TIMESTAMP column you can design the table as follows:
3 |
created_on TIMESTAMP DEFAULT 0 |
4 |
changed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
So when you insert a new record, just omit two TIMESTAMP columns or set them to NULL. The both created_on and changed_on column will be inserted as the current TIMESTAMP.
When you update, omit both TIMESTAMP columns, the changed_on column’s value will be automatic updated if there is any change in other columns’ values.
In this tutorial, you’ve learned how MySQLTIMESTAMP data stored in the MySQL database table and how to use its characteristics to design the created on and last changed on columns.
Related posts:
- Understanding MySQL Table Types
