mysql> select count(*) from trade;
+----------+
| count(*) |
+----------+
| 195682 |
+----------+
1 row in set (0.00 sec)
----------------------------------------------------------------------------------------------
mysql> select count(*) from trade where o_status="배송완료" and date_format(reg_date,'%Y-%m-%d') > date_format(date_add(now(), interval -1 year), '%Y-%m-%d');
+----------+
| count(*) |
+----------+
| 55525 |
+----------+
1 row in set (1.31 sec)
mysql> select count(*) from trade where o_status="배송완료" and reg_date between '2009-02-23 00:00:00' and '2010-02-23 23:59:59';
+----------+
| count(*) |
+----------+
| 55695 |
+----------+
1 row in set (1.09 sec)
----------------------------------------------------------------------------------------------
mysql> select count(*) from trade where o_status="배송완료" and date_format(reg_date,'%Y-%m-%d') > date_format(date_add(now(), interval -1 month), '%Y-%m-%d');
+----------+
| count(*) |
+----------+
| 4114 |
+----------+
1 row in set (1.30 sec)
mysql> select count(*) from trade where o_status="배송완료" and reg_date between '2010-01-24 00:00:00' and '2010-02-23 23:59:59';
+----------+
| count(*) |
+----------+
| 4116 |
+----------+
1 row in set (0.04 sec)
----------------------------------------------------------------------------------------------
mysql> select count(*) from trade where o_status="배송완료" and date_format(reg_date,'%Y-%m-%d') > date_format(date_add(now(), interval -1 week), '%Y-%m-%d');
+----------+
| count(*) |
+----------+
| 911 |
+----------+
1 row in set (1.31 sec)
mysql> select count(*) from trade where o_status="배송완료" and reg_date between '2010-02-17 00:00:00' and '2010-02-23 23:59:59';
+----------+
| count(*) |
+----------+
| 911 |
+----------+
1 row in set (0.01 sec)
----------------------------------------------------------------------------------------------
mysql> select count(*) from trade where o_status="배송완료" and date_format(reg_date,'%Y-%m-%d') > date_format(date_add(now(), interval -1 day), '%Y-%m-%d');
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (1.31 sec)
mysql> select count(*) from trade where o_status="배송완료" and reg_date between '2010-02-23 00:00:00' and '2010-02-23 23:59:59';
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
----------------------------------------------------------------------------------------------
내부적으로 test 해볼결과 값이다~
interval 보다는 between이 시간이 많이 절약되는듯 하다~~
참고해서 처리 하시길 바랍니다.