Edited at

mySQLの日付処理いろいろ

More than 3 years have passed since last update.

mySQLでよく使う日付処理をまとめてみました。

もっと良い書き方があれば、コメントをお願いします。


  • システム日付

mysql> select curdate();

+------------+

| curdate() |

+------------+

| 2016-02-26 |

+------------+

1 row in set (0.16 sec)

mysql> select curdate() + 0;

+---------------+

| curdate() + 0 |

+---------------+

| 20160226 |

+---------------+

1 row in set (0.00 sec)


  • システム日付時刻

mysql> select now();

+---------------------+

| now() |

+---------------------+

| 2016-02-26 12:16:15 |

+---------------------+

1 row in set (0.00 sec)

mysql> select now()+0;

+-----------------------+

| now()+0 |

+-----------------------+

| 20160226121618.000000 |

+-----------------------+

1 row in set (0.00 sec)


  • 当月1日

mysql> select DATE_FORMAT( CURDATE() , '%Y-%m-01' );

+---------------------------------------+

| DATE_FORMAT( CURDATE() , '%Y-%m-01' ) |

+---------------------------------------+

| 2016-02-01 |

+---------------------------------------+

1 row in set (0.00 sec)


  • 当月末日

mysql> select lAST_DAY(CURDATE());

+---------------------+

| lAST_DAY(CURDATE()) |

+---------------------+

| 2016-02-29 |

+---------------------+

1 row in set (0.00 sec)


  • 前月1日

mysql> select DATE_FORMAT( ADDDATE( CURDATE() , INTERVAL -1 MONTH) , '%Y-%m-01' );

+---------------------------------------------------------------------+

| DATE_FORMAT( ADDDATE( CURDATE() , INTERVAL -1 MONTH) , '%Y-%m-01' ) |

+---------------------------------------------------------------------+

| 2016-01-01 |

+---------------------------------------------------------------------+

1 row in set (0.00 sec)


  • 前月末日

mysql> select LAST_DAY( ADDDATE( CURDATE() , INTERVAL -1 MONTH) );

+-----------------------------------------------------+

| LAST_DAY( ADDDATE( CURDATE() , INTERVAL -1 MONTH) ) |

+-----------------------------------------------------+

| 2016-01-31 |

+-----------------------------------------------------+

1 row in set (0.00 sec)


  • 来月1日

mysql> select DATE_FORMAT( ADDDATE( CURDATE() , INTERVAL 1 MONTH) , '%Y-%m-01' );

+--------------------------------------------------------------------+

| DATE_FORMAT( ADDDATE( CURDATE() , INTERVAL 1 MONTH) , '%Y-%m-01' ) |

+--------------------------------------------------------------------+

| 2016-03-01 |

+--------------------------------------------------------------------+

1 row in set (0.00 sec)


  • 来月末日

mysql> select LAST_DAY( ADDDATE( CURDATE() , INTERVAL 1 MONTH) );

+----------------------------------------------------+

| LAST_DAY( ADDDATE( CURDATE() , INTERVAL 1 MONTH) ) |

+----------------------------------------------------+

| 2016-03-31 |

+----------------------------------------------------+

1 row in set (0.00 sec)


  • 当日分(1日分) (column_nameはdatetime)

select count(*) from table_name where date(column_name) = curdate();


  • 当月分(1か月分) (column_nameはdatetime)

select count(*) from table_name where column_name >= DATE_FORMAT( CURDATE() , '%Y-%m-01' ) and column_name <= LAST_DAY(CURDATE());