mysql日期sql语句大全_MySQL常用日期函数和SQL语句

select now();

select now(), sleep(3), now();

select SYSDATE(), sleep(3), SYSDATE();

select current_timestamp, current_timestamp();

select YEARWEEK(now());#201820

select date_format('2018-05-22 10:31:57', '%Y/%m/%d %H:%i:%s');#2018/05/22 10:31:57

#查询本周的数据

SELECT * FROM collie_activiti.act_ru_task art WHERE YEARWEEK(date_format(art.CREATE_TIME_,'%Y-%m-%d')) = YEARWEEK(now());

#查询上周的数据

SELECT * FROM collie_activiti.act_ru_task art WHERE YEARWEEK(date_format(art.CREATE_TIME_,'%Y-%m-%d')) = YEARWEEK(now())-1;

#查询当前月份的数据

select * from collie_activiti.act_ru_task art where date_format(art.CREATE_TIME_,'%Y-%m')=date_format(now(),'%Y-%m')

#查询距离当前现在6个月的数据

select * from collie_activiti.act_ru_task art where art.CREATE_TIME_ between date_sub(now(),interval 6 month) and now();

#查询上个月的数据

select * from collie_activiti.act_ru_task art where date_format(art.CREATE_TIME_,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

#查询本周的数据

select FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d') from collie.application app where WEEKOFYEAR(FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d')) = WEEKOFYEAR(now())

#查询当前月份的数据

select FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d') from collie.application app where MONTH(FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d')) = MONTH(now())

#查询当前年份的数据

select FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d') from collie.application app where YEAR(FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d')) = YEAR(now())

#查询任意时间区间内的数据

select FROM_UNIXTIME(app.create_time/1000,'%y-%m-%d') from collie.application app

where app.create_time between UNIX_TIMESTAMP('2017-5-20')*1000 and UNIX_TIMESTAMP('2018-5-22')*1000;

本文仅代表作者个人观点，不代表SEO研究协会网官方发声，对观点有疑义请先联系作者本人进行修改，若内容非法请联系平台管理员。更多相关资讯，请到SEO研究协会网www.seoxiehui.cn学习互联网营销技术请到巨推学院www.jutuiedu.com。