【转】MySQL关于日期记录

转自：http://blog.csdn.net/dreamboycx/article/details/8293636

http://blog.csdn.net/flm_0722/article/details/4961758

http://blog.csdn.net/chenhj1988918/article/details/7569221

 

本日、本周和本月的SQL语句

$time = time()+8*3600;
$nowtime = date("Y-m-d",$time);
$weekfirst = strtotime('-1 week Monday');
$monthfirst = mktime(0,0,0,date('m'),01,date('Y'));

本周的SQL语句：

select order_amount from ecm_order where status=20 AND ".$weekfirst." <= add_time AND add_time <= ".$time."   

本月的SQL语句：

select order_amount from ecm_order where status=20 AND  ".$monthfirst." <= add_time AND add_time <= ".$time." 

 

-------------------------------------------------------------------------------------------------------------

 

mysql查询上周、月、季度、年和本周、月、季度、年

#查询本周记录
select * from ht_invoice_information where WEEKOFYEAR(create_date)=WEEKOFYEAR(NOW());
#查询上周记录
select * from ht_invoice_information where create_date>=date_add(now(),interval -(8 + weekday(now())) day)
and create_date<=date_add(now(),interval -(1 + weekday(now())) day);

#或者

select * from `ht_invoice_information` where WEEKOFYEAR(create_date)=WEEKOFYEAR(DATE_SUB(now(),INTERVAL 1 week));
#查询本月数据
select * from ht_invoice_information where MONTH(create_date)=MONTH(NOW()) and year(create_date)=year(now());
#查询上月数据
select * from ht_invoice_information where create_date<=last_day(date_add(now(),interval -1 MONTH))
and create_date>=DATE_FORMAT(concat(extract(year_month from date_add(now(),interval -1 MONTH)),'01'),'%Y-%m-%d');

#或者

select * from `ht_invoice_information` where MONTH(create_date)=MONTH(DATE_SUB(NOW(),interval 1 month))
and year(create_date)=year(now());
#查询本季度数据
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 * 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

本季度的第一天 ，然后你可以把字符串再转换为日期。 方法很多，包括同一个函数的别名（同义词）也很多。比如CURDATE(),CURRENT_DATE(), CURRENT_DATE, NOW 等都可以返回当天

mysql教程> select CURDATE(),ELT(QUARTER(CURDATE()),
-> year(CURDATE())*1000+0101,
-> year(CURDATE())*1000+0401,
-> year(CURDATE())*1000+0701,
-> year(CURDATE())*1000+1001) as firstDayofQ;
+————+————-+
| CURDATE() | firstDayofQ |
+————+————-+
| 2009-05-19 | 2009401 |
+————+————-+
1 row in set (0.00 sec)

mysql>

用mysql语句获取本季度的第一天

本月的第一天，
date(
concat(year(curdate()),’-',month(curdate()),’-',’1′))
本周的第一天
curdate()-
WEEKDAY(curdate())

用mysql语句获取本季度的第一天

date(
concat(year(curdate()),’-',
if(quarter(curdate())=1,1,if(quarter(curdate())=2,4,if(quarter(curdate())=3,7,10))

用mysql语句获取本季度的第一天：
优化：
date(
concat(year(curdate()),’-',elt(quarter(curdate()),1,4,7,10),’-',1))

本周的第一天
date_add(date(’2009-5-29′),INTERVAL -weekday(’2009-5-29′) day)

mysql 获取当天发布文件内容sql语句

SELECT * FROM `p8_memberdata` where regdate BETWEEN (UNIX_TIMESTAMP(NOW())-86440) AND NOW()
SELECT * FROM `p8_memberdata` WHERE DATE_FORMAT(FROM_UNIXTIME(regdate),’%Y-%m-%d’)= DATE_FORMAT(NOW(),’%Y-%m-%d’) order by uid desc