MySQL Function

MySQL日期函数与周计算

原创已于 2025-09-05 12:19:21 修改 · 143 阅读

0 ·

CC 4.0 BY-SA版权

创作源自于MyFreeIT

文章标签：

#mysql #数据库

于 2025-09-05 11:09:52 首次发布

数据库专栏收录该内容

59 篇文章

订阅专栏

## DATE_SUB

subtracting days

SELECT DATE_SUB('2025-09-05', INTERVAL 7 DAY);
-- Result: '2025-08-29'

SELECT DATE_SUB('2025-09-05', INTERVAL 3 MONTH);
-- Result: '2025-06-05'

SELECT DATE_SUB('2025-09-05', INTERVAL 1 YEAR);
-- Result: '2024-09-05'

SELECT DATE_SUB('2025-09-05 10:30:00', INTERVAL '2:15' HOUR_MINUTE);
-- Result: '2025-09-05 08:15:00'

## WEEK(date, firstdayofweek) 周

Optional. Specifies what day the week starts on. Can be one of the following:

0 - First day of week is Sunday
1 - First day of week is Monday and the first week of the year has more than 3 days
2 - First day of week is Sunday
3 - First day of week is Monday and the first week of the year has more than 3 days
4 - First day of week is Sunday and the first week of the year has more than 3 days
5 - First day of week is Monday
6 - First day of week is Sunday and the first week of the year has more than 3 days
7 - First day of week is Monday

## 日期是第几周

SELECT WEEK('2025-06-02 10:08:29', 2 ) - WEEK(DATE_SUB('2025-06-02 10:08:29', 
INTERVAL DAYOFMONTH('2025-06-02 10:08:29') - 1 DAY), 2 )+1 AS week_of_month;

SELECT WEEK('2025-06-30 10:08:29', 2 ) - WEEK(DATE_SUB('2025-06-30 10:08:29', 
INTERVAL DAYOFMONTH('2025-06-30 10:08:29') - 1 DAY), 2 )+1 AS week_of_month;



SELECT WEEK ( '2025-06-02 10:46:46', 2 ) - 
WEEK ( DATE_FORMAT( '2025-06-02 10:46:46', '%Y-%m-01' ), 2 )+1

SELECT WEEK ( '2025-06-30 10:46:46', 2 ) - WEEK ( 
DATE_FORMAT( '2025-06-30 10:46:46', '%Y-%m-01' ), 2 )+1