Shulou(Shulou.com)06/01 Report--

1. Background

* MySQL supports a variety of date and time data types, such as YEAR, TIME, DATETIME, TIMESTAMP, etc.

* microseconds are supported in MySQL version 5.6.4 (supported types: TIMTE, DATETIME, TIMESTAMP, maximum microsecond length is 6)

two。 The space and representation range of the date and time type

Types

Prior to version 5.6.4

Occupied byte

After version 5.6.4

(including version 5.6.3)

Occupied byte

Indicates the range DATETIME86+ (microsecond length-1) / 21000-01-01 00:00:00 ~ 9999-12-31 23:59:59DATE33

1000-01-01 ~ 9999-12-31 TIMESTAMP44 + (microsecond length-1) / 21970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTCYEAR11

YEAR (2): 1970 ~ 2070

YEAR (4): 1901 ~ 2155

TIME

33 + (millisecond length-1) / 2-838 purl 59 purl 59 ~ 838 frev 59

3. Date and time type "0" value format

Type Zero ValueDATETIME'0000-00-0000: 00:00'DATE'0000-00-00'TIMESTAMP'0000-00-0000: 00VOUR 00YEAR0000TIMERAN00VOUR 00VOUR 00'

4. Date and time type related function operation

* NOW () gets the date and time when the statement starts to execute. [microseconds can be obtained after MySQL version 5.6.3]

Mysql > SELECT NOW (); +-+ | NOW () | +-+ | 0-06-27 09:00:13 | +-+ 1 row in set (0.00 sec) mysql > SELECT NOW (6) +-- + | NOW (6) | +-- + | 2017-06-27 09 18 sec 50.720849 | +-+ 1 sec)

* SYSDATE () gets the execution time of the SYSDATE () function

Mysql > SELECT SYSDATE (), SLEEP (2), SYSDATE () +-+ | SYSDATE () | SLEEP (2) | SYSDATE () | +- -+ | 2017-06-27 09:38:03 | 0 | 2017-06-27 09:38:05 | +-+ 1 row in set (2.01 sec)

* CURDATE () gets the current date

Mysql > SELECT CURDATE (); +-+ | CURDATE () | +-+ | 2017-06-27 | +-+ 1 row in set (0.00 sec)

* CURTIME () gets the current time

Mysql > SELECT CURTIME (); +-+ | CURTIME () | +-+ | 09:05:51 | +-+ 1 row in set (0.03 sec)

* ADDDATE () increase date

[add 10 days to the current date]

Mysql > SELECT CURDATE (), ADDDATE (CURDATE (), "10") +-+-+ | CURDATE () | ADDDATE (CURDATE ()) "10") | +-+-+ | 2017-06-27 | 2017-07-07 | +-+-- + 1 row in set (0.02 sec)

* ADDTIME () increases time

[add 1 day, 1 hour, 1 minute and 1 second to the current time]

Mysql > SELECT NOW (), ADDTIME (NOW (),'1-1-1-1-1-1') +-+-+ | NOW () | ADDTIME (NOW ()) ) | +-+-+ | 2017-06-27 09:00:21 | 2017-06-28 10:01:22 | +-+- -+ 1 row in set (0.02 sec)

* DATE () returns the date in the string

Mysql > SELECT NOW (), DATE (NOW ()) +-+ | NOW () | DATE (NOW ()) | +-+-+ | 2017-06-27 09:07:18 | 2017-06-27 | +- -+-+ 1 row in set (0.00 sec)

* TIME () returns the time in the string

Mysql > SELECT NOW (), TIME (NOW ()) +-+ | NOW () | TIME (NOW ()) | +-+-+ | 2017-06-27 09:07:40 | 09:07:40 | +- -+-+ 1 row in set (0.01 sec)

* YEAR () returns the number of years in the string

Mysql > SELECT NOW (), YEAR (NOW ()) +-+ | NOW () | YEAR (NOW ()) | +-+-+ | 2017-06-27 09:12:39 | 2017 | +- -+-+ 1 row in set (0.00 sec)

* MONTH () returns the number of months in the string

Mysql > SELECT NOW (), MONTH (NOW ()) +-+ | NOW () | MONTH (NOW ()) | +-+-+ | 2017-06-27 09:13:09 | 6 | +- -+-+ 1 row in set (0.01 sec)

* DAY () returns the number of days in the string

