MySQL最全时间日期操作

最新推荐文章于 2024-06-16 21:53:40 发布

置顶烁烁๑

最新推荐文章于 2024-06-16 21:53:40 发布

阅读量1.7k

点赞数 1

分类专栏：综合文章标签： mysql 数据库

本文链接：https://blog.csdn.net/flaystar/article/details/106903687

版权

综合专栏收录该内容

2 篇文章 0 订阅

订阅专栏

说道MySQL查询，会发现有很多需求都需要n天前或者某一段时间范围内的数据，以下是日常慢慢累积下来常用的SQL讲解，如有补充请留言，感谢

觉得有用可以收藏保存哦

了解函数 (直接看使用)

NOW()
返回当前的日期和时间时间格式Y-m-d H:i:s
CURDATE()
返回当前的日期格式为Y-m-d
CURTIME()
返回当前的时间格式为H:i:s
YEAR()
获取传入时间的年
MONTH()
获取传入时间的月
DAY()
获取传入时间的日
HOUR()
获取传入时间的时
MINUTE()
获取传入时间的分
SECOND()
获取传入时间的秒
QUARTER()
传入时间是本年度的第几个季度（3个月为一个季度）
TIME()
获取传入时间的时分秒
DATE()
用于格式化一个时间为YYYY-MM-DD的格式
TO_DAYS()
返回从0开始到传入日期的天数
WEEK()
获取是本年的第几周
YEARWEEK()
获取是本年和是本年第几周
LAST_DAY()
返回月份中的最后一天
DATE_ADD()
给日期添加指定的时间间隔
为负则减少日期，为正增加时间
时间间隔可选填
YEAR（年）、MONTH（月）、DAY（日）
MOHOUR（时）、MINUTE（分）、SECOND（秒）
WEEK （周）、QUARTER（季）
DATE_SUB()
从日期减去指定的时间间隔
为正则减少日期，为负增加时间
时间间隔可选填
YEAR（年）、MONTH（月）、DAY（日）
MOHOUR（时）、MINUTE（分）、SECOND（秒）
WEEK （周）、QUARTER（季）
DATEDIFF()
返回两个日期之间的天数
前面大后面小（正数），前面小后面大（负数）
DATE_FORMAT()
格式化传入的时间为指定格式（多用于格式化某个字段的时间）
UNIX_TIMESTAMP()
传入的时间转为时间戳格式
FROM_UNIXTIME()
传入时间戳和格式将时间戳转化为字符串时间

一些格式化参数说明

在这里插入图片描述

使用

为了方便理解，假设现在有一张表（table），其中的时间字段为（time）时间格式为
Y-m-d H:i:s

注意
本文使用的table和time两个命名仅为了简化才使用，请勿用到真正的项目中，不然就会很刺激滑稽.jpg
说明
本文中数据库针对的time字段的数据类型为字符串类型，
如果你存的类型是时间戳可以使用FROM_UNIXTIME(time, '%Y-%m-%d %H:%i:%s')函数将时间戳转化为字符串类型在食用
或者使用UNIX_TIMESTAMP("2020-07-21 10:30:11")函数将日期转为时间戳在食用

基本有了全部应用场景的例子，需要精确到年、月、日或着时、分、秒等需要自己去上面看一下相对应的函数呦

本日

SELECT * FROM `table` WHERE DATE(time) = DATE(NOW());
SELECT * FROM `table` WHERE TO_DAYS(time) = TO_DAYS(NOW());

昨日

SELECT * FROM `table` WHERE TO_DAYS( NOW( ) ) - TO_DAYS(time) <= 1
#如果time的格式已经是Y-m-d格式的，前面的time就不需要在加DATE方法辣
SELECT * FROM `table` WHERE DATE(time) =  DATE( DATE_SUB(NOW( ),INTERVAL 1 DAY))

最近30天

SELECT * FROM `table` where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(time)

本周

SELECT * FROM `table` WHERE YEARWEEK(time) = YEARWEEK(NOW());

上周

SELECT * FROM `table` WHERE YEARWEEK(time) = YEARWEEK(NOW())-1;

本月

SELECT * FROM `table` WHERE DATE_FORMAT( time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
SELECT * FROM `table`  WHERE DATE_FORMAT( time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')

上月

SELECT * FROM `table` WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) ， DATE_FORMAT( time, '%Y%m' ) ) =1
SELECT * FROM `table` WHERE DATE(time) = DATE(DATE_SUB(NOW(),INTERVAL 1 MONTH))

本季度

SELECT * FROM `table` WHERE QUARTER(time)=QUARTER(NOW());

上季度

SELECT * FROM `table` WHERE  QUARTER(time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));

6个月前

SELECT * FROM `table` WHERE time BETWEEN DATE_SUB(now(),INTERVAL 6 MONTH) AND NOW();

本年

SELECT * FROM `table` WHERE YEAR(time)=YEAR(NOW());

上一年

SELECT * FROM `table` WHERE  YEAR(time)=YEAR(date_sub(now(),INTERVAL 1 YEAR));

某时间段

#每日7点到13点
SELECT * FROM table WHERE MOHOUR(time) BETWEEN 7 AND 13 
# 2020-07-01 到 2020-07-20
SELECT * FROM table WHERE DATE(time) BETWEEN "2020-07-01" AND "2020-07-30"

指定某时间

#某年某月
SELECT * FROM `table` WHERE DATE_FORMAT(time,'%Y-%m')='2020-7'
SELECT * FROM `table` WHERE DATE_FORMAT(time,'%Y-%m')=DATE_FORMAT('2020-07-21','%Y-%m')
#所有的7月
SELECT * FROM `table` WHERE MONTH(time)) = 7