MySQL Date type datetime and timestamp

How do I represent the current time in MySQL?

In fact, the expression is still quite a lot of, summarized as follows:

Current_timestamp

Current_timestamp ()

Now ()

LocalTime

LocalTime ()

Localtimestamp

Localtimestamp ()

Ii. Comparison of Timestamp and datetime

A complete date format is as follows: Yyyy-mm-dd Hh:mm:ss[.fraction], which can be divided into two parts: the date part and the time part, where the date part corresponds to "YYYY-MM-DD" in the format, and the time portion corresponds to the format "hh:mm : Ss[.fraction] ". For the date field, it supports only the date part, and if the contents of the time part are inserted, it discards the contents of that section and prompts a warning.

As shown below:

Mysql> CREATE TABLE Test (ID int,hiredate date); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (1, ' 20151208000000 '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into test values (1, ' 20151208104400 '); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> Show warning; Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' warning ' on line 1MYSQL&G T SELECT * FROM test;+------+------------+| ID   | hiredate   |+------+------------+|    1 | 2015-12-08 | |    1 | 2015-12-08 |+------+------------+2 rows in Set (0.00 sec)

Note: The first reason for not prompting warning is that the time part is 0.

The same points for timestamp and datetime:

1> Both can be used to represent a date of type Yyyy-mm-dd hh:mm:ss[.fraction].

Different points of timestamp and datetime:

1> the two are not stored the same way.

For timestamp, it stores the time the client has inserted from the current time zone into UTC (World standard Time). When queried, it is then converted to the client's current time zone for return.

For datetime, no changes are made, essentially as-is input and output.

Now, let's verify

First create two test tables, one using the timestamp format and one using the DateTime format.

Mysql> CREATE TABLE Test (ID int,hiredate timestamp); Query OK, 0 rows affected (0.01 sec) mysql> insert into test values (1, ' 20151208000000 '); Query OK, 1 row Affected (0.00 sec) mysql> CREATE TABLE test1 (ID int,hiredate datetime); Query OK, 0 rows affected (0.01 sec) mysql> insert into test1 values (1, ' 20151208000000 '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from test;+------+---------------------+| ID   | hiredate            |+------+---------------------+|    1 | 2015-12-08 00:00:00 |+------+---------------------+1 row in Set (0.01 sec) mysql> select * from test1;+------+--------- ------------+| ID   | hiredate            |+------+---------------------+|    1 | 2015-12-08 00:00:00 |+------+---------------------+1 row in Set (0.00 sec)

Both outputs are the same.

Second, modify the time zone of the current session

Mysql> Show variables like '%time_zone% '; +------------------+--------+| Variable_name    | Value  |+------------------+--------+| System_time_zone | CST    | | time_zone        | SYSTEM |+------------------+--------+2 rows in Set (0.00 sec) mysql> set time_zone= ' +0:00 '; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from test;+------+---------------------+| ID   | hiredate            |+------+---------------------+|    1 | 2015-12-07 16:00:00 |+------+---------------------+1 row in Set (0.00 sec) mysql> SELECT * from test1;+------+--------- ------------+| ID   | hiredate            |+------+---------------------+|    1 | 2015-12-08 00:00:00 |+------+---------------------+1 row in Set (0.01 sec)

The "CST" above refers to the system time of the host on which MySQL is located and is the abbreviation for Chinese standard Time ut+8:00

The result shows that the time returned in test is 8 hours ahead of time, while the test1 is unchanged. This fully verifies the difference between the two.

2> the time range they can store is different.

The time range timestamp can store is: ' 1970-01-01 00:00:01.000000 ' to ' 2038-01-19 03:14:07.999999 '.

The time range that DateTime can store is: ' 1000-01-01 00:00:00.000000 ' to ' 9999-12-31 23:59:59.999999 '.

Summary: Timestamp and datetime are not much different except for the storage range and storage method. Of course, timestamp is more appropriate for business across time zones.

III. automatic initialization and update of timestamp and datetime

First, let's take a look at the following actions

Mysql> CREATE TABLE Test (ID int,hiredate timestamp); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test (ID) values (1); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * from test;+------+---------------------+| ID   | hiredate            |+------+---------------------+|    1 | 2015-12-08 14:34:46 |+------+---------------------+1 row in Set (0.00 sec) mysql> Show create TABLE test\g************* 1. Row ***************************       table:testcreate table:create Table ' test ' (  ' id ' int (one) DEFAULT NULL,  '  HireDate ' timestamp not NULL default current_timestamp on UPDATE current_timestamp) engine=innodb default Charset=latin11 Row in Set (0.00 sec)

It seems strange that I did not insert the HireDate field, its value is automatically changed to the current value, and when I created the table, I did not define the "DEFAULT Current_" shown in the "Show create TABLE Test\g" result. TIMESTAMP on UPDATE current_timestamp ".

In fact, this feature is auto-initialized and automatically updated (Automatic initialization and Updating).

Automatic initialization means that if there is no explicit assignment to the field (for example, the HireDate field in the previous example), it is automatically set to the current system time.

Automatic update means that if you modify other fields, the value of the field is automatically updated to the current system time.

It is related to the "Explicit_defaults_for_timestamp" parameter.

By default, the value of this parameter is off, as follows:

Mysql> Show variables like '%explicit_defaults_for_timestamp% '; +---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| Explicit_defaults_for_timestamp | OFF   |+---------------------------------+-------+1 row in Set (0.00 sec)

Let's look at the description of the official document:

By default, the first TIMESTAMP column have both default Current_timestamp and on UPDATE current_timestamp if neither is SP Ecified explicitly.

Many times, this is not what we want, how to disable it?

1. Set the value of "Explicit_defaults_for_timestamp" to ON.

2. The value of "Explicit_defaults_for_timestamp" is still off, there are two ways to disable

1> Specify a default value for this column with the defaults clause

2> Specifies the Null property for the column.

As shown below:

Mysql> CREATE TABLE test1 (ID int,hiredate timestamp null); Query OK, 0 rows affected (0.01 sec) mysql> Show create TABLE test1\g*************************** 1. Row ***************************       table:test1create table:create Table ' test1 ' (  ' id ' int (one-by-one) DEFAULT NULL,  ' hiredate ' timestamp null default NULL) ENGINE=INNODB default Charset=latin11 row in Set (0.00 sec) mysql> CREATE table Test2 (ID int,hiredate timestamp default 0); Query OK, 0 rows affected (0.01 sec) mysql> Show create TABLE test2\g*************************** 1. Row ***************************       table:test2create table:create Table ' test2 ' (  ' id ' int (one-by-one) DEFAULT NULL,  ' HireDate ' timestamp not NULL default ' 0000-00-00 00:00:00 ') engine=innodb default Charset=latin11 row in Set (0.00 sec)

Prior to MySQL version 5.6.5, Automatic initialization and updating only apply to timestamp, and in one table, a maximum of one timestamp field is allowed to take this attribute. Starting with MySQL 5.6.5, Automatic initialization and updating are available for timestamp and datetime and do not limit the number

MySQL Date type datetime and timestamp