mysql5.7文档:https://www.docs4dev.com/docs/zh/mysql/5.7/reference/
mysql5.7日期函数文档:https://www.docs4dev.com/docs/zh/mysql/5.7/reference/date-and-time-functions.html
mysql 中的时间函数
一、常见获取日期方式
1. 获取当前日期
| SELECT CURRENT_DATE; SELECT CURRENT_DATE(); SELECT CURDATE();
|
2.获取当前的日期时间
1 2 3 4 5 6 7 8
| SELECT NOW(); SELECT CURRENT_TIMESTAMP; SELECT CURRENT_TIMESTAMP; SELECT LOCALTIME; SELECT LOCALTIME(); SELECT LOCALTIMESTAMP; SELECT LOCALTIMESTAMP();
|
3.获取当前时间
1 2 3 4
| SELECT CURTIME(); SELECT CURRENT_TIME; SELECT CURRENT_TIME();
|
4.获取UTC时间、日期
1 2 3 4
| SELECT UTC_DATE(); SELECT UTC_TIME(); SELECT UTC_TIMESTAMP;
|
5. 获取时间戳
1 2 3
| SELECT UNIX_TIMESTAMP(); SELECT UNIX_TIMESTAMP() * 1000;
|
二、时间计算
1. 日期的相加:DATE_ADD 和 ADDDATE
1 2 3 4 5 6 7 8 9 10
| SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); SELECT DATE_ADD(NOW(), INTERVAL -1 DAY); SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND); SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE); SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH); SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR_MONTH); SELECT ADDDATE(NOW(), INTERVAL 1 MONTH)
|
2. 日期的相减:DATE_SUB 和 SUBDATE
1 2 3 4 5 6 7 8 9 10
| SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR); SELECT DATE_SUB(NOW(), INTERVAL -1 DAY); SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND); SELECT DATE_SUB(NOW(), INTERVAL 1 MINUTE); SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR); SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR_MONTH); SELECT SUBDATE(NOW(), INTERVAL 1 HOUR);
|
三、日期时间转换
1. 时间转时间戳
1 2
| SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
|
2. 日期转时间戳
1
| SELECT UNIX_TIMESTAMP(NOW());
|
3. 时间转字符串
1 2
| SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%m:%s');
|
4. 字符串转时间
1 2
| SELECT STR_TO_DATE('2022-02-16', '%Y-%m-%d');
|
四、获取某个时间
1. 日期的年月日时分秒获取
1 2 3 4 5 6 7
| SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW()); SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT SECOND(NOW());
|
2. 星期获取
1 2 3 4
| SELECT WEEKDAY(NOW()); SELECT WEEK(NOW()); SELECT WEEKOFYEAR(NOW());
|
3. 获取昨天
1 2 3 4 5
| SELECT DATE_ADD(NOW(), INTERVAL -1 DAY); SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); SELECT ADDDATE(NOW(), INTERVAL -1 DAY); SELECT SUBDATE(NOW(), INTERVAL 1 DAY);
|
4. 获取明天
1 2 3 4 5
| SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); SELECT DATE_SUB(NOW(), INTERVAL -1 DAY); SELECT ADDDATE(NOW(), INTERVAL 1 DAY); SELECT SUBDATE(NOW(), INTERVAL -1 DAY);
|
5. 获取上周:WEEK,MONTH,YEAR,HOUR,MINUTE,SECOND
1 2 3 4 5
| SELECT DATE_ADD(NOW(), INTERVAL -1 WEEK); SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK); SELECT ADDDATE(NOW(), INTERVAL -1 WEEK); SELECT SUBDATE(NOW(), INTERVAL 1 WEEK);
|
6. 获取下周:WEEK,MONTH,YEAR,HOUR,MINUTE,SECOND
1 2 3 4 5
| SELECT DATE_ADD(NOW(), INTERVAL 1 WEEK); SELECT DATE_SUB(NOW(), INTERVAL -1 WEEK); SELECT ADDDATE(NOW(), INTERVAL 1 WEEK); SELECT SUBDATE(NOW(), INTERVAL -1 WEEK);
|
7. 获取某天的几点
1 2
| SELECT DATE_ADD(CURDATE(), INTERVAL 0 HOUR); SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 DAY), INTERVAL 0 HOUR);
|