MySQL in timestamp set the default time to the current time

When we save the data into the database will probably use a script such as PHP to get a time to save to MySQL, in fact, MySQL can directly use the timestamp data type to implement the default type, the next look.

Many times, in order to be simple, we need to set the Time field to the current time when designing the data table. However, in MySQL, the default value cannot use the function, that is, you cannot set a column, the default is now () the processing. What about that?

The TIMESTAMP data type. When inserting, ignore the column.

The code is as follows

DT TIMESTAMP&NBSP /* equivalent to */ DT timestamp  default current_timestamp  on UPDATE current_timestamp   CREATE table Testb (    id   int PRIMARY KEY,   val  varchar),   DT timestamp  );   /* We try to insert the following data to see */ INSERT INTO Testb (ID, Val) VALUES (1, ' A '); INSERT into Testb (ID, Val) VALUES (2, ' B '); Results SELECT * from Testb; +----+------+---------------------+ | id | val  | dt                   | +----+------+---------------------+ |  1 | a    | 2014-08-21 14:24:20 | |  2 | b    | 2014-08-21 14:24:21 | +----+------+---------------------+ 2 rows in Set (0.00 sec)

Note: MySQL's timestamp type time range between ' 1970-01-01 00:00:01 ' and ' 2038-01-19 03:14:07 ', exceeding this range the value is recorded as ' 0000-00-00 00:00:00 '

An important feature of this type is that the time of the save is closely related to time zone, the time range mentioned above is UTC (Universal time coordinated) standard, refers to longitude 0 degrees of standard times, China's daily life time zone in the capital Beijing is located in the 8th District of the Eastern Hemisphere as the benchmark, Unified use of the East 8 zone time (commonly known as Beijing Time), 8 hours earlier than UTC, the server's time zone settings are also in accordance with this standard, so the time range corresponding to the timestamp should be calibrated to ' 1970-01-01 08:00:01 ' and ' 2038-01-19 11:14:07 ', which means that 1970-1-1 08:00:01 in East Eight is equivalent to UTC 1970-1-1 00:00:01.
It is important to note that the timestamp type is not only related to the time zone when the record was written, but also to the time zone when it is displayed, for example:

The code is as follows

mysql> desc J1_DT; +-------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-------+ | DT | Timestamp |     NO | |       Current_timestamp | | +-------+-----------+------+-----+-------------------+-------+ 1 row in Set (0.00 sec) mysql> INSERT into J1_DT values (' 1970-01-01 08:00:01 '); Query OK, 1 row Affected (0.00 sec) Mysql> select * from J1_dt; +---------------------+ | DT | +---------------------+ | 1970-01-01 08:00:01 | +---------------------+ 1 row in Set (0.00 sec) Mysql> set time_zone= ' +0:00 '; Query OK, 0 rows Affected (0.00 sec) Mysql> select * from J1_dt; +---------------------+ | DT | +---------------------+ | 1970-01-01 00:00:01 | +---------------------+ 1 row in Set (0.00 sec) Mysql> set time_zone= ' +1:00 '; Query OK, 0 rows Affected (0.00 sec) Mysql> select * from J1_dt; +---------------------+ | DT | +---------------------+ | 1970-01-01 01:00:01 | +---------------------+ 1 row in Set (0.00 sec)

As shown above, depending on the time zone, the date displayed is different, which is exactly the time zone characteristic of the timestamp type in the MySQL date type.
If the value inserted into the timestamp type column exceeds the specified range, the actual actual saved value is ' 0000-00-00 00:00:00 ', and a warning message is triggered:

The code is as follows

mysql> set time_zone= '; Query OK, 0 rows Affected (0.00 sec) Mysql> select * from J1_dt; +---------------------+ | DT | +---------------------+ | 1970-01-01 08:00:01 | +---------------------+ 1 row in Set (0.00 sec) mysql> INSERT into J1_DT values (' 1970-01-01 00:00:01 '); Query OK, 1 row affected, 1 Warning (0.00 sec) Mysql> Show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1264 | Out of range value adjusted to column ' dt ' at row 1 | +---------+------+------------------------------------------------------+ 1 row in Set (0.00 sec) Mysql> select * from J1_dt; +---------------------+ | DT | +---------------------+ | 1970-01-01 08:00:01 | | 0000-00-00 00:00:00 | +---------------------+ 2 rows in Set (0.00 sec)

