※過去ブログの転記
| create_date |
|---|
| 2018-09-05 |
| 2018-09-05 |
| 2018-09-03 |
| 2018-09-02 |
| 2018-08-21 |
n日後などの日付を取得する
SELECT create_date, DATE_ADD(create_date, INTERVAL 10 DAY) AS after_day,/*10日後*/ DATE_ADD(create_date, INTERVAL -10 DAY) AS before_day,/*10日前*/ DATE_ADD(create_date, INTERVAL 2 WEEK) AS after_week,/*2週間後*/ DATE_ADD(create_date, INTERVAL -2 WEEK) AS before_week,/*2週間前*/ DATE_ADD(create_date, INTERVAL 3 MONTH) AS after_month,/*3ヶ月後*/ DATE_ADD(create_date, INTERVAL -3 MONTH) AS before_month,/*3ヶ月前*/ DATE_ADD(create_date, INTERVAL 1 YEAR) AS after_year,/*1年後*/ DATE_ADD(create_date, INTERVAL -1 YEAR) AS before_year/*1年前*/ FROM users WHERE 1 ORDER BY create_date DESC
↓
| create_date | after_day | before_day | after_week | before_week | after_month | before_month | after_year | before_year |
|---|---|---|---|---|---|---|---|---|
| 2018-09-05 | 2018-09-15 | 2018-08-26 | 2018-09-19 | 2018-08-22 | 2018-12-05 | 2018-06-05 | 2019-09-05 | 2017-09-05 |
| 2018-09-05 | 2018-09-15 | 2018-08-26 | 2018-09-19 | 2018-08-22 | 2018-12-05 | 2018-06-05 | 2019-09-05 | 2017-09-05 |
| 2018-09-03 | 2018-09-13 | 2018-08-24 | 2018-09-17 | 2018-08-20 | 2018-12-03 | 2018-06-03 | 2019-09-03 | 2017-09-03 |
| 2018-09-02 | 2018-09-12 | 2018-08-23 | 2018-09-16 | 2018-08-19 | 2018-12-02 | 2018-06-02 | 2019-09-02 | 2017-09-02 |
| 2018-08-21 | 2018-08-31 | 2018-08-11 | 2018-09-04 | 2018-08-07 | 2018-11-21 | 2018-05-21 | 2019-08-21 | 2017-08-21 |
datetime型もOK
| create_time |
|---|
| 2018-09-05 08:00:00 |
| 2018-09-05 12:35:00 |
| 2018-08-21 20:10:22 |
| 2018-09-02 11:10:48 |
| 2018-09-03 19:13:14 |
SELECT create_time, DATE_ADD(create_time, INTERVAL 1 HOUR) AS after_hour,/*1時間後*/ DATE_ADD(create_time, INTERVAL -1 HOUR) AS before_hour,/*1時間前*/ DATE_ADD(create_time, INTERVAL 30 MINUTE) AS after_minute,/*30分後*/ DATE_ADD(create_time, INTERVAL -30 MINUTE) AS before_minute,/*30分前*/ DATE_ADD(create_time, INTERVAL 30 SECOND) AS after_second,/*30秒後*/ DATE_ADD(create_time, INTERVAL -30 SECOND) AS before_second,/*30秒前*/ DATE_ADD(create_time, INTERVAL 10 MICROSECOND) AS after_microsecond,/*10マイクロ秒後*/ DATE_ADD(create_time, INTERVAL -10 MICROSECOND) AS before_microsecond/*10マイクロ秒前*/ FROM users WHERE 1 ORDER BY create_time DESC
↓
| create_time | after_hour | before_hour | after_minute | before_minute | after_second | before_second | after_microsecond | before_microsecond |
|---|---|---|---|---|---|---|---|---|
| 2018-09-05 12:35:00 | 2018-09-05 13:35:00 | 2018-09-05 11:35:00 | 2018-09-05 13:05:00 | 2018-09-05 12:05:00 | 2018-09-05 12:35:30 | 2018-09-05 12:34:30 | 2018-09-05 12:35:00.000010 | 2018-09-05 12:34:59.999990 |
| 2018-09-05 08:00:00 | 2018-09-05 09:00:00 | 2018-09-05 07:00:00 | 2018-09-05 08:30:00 | 2018-09-05 07:30:00 | 2018-09-05 08:00:30 | 2018-09-05 07:59:30 | 2018-09-05 08:00:00.000010 | 2018-09-05 07:59:59.999990 |
| 2018-09-03 19:13:14 | 2018-09-03 20:13:14 | 2018-09-03 18:13:14 | 2018-09-03 19:43:14 | 2018-09-03 18:43:14 | 2018-09-03 19:13:44 | 2018-09-03 19:12:44 | 2018-09-03 19:13:14.000010 | 2018-09-03 19:13:13.999990 |
| 2018-09-02 11:10:48 | 2018-09-02 12:10:48 | 2018-09-02 10:10:48 | 2018-09-02 11:40:48 | 2018-09-02 10:40:48 | 2018-09-02 11:11:18 | 2018-09-02 11:10:18 | 2018-09-02 11:10:48.000010 | 2018-09-02 11:10:47.999990 |
| 2018-08-21 20:10:22 | 2018-08-21 21:10:22 | 2018-08-21 19:10:22 | 2018-08-21 20:40:22 | 2018-08-21 19:40:22 | 2018-08-21 20:10:52 | 2018-08-21 20:09:52 | 2018-08-21 20:10:22.000010 | 2018-08-21 20:10:21.999990 |
フォーマット変えたかったらDATE_FORMAT()してあげれば良い
DATE_FORMAT(DATE_ADD(create_time, INTERVAL 1 HOUR),'%H:%i:%s') AS after_hour,/*1時間後*/
unixtimeのフォーマット変換してDATE_ADD()する
DATE_ADD(FROM_UNIXTIME(create_time,"%Y-%m-%d") , INTERVAL 10 day) AS after_day,/*10日後*/
INTERVALの存在忘れそう
