MySQL中常用到的关于时间的SQL
-- 今天
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AS dayStart;
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') AS dayEnd;
-- 昨天
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AS yesterdayStart;
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') AS yesterdayEnd;
-- 上周
SELECT DATE_FORMAT( DATE_SUB( DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d 00:00:00') AS lastWeekStart
SELECT DATE_FORMAT( SUBDATE(CURDATE(), WEEKDAY(CURDATE()) + 1), '%Y-%m-%d 23:59:59') AS lastWeekEnd;
-- 本周
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), '%Y-%m-%d 00:00:00') AS weekStart;
SELECT DATE_FORMAT(DATE_ADD(SUBDATE(CURDATE(), WEEKDAY(CURDATE())), INTERVAL 6 DAY), '%Y-%m-%d 23:59:59') AS weekEnd;
-- 上月
SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AS lastMonthStart;
SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') AS lastMonthEnd;
-- 本月
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AS monthStart;
SELECT DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') AS monthEnd;
--获取今天时间年月日
--SELECT curdate() as nowDate
-- 获取现在时间(年月日时分秒)
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') as nowTime
--获取本月有多少天
select DATEDIFF(date_add(curdate() - day(curdate()) + 1, interval 1 month),
DATE_ADD(curdate(), interval -day(curdate()) + 1 day)) as day
from dual;
获取某个月的每一天或当前月的每一天日期
此SQL参考自 https://blog.csdn.net/qq_38798251/article/details/89140471
SELECT date
FROM (select DATE_ADD(t1.dayed, INTERVAL t2.number DAY) date
from (select DATE_ADD(curdate(), interval -day(curdate()) + 1 day) dayed) t1,
(SELECT @xi := @xi + 1 as number
from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
(SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8) xc2,
(SELECT @xi := -1) xc0) t2
where DATE_ADD(t1.dayed, INTERVAL t2.number DAY)) time
WHERE SUBSTRING(date, 1, 7) = SUBSTRING(CURDATE(), 1, 7)
另有一些未用到的SQL 做个记录
1、查询当前时间3天前的时间点
select date_sub(now() ,interval 3 day)
2.查询当前时间的时间3天之后的时间点
select data_sub(now(),interval -3 day)
3.查询当前时间3个月之前的时间点
select data_sub(now(),interval 3 month)
4.查询当前时间3个月之后的时间点
select data_sub(now,interval -3 month)
最新文章
- Let it go.Let it be.Keep it up!
- Mac mySql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)的解决办法
- sdk 简单说明文档草稿。
- [ javascript canvas 插件演示 ] canvas 插件演示
- eclips android项目复制
- Android学习笔记之DocumentBuilder的使用....
- String及其他
- TWaver初学实战——如何在EasyUI中插入TWaver
- iOS9横竖屏设置的处理方法
- 发布GeoServer后预览提示下载wms文件
- 返回当前页面title、url等操作
- Eclipse使用之杂七杂八
- BZOJ 3039: 玉蟾宫
- CLAHE的实现和研究
- python基础 - 01
- [python学习笔记] py2exe 打包
- JavaScript递归函数解“汉诺塔”
- android_模拟器调试
- jmeter 获取登录token
- 并发编程之 CyclicBarrier 源码分析