mysql 查询当天、最近一天、最近一周,本月,上一个月的数据
今天
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 下月第一天
where date(regdate) = curdate();
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
Mysql数据库中获取系统时间,年,月,日单个获取
获取当前系统日期时间:select SYSDATE() AS 系统日期时间;
获取当前系统年月日:select current_date AS 年月日;
获取当前系统年份:select year(CURRENT_DATE) AS 年 ;
获取当前系统月份:select month(CURRENT_DATE) AS 月;
获取当前系统日:select day(CURRENT_DATE) AS 日;
获取当前系统时间:select time(SYSDATE()) AS 系统时间;
获取当前系统小时:select HOUR(SYSDATE()) AS 系统小时;
获取当前系统分钟:select MINUTE(SYSDATE()) AS 系统分钟;
获取当前系统秒:select SECOND(SYSDATE()) AS 系统秒;
获取当前系统毫秒:select MICROSECOND(SYSDATE()) AS 系统秒;
Mysql数据库中获取系统时间,年,月,日单个获取
Mysql数据库中获取系统时间,年,月,日
selectcurdate();--获取当前日期
1、利用TO_DAYS函数
select to_days(now()) - to_days('19930908')
2、利用DATEDIFF函数
select datediff(now(),'19930908')
参数1 - 参数2 等于间隔天数
selectlast_day(curdate());--获取当月最后一天。
selectDATE_ADD(curdate(),interval-day(curdate())+1day);--获取本月第一天
selectdate_add(curdate()-day(curdate())+1,interval1month);-- 获取下个月的第一天
selectDATEDIFF(date_add(curdate()-day(curdate())+1,interval1month),DATE_ADD(curdate(),interval-day(curdate())+1day))fromdual;--获取当前月的天数
select curtime() ---获取当前系统 time
select sysdate()、current_timestamp()、now()、localtime()、localtimestamp() ----获取当前系统 日期+时间
Mysql数据库中计算两个日期之间的时间差
1、利用TO_DAYS函数
select to_days(now()) - to_days('19930908')
2、利用DATEDIFF函数
select datediff(now(),'19930908')
参数1 - 参数2 等于间隔天数
3、利用TIMESTAMPDIFF函数
计算两日期时间之间相差的天数,秒数,分钟数,周数,小时数,这里主要分享的是通过MySql内置的函数 TimeStampDiff() 实现。
函数 TimeStampDiff() 是MySQL本身提供的可以计算两个时间间隔的函数,语法为:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其中unit单位有如下几种,分别是:FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 。该参数具体释义如下:
FRAC_SECOND 表示间隔是毫秒SECOND 秒MINUTE 分钟HOUR 小时DAY 天WEEK 星期MONTH 月QUARTER 季度YEAR 年
例如:
#计算两日期之间相差多少周
select timestampdiff(week,'2011-09-30','2015-05-04');
#计算两日期之间相差多少天
select timestampdiff(day,'2011-09-30','2015-05-04');
Mysql数据库中计算两个日期之间的时间差
TIMESTAMPDIFF,(如果当期时间和之前时间的分钟数相比较。大于1天,即等于1;小于1天,则等于0)
select TIMESTAMPDIFF(DAY,'2016-11-16 10:13:42',NOW());
DATEDIFF,(只按2016-11-16计算,不会加小时分钟数,按天计算)
select DATEDIFF(NOW(),'2016-11-16 17:10:52');
mysql分别获取已有数据date中的年月日(单个提取)
select YEAR(DATE) from test; //获取年
select day(DATE) from test; //获取日
select month(DATE) from test; //获取月
select DATE(CURDATE()) //获取日期
select CONCAT(YEAR(CURDATE()),'-','01') //获取本年第一月
mysql分别获取已有数据date中的年月日(单个提取)
select left(signDate,7) as date from tablename
select date_format(日期字段,'%Y-%m') as '日期' from 表