MySQL 时间日期类型和相关函数
MySQL 时间日期类型和相关函数
一、时间日期类型
| 类型 | 字节 | 最小值 | 最大值 | 零值 |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 0000-00-00 00:00:00 |
| DATE | 4 | 1000-01-01 | 9999-12-31 | 0000-00-00 |
| TIMESTAMP | 4 | 19700101080001 | 2038年某时刻 | 00000000000000 |
| TIME | 3 | -838:59:59 | 838:59:59 | 00:00:00 |
| YEAR | 1 | 1901 | 2155 | 0000 |
TIMESTAMP返回的格式为"YYYY-MM-DD HH:MM:SS"，宽度固定为19个字符，要想获得数字值，可以 select current_timestamp+0 的方式：
YEAR有2位(5.5.27之前的版本有)或4位格式的年，默认是4位格式，在4位格式中，允许的值1901 ~ 2155和0000，在2位格式中，允许的值是70 ~ 69，表示1970 ~ 2069，YEAR返回的格式为“YYYY”。
对于以上所有时间和日期类型，若想插入当前时间对应的值，可以用current_timestamp、now()：
二、时间日期函数
| 函数 | 功能 | 格式 |
| CURDATE() | 返回当前日期 | 2016-10-29 |
| CURTIME() | 返回当前时间 | 11:20:34 |
| NOW() | 返回当前的日期和时间 | 2016-10-29 11:21:02 |
|
UNIX_TIMESTAMP(date) UNIX_TIMESTAMP() |
返回日期date的UNIX时间戳 |
执行：select unix_timestamp() 输出：1477711822 |
| FROM_UNIXTIME(unix_timestamp) | 返回UNIX时间戳的日期值 |
执行：select from_unixtime(1477670400) 输出：2016-10-29 11:30:22 |
| WEEK(date) | 返回日期date为一年中的第几周 |
执行：select week(curdate()) 输出：43 |
| YEAR(date) | 返回日期date的年份 |
执行： select year(curdate()) 输出：2016 |
| HOUR(time) | 返回time的小时值 |
执行：select hour(curtime()); 输出：11 |
| MINUTE(time) | 返回time的分钟值 |
执行：select minute(curtime()) 输出：36 |
| MONTHNAME(date) | 返回date的月份名 |
执行：select monthname(curdate()) 输出October |
| DATE_FORMAT(date, format) | 返回按字符串format格式化日期date值 |
执行：select date_format(now(), '%Y年%c月%e日 %T时%i分%S秒'); 输出：2016年10月29日 11:44:57时44分57秒 |
| DATE_ADD(date,INTERVAL expr type) | 返回一个日期值加上一个时间间隔的时间值 |
执行：select now() current,date_add(now(),INTERVAL 1 day) after_one_day; 输出：2016-10-29 11:52:10 | 2016-10-30 11:52:10 |
| DATEDIFF(expr1,expr2) | 返回起始时间expr1和结束时间expr2之间的天数 |
执行：select datediff('2016-10-20',now()); 输出：-9 执行：select datediff(now(),'2016-10-20') 输出：9 |
UNIX_TIMESTAMP(date)和 FROM_UNIXTIME(unix_timestamp)是互逆作用的.
DATE_FORMAT(date,format)函数：
format字符串中的格式符：
| 格式符 | 格式说明 |
| %S和%s | 两位数字形式的秒(00, 01, ... , 59) |
| %i | 两位数字的分(00, 01, ... , 59) |
| %H | 两位数字形式的小时，24小时(00,01,...,23) |
|
%h和%I |
两位数字形式的小时，24小时(01,02,...,12) |
| %k | 数字形式的小时，24小时(0,1,2,...,23) |
| %l | 数字形式的小时，12小时(1,2,...,12) |
| %T | 24小时的时间形式(hh:mm:ss) |
| %r | 12小时的时间形式(hh:mm:ssAM或hh:mm:ssPM) |
| %p | AM或PM |
| %W | 一周中每一天的名称(Sunday,Monday,...,Saturday) |
| %a | 一周中每一天的名称缩写(Sun,Mon,...,Sat) |
| %d | 两位数字表示月中的天数(00,01,...,31) |
| %e | 数字表示月中天数(1,2,...,31) |
| %D | 英文后缀表示月中的天数(1st,2nd,3rd,...) |
| %w | 数字形式表示周中的天数(0=Sunday,1=Monday...) |
| %j | 以3位数字表示年中天数(001,002,...,366) |
| %U | 年的周(0,1...,52)，其中Sunday位周第一天 |
| %u | 年的周(0,1...,52)，其中Monday位周第一天 |
| %M | 月名(January,February,...,December) |
| %b | 缩写的月名(Jan,Feb,...Dec) |
| %m | 两位数字表示的月份(01,02,...,12) |
| %c | 数字表示的月份(1,2,...,12) |
| %Y | 4位数字表示的年份 |
| %y | 两位数字表示的年份 |
| %% | 转义 |
实例：
DATE_ADD(date,INTERVAL expr type)：其中INTERVAL时间隔类型关键字，expr是一个表达式，对应后面的类型，type时间隔类型，其值如下表
| 表达式类型 | 描述 | 格式 |
| HOUR | 小时 | hh |
| MINUTE | 分 | mm |
| SECOND | 秒 | ss |
| YEAR | 年 | YY |
|
MONTH |
月 | MM |
| DAY | 日 | DD |
| YEAR_MONTH | 年和月 | YY-MM |
| DAY_HOUR | 日和小时 | DD hh |
| DAY_MINUTE | 日和分钟 | DD hh:mm |
| DAY_SECOND | 日和秒 | DD hh:mm:ss |
| HOUR_MINUTE | 小时和分 | hh:mm |
| HOUR_SECOND | 小时和秒 | hh:ss |
| MINUTE_SECOND | 分钟和秒 | mm:ss |
例如，得到3天5小时后的时间：
得到3天5小时前的时间：
关于datetime和timestamp
MySQL中datetime能保存的日期范围从1001年到9999年，精度为秒，存储时把日期和时间封装为YYYYMMDDHHMMSS的格式的整数中，与时区无关，使用8个字节存储。
timestamp类型能保存的日期范围从1970年1月1日到2038年12月31日，它存储的是时间戳，只用了4个字节，所以范围比datetime小很多，timestamp是和时区相关的。默认情况下，如果插入时没有指定第一个timestamp列的值，那么mysql会默认给设置当前时间。在更新一行记录时，也会更新第一个timestamp列的值为当前时间（除非指定了该列的值）
