MYSQLでの日付比較演算子の確認

## 月跨ぎでページがエラーになってしまっていた。


$query = "select GET_D,TRUNCATE(avg(TAKEN),2) AVGT from T_Confirmation
where GET_D > CURDATE() - 8 group by GET_D";

## DB側にてデータの確認

mysql> select GET_D,TRUNCATE(avg(TAKEN),2) AVGT from T_Confirmation
where GET_D > CURDATE() - 8 group by GET_D;
Empty set, 1 warning (0.02 sec)

## データはあるので、where句の部分を確認。
## 実際にQueryを実行してみると以下のような日付が返ってきた。


mysql>select CURDATE() - 8;
+---------------+
| CURDATE() - 8 |
+---------------+
| 20100893 |
+---------------+
1 row in set (0.00 sec)


mysql> select CURRENT_DATE() - 8;
+--------------------+
| CURRENT_DATE() - 8 |
+--------------------+
| 20100893 |
+--------------------+
1 row in set (0.00 sec)

## 日付の表示を正しいものに修正する為に、DATA_ADDを利用する事にした。

mysql> SELECT DATE_ADD(CURRENT_DATE,INTERVAL 8 DAY);
+—————————————+
| DATE_ADD(CURRENT_DATE,INTERVAL 8 DAY) |
+—————————————+
| 2010-09-09 |
+—————————————+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURRENT_DATE,INTERVAL -8 DAY);
+—————————————-+
| DATE_ADD(CURRENT_DATE,INTERVAL -8 DAY) |
+—————————————-+
| 2010-08-24 |
+—————————————-+
1 row in set (0.00 sec)

mysql>

## 正しくデータが表示される事を確認

mysql> select GET_D,TRUNCATE(avg(TAKEN),2) AVGT from T_Confirmationwhere GET_D > DATE_ADD(CURRENT_DATE,INTERVAL -8 DAY) group by GET_D;
+————+——+
| GET_D | AVGT |
+————+——+
| 2010-08-25 | 0.77 |
| 2010-08-26 | 0.48 |
| 2010-08-27 | 0.32 |
| 2010-08-28 | 0.30 |
| 2010-08-29 | 0.28 |
| 2010-08-30 | 0.35 |
| 2010-08-31 | 0.34 |
+————+——+
7 rows in set (0.02 sec)

mysql>


select substring(GET_T,1,2) as GET_T,COUNT(*) as TOTAL from
T_Confirmation where MONTH(GET_D)= MONTH(DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY))
AND TAKEN >= 10 Group by substring(GET_T,1,2);

MONTH(DATE_ADD(CURRENT_DATE,INTERVAL -1 DAY))

## 全てのPHPファイルを修正する為に、ワンライナーで一気に書き換えで終了。
## ワンライナーでフォルダー配下のPHPを全て修正して解決!!

perl -pi -e 's/CURDATE\(\) - 8/DATE_ADD\(CURRENT_DATE,INTERVAL -8 DAY\)/' *.php


MYSQLにてテーブルサイズの調査を行う。


select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as allMB,
floor((data_length)/1024/1024) as dMB,
floor((index_length)/1024/1024) as iMB
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;


+------------------+--------+----------+-------+-------+------+------+
| table_name | engine | tbl_rows | rlen | allMB | dMB | iMB |
+------------------+--------+----------+-------+-------+------+------+
| mt_searchlog | MyISAM | 2799447 | 146 | 418 | 390 | 27 |
| mt_entry | MyISAM | 9434 | 12629 | 114 | 113 | 1 |
| mt_log | MyISAM | 59543 | 257 | 17 | 14 | 3 |
| mt_entryphotoAll | MyISAM | 80260 | 47 | 5 | 3 | 1 |
| mt_trackback | MyISAM | 8908 | 278 | 2 | 2 | 0 |
| mt_template | MyISAM | 373 | 2870 | 1 | 1 | 0 |
| mt_placement | MyISAM | 9447 | 18 | 0 | 0 | 0 |
| mt_entryphoto | MyISAM | 9079 | 37 | 0 | 0 | 0 |
| mt_session | MyISAM | 2536 | 96 | 0 | 0 | 0 |
| mt_tbping | MyISAM | 493 | 473 | 0 | 0 | 0 |
| mt_rfdata | MyISAM | 256 | 288 | 0 | 0 | 0 |
| mt_plugindata | MyISAM | 7 | 3074 | 0 | 0 | 0 |
| mt_category | MyISAM | 151 | 52 | 0 | 0 | 0 |
| mt_errorlist | MyISAM | 19 | 572 | 0 | 0 | 0 |
| mt_blog | MyISAM | 4 | 379 | 0 | 0 | 0 |
| mt_tag | MyISAM | 5 | 22 | 0 | 0 | 0 |
| mt_templatemap | MyISAM | 12 | 77 | 0 | 0 | 0 |
| mt_author | MyISAM | 3 | 114 | 0 | 0 | 0 |
| mt_permission | MyISAM | 14 | 88 | 0 | 0 | 0 |
| mt_categoryinfo | MyISAM | 11 | 207 | 0 | 0 | 0 |
| mt_config | MyISAM | 1 | 80 | 0 | 0 | 0 |
| test2 | MyISAM | 2 | 7 | 0 | 0 | 0 |
| mt_accessranking | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_comment | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_notification | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_objecttag | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_fileinfo | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_ipbanlist | MyISAM | 0 | 0 | 0 | 0 | 0 |
| mt_sendping | MyISAM | 0 | 0 | 0 | 0 | 0 |
+------------------+--------+----------+-------+-------+------+------+
29 rows in set (0.00 sec)

mysql>

■参考
http://opendatabaselife.blogspot.com/2009_08_01_archive.html
http://webmemo.uzuralife.com/category/pkweryrfvid.html/?search=&opt=0&order=0&from=380