MYSQL时间相关查询
1.函数
函数 | 用法 |
---|---|
DATE_ADD(date,INTERVAL expr unit) | 向日期添加指定的时间间隔,参数①合法的日期表达式,参数②expr为时间间隔,unit为时间单位 |
ADDDATE(date,INTERVAL expr unit) | 同上 |
DATE_SUB(date,INTERVAL expr unit) | 向日期减去指定的时间间隔,参数①合法的日期表达式,参数②expr为时间间隔,unit为时间单位 |
SUBDATE(date,INTERVAL expr unit) | 同上 |
PERIOD_ADD(P,N) | 增加N个月到阶段P,以格式YYYYMM返回值。参数P不是日期值,格式为YYMM或YYYYMM |
PERIOD_DIFF(P1,P2) | 返回在时期P1和P2之间月数,以格式YYYYMM返回值。参数P1、P2格式为YYMM或YYYYMM |
NOW() | 获得当前日期+时间(date + time) |
SYSDATE() | 获得当前日期+时间(date + time) |
CURRENT_TIMESTAMP() | 获取当前日期+时间(date + time) |
UNIX_TIMESTAMP() | 获取当前时间的秒数 |
CURDATE() | 获得当前日期date,与CURRENT_DATE(),CURRENT_DATE同义 |
CURTIME() | 获取当前时间time,与CURRENT_TIME(), CURRENT_TIME同义 |
UTC_TIMESTAMP() | 获取 UTC 日期+时间(date + time),与UTC_TIMESTAMP同义 |
UTC_DATE() | 获取 UTC 日期,与 UTC_DATE同义 |
UTC_TIME() | 获取UTC时间(我国位于东八区,本地时间=UTC时间+8小时),与UTC_TIME同义 |
MONTHNAME(date) | 获取日期的月份(英文)名字 |
DAYNAME(date) | 获取日期的星期(英文)名字 |
WEEKDAY(date) | 获取日期的星期索引(0=星期一,1=星期二, ……6= 星期天) |
DAYOFWEEK(date) | 获取日期的星期索引(1=星期天,2=星期一, ……7=星期六) |
DAYOFMONTH(date) | 获取日期在月份中的日期,范围从1到31 |
DAYOFYEAR(date) | 获取日期在一年中的日数,范围从1到366 |
WEEKOFYEAR(date) | 获取日期的周数, 范围从1到52 |
YEARWEEK(date[,mode]) | 获取日期的年份和周数,参数②是0或没有代表星期从星期天开始,参数②是1代表星期从星期一开始 |
LAST_DAY(date) | 获取日期在这个月的最后一天,格式YYYY-MM-DD |
DATEDIFF(expr1,expr2) | 获取expr1-expr2的日期差 |
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 获取datetime_expr2-datetime_expr1的时间单位差 |
EXTRACT(unit FROM date) | 获取日期的时间单位部分数值 |
DATE_FORMAT(date,format) | 将日期格式化,date 参数是合法的日期。format 规定日期/时间的输出格式 |
STR_TO_DATE(str,format) | 将字符串转换成格式化日期类型,format为保留部分日期格式表达式,需与字符串匹配 |
CONVERT(expr,type) | 将字符串转换成type类型,type=date只保留日期部分,type=time只保留时间部分,type=datetime保留日期+时间部分 |
时间单位部分函数 | 见下 |
2.时间单位
TYPE值 | 解释 |
---|---|
MICROSECOND | 微秒,MICROSECOND(date)函数用于返回指定时间或日期时间值的微秒部分。范围从0到999999 |
SECOND | 秒,SECOND(date)函数用于返回指定时间或日期时间值的秒值,范围从0到59 |
MINUTE | 分,MINUTE(date)函数用于返回指定时间或日期时间值的分值,范围从0到59 |
HOUR | 时,HOUR(date)函数用于返回指定时间或日期时间值的小时值,范围从0到23 |
DAY | 日期,DAY(date)函数用于返回指定时间或日期时间值的日期部分,范围从1到31,与DAYOFMONTH(date)函数同义 |
WEEK | 星期,WEEK(date[,mode])函数用于返回指定日期的周数,范围从1到52,参数②是0或没有代表星期从星期天开始,参数②是1代表星期从星期一开始 |
MONTH | 月,MONTH()获取当前日期的月份,范围从1到12 |
QUARTER | 季度,QUARTER()获取日期在一年中的季度,范围从1到4 |
YEAR | 年,YEAR()获取日期的年份,范围从1000到9999 |
SECOND_MICROSECOND | 秒和微秒 |
MINUTE_MICROSECOND | 分钟,秒和微秒 |
MINUTE_SECOND | 分钟和秒 |
HOUR_MICROSECOND | 小时,分钟,秒和微秒 |
HOUR_SECOND | 小时, 分钟和秒 |
HOUR_MINUTE | 小时和分钟 |
DAY_MICROSECOND | 小时,分钟,秒和微秒 |
DAY_SECOND | 小时, 分钟, 秒 |
DAY_MINUTE | 小时, 分钟 |
DAY_HOUR | 小时 |
YEAR_MONTH | 年和月 |
3.时间格式
格式 | 描述 |
---|---|
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微秒 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
4.示例
- 获取当前月份的天数
SELECT DAY(LAST_DAY(now()));
- 获取当天所有数据
SELECT * from T WHERE DATEDIFF(created_time, NOW()) = 0
或SELECT * from T WHERE curdate() = DATE(created_time)
或SELECT * from T WHERE TO_DAYS(NOW()) = DATE(created_time)
- 获取昨天所有数据
SELECT * from T WHERE DATEDIFF(created_time, NOW()) = -1
- 获取本季度所有数据
SELECT * from T WHERE QUARTER(NOW()) = QUARTER(created_time)
- 获取上季度所有数据
SELECT * from T WHERE QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) = QUARTER(created_time)
- 当前日期格式化
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')