※過去ブログの転記
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,
DATE_ADD(create_date, INTERVAL -10 DAY) AS before_day,
DATE_ADD(create_date, INTERVAL 2 WEEK) AS after_week,
DATE_ADD(create_date, INTERVAL -2 WEEK) AS before_week,
DATE_ADD(create_date, INTERVAL 3 MONTH) AS after_month,
DATE_ADD(create_date, INTERVAL -3 MONTH) AS before_month,
DATE_ADD(create_date, INTERVAL 1 YEAR) AS after_year,
DATE_ADD(create_date, INTERVAL -1 YEAR) AS before_year
FROM users
WHERE 1
ORDER BY create_date DESC
↓
datetime型もOK
SELECT
create_time,
DATE_ADD(create_time, INTERVAL 1 HOUR) AS after_hour,
DATE_ADD(create_time, INTERVAL -1 HOUR) AS before_hour,
DATE_ADD(create_time, INTERVAL 30 MINUTE) AS after_minute,
DATE_ADD(create_time, INTERVAL -30 MINUTE) AS before_minute,
DATE_ADD(create_time, INTERVAL 30 SECOND) AS after_second,
DATE_ADD(create_time, INTERVAL -30 SECOND) AS before_second,
DATE_ADD(create_time, INTERVAL 10 MICROSECOND) AS after_microsecond,
DATE_ADD(create_time, INTERVAL -10 MICROSECOND) AS before_microsecond
FROM users
WHERE 1
ORDER BY create_time DESC
↓
フォーマット変えたかったらDATE_FORMAT()
してあげれば良い
DATE_FORMAT(DATE_ADD(create_time, INTERVAL 1 HOUR),'%H:%i:%s') AS after_hour,
unixtimeのフォーマット変換してDATE_ADD()する
DATE_ADD(FROM_UNIXTIME(create_time,"%Y-%m-%d") , INTERVAL 10 day) AS after_day,
INTERVAL
の存在忘れそう