Mysql > SELECT NOW (), DAY (NOW ()) +-+ | NOW () | DAY (NOW ()) | +-+-+ | 2017-06-27 09:12:08 | 27 | +- -+-+ 1 row in set (0.01 sec)

* HOUR () returns the hours in the string

Mysql > SELECT NOW (), HOUR (NOW ()) +-+ | NOW () | HOUR (NOW ()) | +-+-+ | 2017-06-27 09:13:57 | 9 | +- -+-+ 1 row in set (0.00 sec)

* MINUTE () returns the number of minutes in string

Mysql > SELECT NOW (), MINUTE (NOW ()) +-+ | NOW () | MINUTE (NOW ()) | +-+-+ | 2017-06-27 09:15:38 | 15 | +- -+-+ 1 row in set (0.00 sec)

* SECOND () returns the number of seconds in the string

Mysql > SELECT NOW (), SECOND (NOW ()) +-+ | NOW () | SECOND (NOW ()) | +-+-+ | 2017-06-27 09:16:23 | 23 | +- -+-+ 1 row in set (0.02 sec)

* MICROSECOND () returns the number of microseconds in the string [after MySQL version 5.6.3]

Mysql > SELECT NOW (6), MICROSECOND (NOW (6)) +-- +-+ | NOW (6) | MICROSECOND (NOW (6)) | +-- -+ | 2017-06-27 0918Switzerland 11.264125 | 264125 | +-- +-+ 1 row in set (0.01sec)

* DATEDIFF () date subtraction operation

Mysql > SELECT DATEDIFF ('2017-06-29,' 2017-06-09') +-- + | DATEDIFF ('2017-06-29' '2017-06-09') | +-- + | 20 | +-+ 1 row in set (0.00 sec) mysql > SELECT DATEDIFF (' 2017-06-29') '2017-07-09') +-- + | DATEDIFF ('2017-06-29' '2017-07-09') | +-+ |-10 | +-+ 1 row in set (0.03 sec)

* DAYNAME () returns the English name of the working day

Mysql > SELECT CURDATE (), DAYNAME (CURDATE ()) +-+ | CURDATE () | DAYNAME (CURDATE ()) | +-+-+ | 2017-06-27 | Tuesday | +-+- -+ 1 row in set (0.01 sec)

* English name of the return month of MONTHNAME ()

Mysql > SELECT CURDATE (), MONTHNAME (CURDATE ()) +-+-+ | CURDATE () | MONTHNAME (CURDATE ()) | +-+-+ | 2017-06-27 | June | +-+- -+ 1 row in set (0.01 sec)

* PERIOD_DIFF () month subtraction [format is YYYYMM or YYMM]

Mysql > SELECT PERIOD_DIFF (201706, 201705) +-- + | PERIOD_DIFF (201706, 201705) | +-- + | 1 | +-+ 1 row in set (201706 sec) mysql > SELECT PERIOD_DIFF (201706) 201707) +-- + | PERIOD_DIFF (201706, 201707) | +-- + |-1 | +-- + 1 row in set (201707 sec)

. Wait, see the official website manual for details.

5. The difference between TIMESTAMP and DATETIME [TIMESTAMP VS DATETIME]

* timestamp takes up less space bytes than datetime.

* timestamp easily supports a smaller range than timedate. And it is easy to go beyond.

* timestamp is affected by time zone timezone, MYSQL version and server SQL MODE, while datetime is not affected by time zone.

6. Time effect experiment

* create table tweed

Mysql > CREATE TABLE tweed (- > t TIMESTAMP,-> d DATETIME->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.09 sec)

* View time zone

Mysql > show variables like'% zone%';+-+-+ | Variable_name | Value | +-+-+ | system_time_zone | CST | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.02 sec)

* insert data

Mysql > INSERT INTO tweed SELECT NOW (), NOW (); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

* View data

Mysql > SELECT * FROM tweed +-+ | t | d | +-+-+ | 2017-06-27 09:44: 13 | 0-06-27 09:44:13 | +-+-+ 1 row in set (sec)

* Update time zone

Mysql > set time_zone ='+ 0 rows affected (0 sec)

* View the data again

Mysql > SELECT * FROM tweed +-+ | t | d | +-+-+ | 2017-06-27 01:44: 13 | 0-06-27 09:44:13 | +-+-+ 1 row in set (2017 sec)

7. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.