11
2013-10

MySQL查询本周、上周、本月、上个月份的数据【转摘】

  MySQL查询的方式很多,下面为您介绍的MySQL查询实现的是查询本周、上周、本月、上个月份的数据,如果您对MySQL查询方面感兴趣的话,不妨一看。
  
  查询当前这周的数据
  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 下月第一天
  
///////////////////////////////////////////////////////////////////////
#查询距离当前现在6个月的数据
#select NewsCreateDate from web_news where NewsCreateDate between date_sub(now(),interval 6 month) and now()

#查询当前这周的数据
#select NewsCreateDate from web_news where YEARWEEK(date_format(NewsCreateDate,'%Y-%m-%d')) = YEARWEEK(now())

#查询上周的数据
#select NewsCreateDate from web_news where YEARWEEK(date_format(NewsCreateDate,'%Y-%m-%d')) = YEARWEEK(now())-1

#查询当天的数据
#select NewsCreateDate from web_news where date(date_format(NewsCreateDate,'%Y-%m-%d'))=date(now())

#查询当前月份的数据
#select NewsCreateDate from web_news where date_format(NewsCreateDate,'%Y-%m')=date_format(now(),'%Y-%m')

#查询上个月的数据
#select NewsCreateDate from web_news where date_format(NewsCreateDate,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

/////////////////////////////////////////////////////////////////////
  
  java 获取时间戳 2010-11-11 09:18:50| 分类: java | 标签: |字号大中小 订阅 .
  
   Date date = new Date();
   long time = date.getTime();
  
  //mysq 时间戳只有10位 要做处理
   String dateline = time + "";
   dateline = dateline.substring(0, 10);

本文摘自:天涯博客


除非注明,文章均为史亚永原创,欢迎转载!转载请注明本文地址,谢谢。

本文地址:http://www.shiyayong.cn/post/228.html

评论列表:

2  小易  2013-10-16 15:31:37 回复该留言  IP:119.36.193.56
mark,学习了,不错,谢谢分享
  茶馆老板  2014-1-17 17:14:25 回复该留言  IP:117.121.48.242
那以后常来哦
茶馆老板
小易
1  夏日博客  2013-10-14 13:16:50 回复该留言  IP:124.205.129.32
很实用,有点查询日历的感觉。
  茶馆老板  2014-1-17 17:14:15 回复该留言  IP:117.121.48.242
其实是转摘的,有些自己还没测试过呢
茶馆老板
夏日博客

发表评论:

(设置个性头像)

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

无觅相关文章插件,快速提升流量