MYSQL常用日期操作
mysql查询今天、昨天、7天、近30天、本月、上一月 数据 今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select *
from user
where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
select *
from [ user ]
where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())
and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
select *
from [ user ]
where pudate between 上月最后一天
and 下月第一天
select * from user where pudate between unix_timestamp(date_sub(curdate(),interval (day(now())+1) day)) and unix_timestamp(date_sub(date_sub(curdate(),interval -1 month ),interval (day(now())-2) day));
select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())
SELECT date( c_instime ) ,curdate( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30
最新文章
- JSP页面编码问题
- HDU 3333 Turing Tree --树状数组+离线处理
- LeetCode:Word Break(DP)
- web的三种监听器
- [COCOS2DX]官网helloworld在VS2012中的部署
- VS2008压力测试时web测试记录器无显示
- poj 3321
- 求n阶勒让德多项式
- 《STL源代码分析》---stl_list.h读书笔记
- android常用工具类
- Java中Date对象与String互转
- SpringMVC源码之参数解析绑定原理
- Bootstarp-table入门
- iOS学习——图片压缩到指定大小以内
- python学习之闭包
- [linux]解析crontab
- DirectoryEntry配置IIS7出现ADSI Error:未知错误(0x80005000)
- c#Loading 页SplashScreenManager的使用
- shell jq
- chapter02 回归模型在&#39;&#39;美国波士顿房价预测&#39;&#39;问题中实践
热门文章
- oscache源码浅析
- spring mvc处理http请求报错:java.lang.IllegalStateException: getInputStream() has already been called for this request
- 1131 Subway Map
- 关于 Mybatis的原生连接池 和 DBCP 连接池
- 使用原生js自定义内置标签
- Django界面不能添加中文解决办法
- 循序渐进Python3(十三) --1-- django之form表单
- Delphi 解压缩 ZipForge
- 前端工作准备-foxmail登陆失败汇总
- 斯坦福CS229机器学习课程笔记 part3:广义线性模型 Greneralized Linear Models (GLMs)