各种日期数据类型及其所占空间
| 类型 | 所占空间 |
|---|---|
| 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;
从上图我们可以看出，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
从上面图片我们可以看出三个函数的一些区别:
- 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'
