MySQL Time field Introduction

There are about 5 types of MySQL time, such as

1. Create a database

create table t1 (    id  int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,    d1_data date,    d2_time time,    d3_datatime datetime,    d4_year year,    d5_timestamp TIMESTAMP);

Insert by String method

INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(NOW(),NOW(),NOW(),NOW(),NOW());mysql> select * from t1 where id=1\G*************************** 1. row ***************************          id: 1     d1_data: 2018-06-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(‘2018-06-15‘,‘13:50:25‘,‘2018-06-15 13:50:25‘,‘2018‘,‘2018-06-15 13:50:25‘);mysql> select * from t1 where id=2\G*************************** 1. row ***************************          id: 2     d1_data: 2018-06-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)

1.1. Date (number Insert)

mysql> help DATEA date. The supported range is ‘1000-01-01‘ to ‘9999-12-31‘. MySQLdisplays DATE values in ‘YYYY-MM-DD‘ format, but permits assignment ofvalues to DATE columns using either strings or numbers.插入时候可以是字符串或者数字20180615‘2018-06-15‘

1.2. Time (Digital Insert)

mysql> help TIMEA time. The range is ‘-838:59:59.000000‘ to ‘838:59:59.000000‘. MySQLdisplays TIME values in ‘HH:MM:SS[.fraction]‘ format, but permitsassignment of values to TIME columns using either strings or numbers.An optional fsp value in the range from 0 to 6 may be given to specifyfractional seconds precision. A value of 0 signifies that there is nofractional part. If omitted, the default precision is 0.mysql> INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(20180515,135025,‘2018-06-15 13:50:25‘,‘2018‘,‘2018-06-15 13:50:25‘);Query OK, 1 row affected (0.00 sec)mysql> select * from t1 where id=4\G*************************** 1. row ***************************          id: 4     d1_data: 2018-05-15     d2_time: 13:50:25 d3_datatime: 2018-06-15 13:50:25     d4_year: 2018d5_timestamp: 2018-06-15 13:50:251 row in set (0.00 sec)

1.3. DATETIME (numeric insertion)

mysql> Help Datetimea date and time combination. The supported range is ' 1000-01-0100:00:00.000000 ' to ' 9999-12-31 23:59:59.999999 '. MySQL displaysdatetime values in ' yyyy-mm-dd hh:mm:ss[.fraction] ' format, but permitsassignment of values to DATETIME Colu MNS using either strings ornumbers. An optional FSP value in the range from 0 to 6 may be given to specifyfractional seconds precision. A value of 0 signifies that there was nofractional part. If omitted, the default precision is 0.Automatic initialization and updating to the current date and time Fordatetime Colu MNS can specified using DEFAULT and on UPDATE columndefinition clauses, as described inhttp://dev.mysql.com/doc/refman/ 5.7/en/timestamp-initialization.html.20180615135025 ' 2018-06-15 13:50:25 ' stores a string mysql> INSERT into T1 (d1_data,d2 _time,d3_datatime,d4_year,d5_timestamp) VALUES (20180515,135025,20180615135025, ' 2018 ', ' 2018-06-15 13:50:25 '); Query OK, 1 row affected (0.01 sec) mysql> select * from t1 where id=5\g*********1. Row *************************** id:5 d1_data:2018-05-15 d2_time:13:50:25 d3_datatime:2018-06-15 13:50 : d4_year:2018d5_timestamp:2018-06-15 13:50:251 row in Set (0.00 sec)

1.4. Year

mysql> help yearReturns the year for date, in the range 1000 to 9999, or 0 for the"zero" date.

1.5, timestamp (digital insertion)

mysql> help timestampA timestamp. The range is ‘1970-01-01 00:00:01.000000‘ UTC to‘2038-01-19 03:14:07.999999‘ UTC. TIMESTAMP values are stored as thenumber of seconds since the epoch (‘1970-01-01 00:00:00‘ UTC). ATIMESTAMP cannot represent the value ‘1970-01-01 00:00:00‘ because thatis equivalent to 0 seconds from the epoch and the value 0 is reservedfor representing ‘0000-00-00 00:00:00‘, the "zero" TIMESTAMP value.存储的是时间戳mysql> INSERT  INTO t1 (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES(20180515,135025,20180615135025,‘2018‘,20180615135025);Query OK, 1 row affected (0.01 sec)

Summarize

When inserting or updating the above description, the value can be ' 2018-06-06 ' time type string or 20180606 number, which will be processed by MySQL function to deposit

100w Data Compare datatime and timestamp efficiency

2. Prepare 100w Data

drop PROCEDURE  autoinsert;DELIMITER $$create PROCEDURE autoinsert()  BEGIN    DECLARE i int DEFAULT 1;    DECLARE mytime varchar(19) ;    WHILE (i<1000000) DO      set mytime=date_add(now(),interval i second);      INSERT into t1  (d1_data,d2_time,d3_datatime,d4_year,d5_timestamp) VALUES (mytime,mytime,mytime,date_add(now(),interval i second),mytime);      set i=i+1;    END WHILE ;  END $$DELIMITER ;CALL autoinsert();

2.1. DateTime Analysis: No index case:

Probably need 0.22s to add an index

mysql> alter table t1 add key (d3_datatime);

2.2. Timestamp analysis

No index is in the case:

When you need to add an index of around 0.4s:

ALTER TABLE `t1` add key (d5_timestamp);

100w data theory, DateTime is faster than timestamp, the underlying comparison should be numbers, timestamp may be more expensive in time conversion, there is no difference in the index case, the spatial consideration of the DateTime requires 8 bytes, and timestamp as long as 4 bytes.

MySQL Time field Introduction