mysqlの日付関数。毎回忘れるので、メモ。この interval ってなんの意味があるんだろ。引数が、時点じゃなく時間(間隔?)ですよ、的な解釈でいいのか?
WindowsXPでmysql4.1です。
■基本
mysql> select date_add('2011-01-01 11:11:11', interval 30 day); +--------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 30 day) | +--------------------------------------------------+ | 2011-01-31 11:11:11 | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2011-01-01 11:11:11', interval 31 day); +--------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 31 day) | +--------------------------------------------------+ | 2011-02-01 11:11:11 | +--------------------------------------------------+ 1 row in set (0.00 sec)
■演算対象いろいろ。年、月、日、時、分、秒、ミリ秒
mysql> select date_add('2011-01-01 11:11:11', interval 2 year); +--------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 2 year) | +--------------------------------------------------+ | 2013-01-01 11:11:11 | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2011-01-01 11:11:11', interval 2 month); +---------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 2 month) | +---------------------------------------------------+ | 2011-03-01 11:11:11 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2011-01-01 11:11:11', interval 2 day); +-------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 2 day) | +-------------------------------------------------+ | 2011-01-03 11:11:11 | +-------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2011-01-01 11:11:11', interval 2 hour); +--------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 2 hour) | +--------------------------------------------------+ | 2011-01-01 13:11:11 | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2011-01-01 11:11:11', interval 2 minute); +----------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 2 minute) | +----------------------------------------------------+ | 2011-01-01 11:13:11 | +----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2011-01-01 11:11:11', interval 2 second); +----------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval 2 second) | +----------------------------------------------------+ | 2011-01-01 11:11:13 | +----------------------------------------------------+ 1 row in set (0.00 sec)
■減算
mysql> select date_add('2011-01-01 11:11:11', interval -2 year); +---------------------------------------------------+ | date_add('2011-01-01 11:11:11', interval -2 year) | +---------------------------------------------------+ | 2009-01-01 11:11:11 | +---------------------------------------------------+ 1 row in set (0.02 sec) mysql> select date_sub('2011-01-01 11:11:11', interval 2 year); +--------------------------------------------------+ | date_sub('2011-01-01 11:11:11', interval 2 year) | +--------------------------------------------------+ | 2009-01-01 11:11:11 | +--------------------------------------------------+ 1 row in set (0.00 sec)
ADDDATE() は DATE_ADD() 、
SUBDATE() は DATE_SUB() 、
それぞれのシノニム。違いはない。