Definition of time stamp

Time stamp means Greenwich mean time 1970 year 01 month 01 Japan 00 when 00 branch 00 The total number of seconds from to now . Generally speaking , A time stamp is a complete and verifiable data that can represent the existence of a data at a specific point in time .

• system time

2019-11-29 10:00:15

• System time with time zone

2019-11-29 10:00:15 UTC+8:00

• Greenwich mean time in system time —— Time stamp （Timestamp）

2019-11-29 02:00:15 UTC+0:00 ## Eight hours apart 

The system stores the current time without recording the time zone information The record is Greenwich mean time to Current system time The number of seconds passed
2019-11-29 10:00:15 UTC+8:00 ~ 2019-11-29 02:00:15 UTC+0:00
share 1574964015 second , 1574964015 It’s a time stamp

The process of parsing timestamps

• Japan time zone ( East nine ) The operating system reads timestamps
  1574964015 Read as Greenwich mean time , At the same time , China’s MySQL I also see this string of numbers

2019-11-29 02:00:15 UTC+0:00

• But if you add time zone information ： Convert to time with time zone – Get the Japanese time zone ( East nine )

2019-11-29 11:00:15 UTC+9:00

The principle of timestamps

Use the same time standard （ Current Greenwich mean time ）, Make all time zones read the same time .
In other words , Is to keep an absolute time , Time zone as offset , This offset is determined by the MySQL Self maintenance .

MySQL Medium datetime and timestamp

datetime

• Occupied bytes
  8 Bytes
• Value range
  1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Time zone correlation
  Time zone ambiguity -> Switch database time zone , The stored value does not change

timestamp

• Occupied bytes
  4 Bytes ,mysql Actually int Type storage timestamp Value
• Value range
  1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07
• Time zone correlation
  Time zone sense -> Switch database time zone , Stored Timestamp unchanged , however The time of the display changes

CREATE TABLE test(
col_datetime DATETIME,
col_timestamp TIMESTAMP
);
INSERT INTO test VALUES(NOW(), NOW());

SET time_zone = '+8:00';
SELECT * FROM test;

SET time_zone = '+5:00';
SELECT * FROM test;

timestamp The added value of

Default record Insert （ establish ）、 to update Time for

ALTER TABLE test ADD col_2_timestamp TIMESTAMP;
SELECT * FROM test;

By default , A watch of first The data type is TIMESTAMP Field of ,
The update of the corresponding row will trigger the field to record the update time

## Modify only col_datatime, col_timestamp It will also write the current date , col_2_timestamp Unchanged 
UPDATE test SET col_timestamp = now();

Observe TIMESTAMP Default table creation statement of

SHOW CREATE TABLE test;

CREATE TABLE `test` (
`col_datetime` DATETIME DEFAULT NULL,
`col_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`col_2_timestamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=INNODB DEFAULT CHARSET=utf8

first TIMESTAMP Make up automatically DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
Indicates that updates and inserts are automatically written to the current time

the second TIMESTAMP Make up automatically DEFAULT '0000-00-00 00:00:00'
Indicates that only default values are assigned , There are no other ancillary functions

UPDATE test SET col_timestamp = '2019-11-29 11:12:06';

• Write the date directly to this field , It will not trigger automatic writing of the current time

Use two timestamp, Record data creation time and update time respectively

mysql 5.5 The version will report an error

ALTER TABLE test ADD col_default TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

> Error Code: 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Solution ：

## A column is used to record the creation time 
ALTER TABLE test MODIFY col_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
## Use trigger to record data update time 
CREATE TRIGGER `update_trigger` BEFORE UPDATE ON `test`FOR EACH ROW SET NEW.`col_2_timestamp` = NOW();
## The three data are initially recorded as 2019-11-29 15:03:27
INSERT INTO test VALUES(NOW(), NOW(), NOW());
## Change the first column , Observations 
UPDATE test SET col_datetime = '2000-11-11 11:11:11';
SELECT * FROM test;

mysql 5.6 Tables can be created directly after version , Don’t complain

ALTER TABLE test MODIFY col_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE test MODIFY col_2_timestamp TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

mysql 5.6 Newly added date and time

Basic attributes

• Occupied bytes
  All for 3 Bytes
• Value range
  date : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  time : HH:MM:SS, The millisecond time can be displayed by defining the width
• Time zone correlation
  Time zone ambiguity -> Switch database time zone , The stored value does not change

date and time Only 3 You can save date data in bytes , Be able to participate in mysql Date processing of
datetime 8 Bytes
date 3 Bytes
time 3 Bytes
timestamp 4 Bytes The value range is small

If only birthday information is stored , have access to date, He can record 1970 Time before , And the volume ratio timestamp smaller