MySQL日期和时间

各种日期数据类型及其所占空间

类型	所占空间
DateTIME	8字节
Date	3字节
TIMESTAMP	4字节
YEAR	1字节
TIME	3字节

DATETIME &DATE
DATETIME占用8字节，日期范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
DATE占用三个字节,日期范围是'1000-01-01'到'9999-12-31'。
MySQL数据库中,对日期和时间的输入格式是比较宽松的，一下输入均可以:

• 2011-01-01 00:01:10
• 2011/01/01 00+01+10
• 11/01/01 00@01@10

TIMESTAMP
TIMESTAMP和DATETIME显示的结果是一样的，都是固定的"YYYY-MM-DD HH:MM:SS"的形式，不过TIMESTAMP只占了四个字节，显示范围是"1970-01-01 00:00:00"到"2038-01-19 03:14:07"。
TIMESTAMP和DATETIME除了显示时间不同外，还有以下不同:

• 在建表时，列为TIMESTAMP的日期类型可以设置默认值，而DATETIME不行；
• 在更新表时，可以设置TIMESTAMP类型的列自动更新为当前时间。

create table c(
id INT primary key,
time TIMESTAMP
)ENGINE=InnoDB;

mysql_timestamp.png
从上图我们可以看出，TIMESTAMP类型的行在表创建后会被MySQL设置自动更新为当前时间。我在使用TIMESTAMP类型时吃过亏：因为该行的存在，主从数据检校一直出错，但排查了好半天的错误最后才定位出来。
那如果要取消TIMESTAMP的这种默认自动更新,同时又需要TIMESTAMP默认值为当前值，可以使用:

ALTER TABLE c 
CHANGE time time  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

YEAR和TIME
YEAR类型占用1个字节，并且在5.5.27版本以前定义时就可以指定显示的宽度YEAR(4)或者YEAR(2)。5.5.27版本以后只能使用YEAR(4)。YEAR的范围是1970~2070；
TIME类型占用3个字节，显示范围是"-838:59:59"~"839:59:59"。为什么TIME类型的时间可以大于23。那是因为TIME类型不仅可以用来保存一天中的时间，还可以用来保存时间间隔。不过我们也不难看出，保存的时间间隔不是很长，840/24=3天半。TIME类型用得很少。

日期相关函数
NOW()、CURRENT_TIMESTAMP和SYSDATE

mysql_now_cur_time_sysdate.png
从上面图片我们可以看出三个函数的一些区别:

• CURRENT_TIMESTAMP和NOW()是一样的；
• SYSDATE()函数返回的是执行当前函数的时间，NOW返回的是执行SQL语句的时间。

时间加减时间:DATE_ADD、DATE_SUB

mysql> select DATE_ADD('2016-09-28 01:00:00',INTERVAL 1 YEAR) as a,
    -> DATE_ADD('2016-09-28 01:00:00',INTERVAL "20:30" HOUR_MINUTE) as b;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2017-09-28 01:00:00 | 2016-09-28 21:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

除了YEAR、HOUR_MUNITE数据类型外，还有下面的数据类型:

unit Value	Expected expr Format
MICROSECONS	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

DATE_FORMAT函数
DATE_FORMAT函数没什么好说的，就是按照需求打印日期格式:

mysql> select date_format(now(),'%Y%m%d') as date;
+----------+
| date     |
+----------+
| 20160928 |
+----------+

不过如果滥用这个函数会造成很严重的后果，一般来说表中的时间列都是使用索引的，但是下面的语句，MySQL是用不了索引的，会导致查询效率非常低下:
select * from table where DATE_FORMAT(date,'%Y-%m-%d')='xxxx-xx-xx'