MySQL 时间日期类型和相关函数

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()：

 
 

 

二、时间日期函数 

 

MySQL中的日期时间函数

函数	功能	格式
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字符串中的格式符：

MySQL中的日期和时间格式

格式符	格式说明
%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时间隔类型，其值如下表

MySQL日期间隔类型

表达式类型	描述	格式
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列的值为当前时间（除非指定了该列的值）