mysql查询本年的函数_mysql实现查询当天, 昨天, 上周 ,上月 本季度, 本年,(日期函数)...

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4.

返回日期的一年中的季度，范围为1到4。

ADDDATE(d,n)(date)

计算起始日期 d 加上 n 天的日期

SELECT ADDDATE(“2017-06-15”, INTERVAL 10 DAY);

->2017-06-25

DATEDIFF(d1,d2)

计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’)

-> -32

DATE_SUB(date,INTERVAL expr type)

函数从日期减去指定的时间间隔。

Orders 表中 OrderDate 字段减去 2 天：

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate

FROM Orders

DAY(d)

返回日期值 d 的日期部分

SELECT DAY(“2017-06-15”);

-> 15

DAYNAME(d)

返回日期 d 是星期几，如 Monday,Tuesday

SELECT DAYNAME(‘2011-11-11 11:11:11’)

->Friday

DAYOFMONTH(d)

计算日期 d 是本月的第几天

SELECT DAYOFMONTH(‘2011-11-11 11:11:11’)

->11

DAYOFWEEK(d)

日期 d 今天是星期几，1 星期日，2 星期一，以此类推

SELECT DAYOFWEEK(‘2011-11-11 11:11:11’)

->6

DAYOFYEAR(d)

计算日期 d 是本年的第几天

SELECT DAYOFYEAR(‘2011-11-11 11:11:11’)

->315

今天

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(时间字段名)

查询本周

SELECT * FROM mrt_product_out ou WHERE YEARWEEK(date_format(ou.out_Date,'%Y-%m-%d')) = YEARWEEK(now());

近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

同时，再附上 一个 mysql官方的相关document

#查询本季度数据

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