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

时间:2024-05-23 16:51:06

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 的方式:

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

 

YEAR有2位(5.5.27之前的版本有)或4位格式的年,默认是4位格式,在4位格式中,允许的值1901 ~ 2155和0000,在2位格式中,允许的值是70 ~ 69,表示1970 ~ 2069,YEAR返回的格式为“YYYY”。

 

对于以上所有时间和日期类型,若想插入当前时间对应的值,可以用current_timestamp、now():

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

 

二、时间日期函数 

 

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 两位数字表示的年份
%% 转义

 

实例:

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

 

 

 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小时后的时间:

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

得到3天5小时前的时间:
MySQL 时间日期类型和相关函数
 

 

关于datetime和timestamp

 

MySQL中datetime能保存的日期范围从1001年到9999年,精度为秒,存储时把日期和时间封装为YYYYMMDDHHMMSS的格式的整数中,与时区无关,使用8个字节存储。

 

timestamp类型能保存的日期范围从1970年1月1日到2038年12月31日,它存储的是时间戳,只用了4个字节,所以范围比datetime小很多,timestamp是和时区相关的。默认情况下,如果插入时没有指定第一个timestamp列的值,那么mysql会默认给设置当前时间。在更新一行记录时,也会更新第一个timestamp列的值为当前时间(除非指定了该列的值)