MySQL timestamp set default time to current time

Many times, in order to be simple, we need to set the Time field to the current time when designing the datasheet. In MySQL, however, the default value cannot be used, that is, you cannot set a column, and the default value is now (). What about it?

TIMESTAMP data type. When inserting, ignore the column.

The code is as follows

Copy Code

DT TIMESTAMP /* is equivalent to */ DT timestamp  default current_timestamp  on UPDATE current_timestamp   CREATE TABLE Testb (   id   int PRIMARY KEY,   val  varchar (ten),   DT TIMESTAMP ); &NBSP /* Let's try inserting the 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    &nb sp;             | +----+------+---------------------+ |  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 spent saving is closely related to the timezone, which is the UTC (Universal Time coordinated) standard, which refers to the standard time of longitude 0 degrees, the time zone in our daily life is based on the 8th area of the Old World in the capital Beijing, Unified use of the East 8 area 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 timestamp should be calibrated to ' 1970-01-01 08:00:01 ' and ' 2038-01-19 11:14:07 ', that is to say, East Eight, 1970-1-1 08:00:01 is equivalent to UTC 1970-1-1 00:00:01.
Note that the timestamp type is not only relevant to the time zone when the record was written, but also to the time zone when it was displayed, for example:

The code is as follows

Copy Code

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, the date displayed varies according to the time zone, which is the unique time zone feature of the timestamp type in the MySQL date type.
If the value inserted into the timestamp type column is outside the specified range, the actual saved value is ' 0000-00-00 00:00:00 ' and triggers a warning message:

The code is as follows

Copy Code

Mysql> set time_zone= ' +8:00 '; 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 for 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)

Triggered warning messages at the MySQL level is only a warning rather than an error, the front-end application of Try catch is not captured, however, because the actual written data is not expected, it is possible to bury some hidden dangers, once exposed, it is possible to trigger the front-end application exception.
For timestamp type, it is important to understand the concept of time zone in practical application, when setting the default value of timestamp column, and when the value of the actual assignment must be clearly written to save the state, try to avoid embedding hidden trouble. For existing records that have already been faulted, you can consider handling them by batch update and modifying the table structure.

Some test examples about timestamp minimum value and maximum value

The code is as follows

Copy Code

-mysql Timestamp Minimum value CREATE TABLE ' Test ' ( ' ID ' int (one) not NULL DEFAULT ' 0 ', ' NAME ' varchar DEFAULT NULL, ' HireDate ' timestamp not NULL 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 ')--inserted successfully 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 insert 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 insert 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 timestamp in MySQL, you should pay attention to when you use
Verify that we insert the following records should be the error:

The code is as follows	Copy Code
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

--that's a sure error.

No wonder I'm testing using Toad for MySQL import Tool 1000000 data Guide to date: ' 1970-01-01 07:57:09 ' when the data error! The result is less than the record of this date. What a tragedy!

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

Use datetime types when you need values that contain both date and time information. MySQL retrieves and displays 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 previous values may work, there is no guarantee).

Use the date type when you want only the value of the dates and do not need the time section. MySQL retrieves and displays date values 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 a datetime type.

The code is as follows	Copy Code
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

It seems that MySQL's timestamp and DB2 's timestamp are not the same!
DB2 timestamp types do not have MySQL-like restrictions