The warning message that is triggered is only a warning at the MySQL level and not an error, and the front-end application's try catch is not captured, however, because the actual data written is not expected, or there is a risk of burying some of these hidden dangers, once exposed, it is possible to trigger a front-end application exception.
For the timestamp type, it is important to understand the concept of time zone in the actual application, when setting the timestamp column default value, and the actual assignment, it is necessary to explicitly write the value of the actual saving state, as far as possible to avoid embedding hidden dangers. For existing records that have been faulted, consider using bulk update and modifying the table structure in a way that can be handled.

About timestamp minimum and maximum values some test examples

The code is as follows

-mysql Timestamp min value CREATE TABLE ' Test ' ( ' ID ' int (one) not NULL DEFAULT ' 0 ', ' NAME ' varchar (DEFAULT NULL), ' HireDate ' timestamp not NULL the DEFAULT current_timestamp on UPDATE current_timestamp, PRIMARY KEY (' ID ') ) Engine=innodb DEFAULT CHARSET=GBK; INSERT into test values (1, ' Zjadolf ', ' 1970-01-01 08:01:00 ')--insertion succeeded Category Timestamp Duration Message line Position Statement 2010/9/27 14:57:43 0:00:00.003 1 rows affected 2 0 INSERT into test values (2, ' Zjadolf ', ' 1970-01-01 08:00:00 ')-- Category Timestamp Duration Message line Position Error 2010/9/27 14:58:35 0:00:00.000 MySQL Database error:incorrect datetime value: ' 1970-01-01 08:00:00 ' for column ' hir Edate ' at row 1 2 0 INSERT into test values (3, ' Zjadolf ', ' 1970-01-01 08:00:30 ')--ok can be inserted Category Timestamp Duration Message line Position Statement 2010/9/27 15:04:48 0:00:00.007 1 rows affected 2 0 INSERT into test values (1, ' Zjadolf ', ' 1970-01-01 08:00:01 ')--ok can be inserted Category Timestamp Duration Message line Position Statement 2010/9/27 15:06:39 0:00:00.006 1 rows affected 2 0 INSERT into test values (1, ' Zjadolf ', ' 1970-01-01 08:00:00 ')--no error can be inserted Category Timestamp Duration Message line Position Error 2010/9/27 15:07:13 0:00:00.000 MySQL Database error:incorrect datetime value: ' 1970-01-01 08:00:00 ' for column ' hir Edate ' at row 1 2 0

It looks like 1970-01-01 08:00:01 should be the minimum allowable value of timestamp in MySQL, you should pay attention to the following
Verify that we insert the following record should be error:

The code is as follows
INSERT into test values (1, ' Zjadolf ', ' 1969-01-01 08:01:02 ') Category Timestamp Duration Message line Position Error 2010/9/27 15:09:29 0:00:00.000 MySQL Database error:incorrect datetime value: ' 1969-01-01 08:01:02 ' for column ' hir Edate ' at row 1 2 0

--True Error

No wonder I was testing using Toad for MySQL import tool importing 1000000 data to date: ' 1970-01-01 07:57:09 ' This data is an error! Results less than this date are not in the record! What a tragedy!

View the official explanation document as follows:
The timestamp value cannot be earlier than 1970 or later than 2037.

Use the datetime type when you need a value that contains both date and time information. MySQL retrieves and displays the datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. ("support" means that although the previous value may work, there is no guarantee).

Use the date type when you only need a DateTime value and do not need the time portion. MySQL retrieves and displays the date value in the ' YYYY-MM-DD ' format. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '.

The properties of the timestamp column type are not fixed, depending on the MySQL version and the SQL mode that the server is running. These properties are described later in this section.

It seems that if you want to support a longer date, you need to use the datetime type.

The code is as follows
INSERT into T4 values (1, ' 1900-01-01 08:01:02 ') Category Timestamp Duration Message line Position Statement 2010/9/27 15:21:53 0:00:00.051 1 rows affected 15 0

So it seems that the MySQL timestamp and DB2 timestamp is not the same!
DB2 's timestamp type does not have the same limitations as MySQL

MySQL in timestamp set the default time to the current time