Usage of mysql timestamp

Preface:

Timestamp fields are often used in MySQL. For example, when we need to record the time when a row of data is created or modified, we usually use the timestamp field. This article mainly introduces the usage and related parameters of the timestamp field. I hope you can have a deeper understanding of timestamp after reading it.

1. Introduction to TIMESTAMP field type

The timestamp field type can store time type data. The time range that timestamp can store is: '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. The timestamp field occupies 4 bytes. In MySQL 5.7, timestamp can specify precision, that is, fsp in TIMESTAMP(fsp) can specify an optional value between 0 and 6 to represent fractional seconds precision. A value of 0 means no decimal part, and if omitted, the default precision is 0.

Let's do a simple test:

# timestamp can specify precisionmysql> CREATE TABLE `stu_tb` (
  -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
  -> `stu_id` int(11) NOT NULL COMMENT 'student number',
  -> `stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
  -> `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  -> `update_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2) COMMENT 'modification time',
  -> PRIMARY KEY (`id`)
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> show create table stu_tb\G
*************************** 1. row ***************************
    Table: stu_tb
Create Table: CREATE TABLE `stu_tb` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
 `stu_id` int(11) NOT NULL COMMENT 'Student ID',
 `stu_name` varchar(20) DEFAULT NULL COMMENT 'Student name',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
 `update_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(2) ON UPDATE CURRENT_TIMESTAMP(2) COMMENT 'Modification time',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into stu_tb (stu_id,stu_name) values ​​(1001,'dfdfa');
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time | update_time |
+----+--------+----------+---------------------+------------------------+
| 1 | 1001 | dfdfa | 2020-01-07 17:33:51 | 2020-01-07 17:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)

2. Parameters that affect timestamps

There are two main parameters that affect the display of timestamps, namely explicit_defaults_for_timestamp and time_zone. The following briefly introduces the impact of these two parameters on timestamps.

The explicit_defaults_for_timestamp parameter determines how the MySQL server handles default values ​​and NULL values ​​in timestamp columns. This variable has been introduced since MySQL 5.6.6. It is divided into global level and session level. It can be updated dynamically. The default value is OFF. For more information about this parameter, please refer to this article.

When the explicit_defaults_for_timestamp parameter is set to OFF by default, the behavior is as follows:

• By default, if the timestamp column does not explicitly specify the null attribute, the column will be automatically added with the not null attribute (other types of columns that do not explicitly specify not null allow null values). If a null value is inserted into this column, the column value will be automatically set to the current timestamp value.
• The first timestamp column in the table, if the null attribute is not specified or a default value is not specified, and an ON UPDATE clause is not specified. Then the column will automatically be added with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.
• For other TIMESTAMP columns, if the NULL and DEFAULT attributes are not explicitly specified, they are automatically set to NOT NULL DEFAULT '0000-00-00 00:00:00'. (Of course, this is related to SQL_MODE. If SQL_MODE contains 'NO_ZERO_DATE', it is actually not allowed to set its default value to '0000-00-00 00:00:00'.)

When the explicit_defaults_for_timestamp parameter is set to ON, the behavior is as follows:

• If the timestamp column does not have an explicit not null attribute, the column can be null by default. In this case, when a null value is inserted into the column, null is directly recorded instead of the current timestamp.
• The DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes are not automatically added to the first timestamp column in the table.
• If the timestamp column is added with the not null attribute and no default value is specified. At this time, if a record is inserted into the table but no value is specified for the TIMESTAMP column, an error will be reported directly if strict sql_mode is specified. If strict sql_mode is not specified, '0000-00-00 00:00:00' is inserted into the column and a warning is generated.
  

The time_zone parameter specifies the database time zone. The time_zone is divided into global level and session level. It can be updated dynamically. The default is SYSTEM, which means that the database service time zone is the same as the system time zone. When MySQL stores timestamps, the actual time stored in the database is UTC time. When querying and displaying, different times will be displayed according to the specific time zone. You can write default_time_zone in the configuration file to specify the time zone. For example, to specify China time zone 8, you can write: default_time_zone='+8:00'. The following is a simple demonstration of the display of time in different time zones:

# Initial default valuesmysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
mysql> select * from testdb.stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time | update_time |
+----+--------+----------+---------------------+------------------------+
| 1 | 1001 | dfdfa | 2020-01-07 17:33:51 | 2020-01-07 17:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)
# Change to UTC time zone and reconnect to find that the time stored in timestamp has changed mysql> set global time_zone='+0:00';
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)
mysql> select * from testdb.stu_tb;
+----+--------+----------+---------------------+------------------------+
| id | stu_id | stu_name | create_time | update_time |
+----+--------+----------+---------------------+------------------------+
| 1 | 1001 | dfdfa | 2020-01-07 09:33:51 | 2020-01-07 09:33:51.63 |
+----+--------+----------+---------------------+------------------------+
1 row in set (0.00 sec)

3. Best Practices for Using TIMESTAMP

There are many variations of TIMESTAMP in use. The following are some of the most commonly used ones:

• TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

This data column is refreshed when creating new records and modifying existing records

• TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Set this field to the current time when creating a new record, but do not refresh it when modifying it later.

• TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Set this field to 0 when creating a new record, and refresh it when modifying it later

• TIMESTAMP DEFAULT 'yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

Set this field to the given value when creating a new record, and refresh it when modifying it later

In practice, it is recommended to create the table like this:

CREATE TABLE `table_name` (
 `increment_id` INT UNSIGNED NOT NULL auto_increment COMMENT 'Auto-increment primary key',
 ...
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',
 PRIMARY KEY (`increment_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

That is, each row of data contains the creation time and modification time. The program does not need to display the specified creation time and modification time when writing data, and the default value is automatically filled in.

Regarding timestamps, here are some suggestions and experience that I hope will be helpful to you:

The data table has create_time and update_time timestamp fields, and default values ​​are set.
For timestamp fields, it is recommended not to specify or insert the now() function when inserting data.
The explicit_defaults_for_timestamp parameter is recommended to be OFF by default.
If China time is used, it is recommended that time_zone be set to '+8:00'.
It is recommended that the database time zones in different environments be kept consistent and do not change the database time zone at will.
When migrating data between different instances, pay attention to whether the time zones are the same.
It is recommended that you specify the time zone in the JDBC connection string and keep it consistent with the database.

Summarize:

This article introduces the usage of timestamp in detail. We also briefly introduce several parameters that affect timestamp. I hope that the timestamp usage practices and related suggestions will be helpful to you. In fact, these contents are recorded in the official documents. You can read the official documents more often.

PS: I have recently posted some advertising articles to make a living. I hope you don’t mind. Only when I have income can I have the motivation to continue writing. This public account will still focus on original technical articles in the future. Careful readers will find that technical articles will be pushed out almost every Friday. Please rest assured that Friday’s articles are absolutely useful. Thank you for your support.

The above is the detailed content of the usage of MySQL timestamp. For more information about MySQL timestamp, please pay attention to other related articles on 123WORDPRESS.COM!