MySQL SQL 日付関連

月末

 SELECT LAST_DAY(NOW());

月初

 SELECT DATE_FORMAT(NOW(), '%Y-%m-01');

2週間後 (INTERVAL)

 SELECT NOW() + INTERVAL 2 WEEK;

日数の差

 SELECT DATEDIFF(CAST('2014-12-31' AS date), CAST('2014-11-01' AS date));
   => 60

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_datediff

タイムスタンプを日付にキャスト

 SELECT DATE(NOW());

部分フィールドの取得

 SELECT EXTRACT(YEAR FROM '1999-07-02');

INT（またはString）型の年月日からDate型の日付を作る

 SELECT STR_TO_DATE(CONCAT(year,'-',month,'-',day), '%Y-%c-%e') AS date FROM t1;

• http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date
• http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

書式指定 DATE_FOMRMAT()

 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00');
 +-----------------------------------------+
 | DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00') |
 +-----------------------------------------+
 | 2015-06-02 03:41:00                     |
 +-----------------------------------------+

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

参考

• http://dev.mysql.com/doc/refman/5.1-olh/ja/date-and-time-functions.html
• http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html