読者です 読者をやめる 読者になる 読者になる

(-> % read write unlearn)

All opinions expressed are solely my own and do not express the views or opinions of my employer.

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() 、
それぞれのシノニム。違いはない。