MySQL常用的日期函数_mysql extract week

1. 获取当前时间

• 获取当前日期

  SELECT CURRENT_DATE;

• 获取当前时间

  SELECT CURRENT_TIME;

• 获取当前日期和时间

  SELECT CURRENT_TIMESTAMP;

2. 截取日期元素

2.1 直接使用MySQL的日期函数

SELECT 
year(CURRENT_TIMESTAMP) as year,
month(CURRENT_TIMESTAMP) as month,
day(CURRENT_TIMESTAMP) as day,
date(CURRENT_TIMESTAMP) as date,
quarter(CURRENT_TIMESTAMP) as quarter,
week(CURRENT_TIMESTAMP) as week,
time(CURRENT_TIMESTAMP) as time, 
hour(CURRENT_TIMESTAMP) as hour, 
minute(CURRENT_TIMESTAMP) as minute,
second(CURRENT_TIMESTAMP) as second,
microsecond(CURRENT_TIMESTAMP) as microsecond;
1
2
3
4
5
6
7
8
9
10
11
12

2.2 EXTRACT函数（适用于大部分SQL）

SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(QUARTER    FROM CURRENT_TIMESTAMP) AS quarter,
EXTRACT(WEEK    FROM CURRENT_TIMESTAMP) AS week,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
1
2
3
4
5
6
7
8
9

MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。
参考来源：DataWhale-SQL: Task03：复杂一点的查询 - 3.3.3日期函数

2.3 DATE_FORMAT函数

DATE_FORMAT函数可以以不同的格式显示日期/时间数据。

-- 用now获取现在时间
select
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'),
DATE_FORMAT(NOW(),'%m-%d-%Y'),
DATE_FORMAT(NOW(),'%d %b %y'),
DATE_FORMAT(NOW(),'%d %b %Y %T:%f');
1
2
3
4
5
6

常用格式：

格式

描述

%Y

年（四位）

%c

月（数值(1-12)）

%m

月 （数值(00-12)）

%d

月的天，数值(00-31)

%H

小时 (00-23）

更多请见：MySQL DATE_FORMAT() 函数

2.4 其他日期函数

select CURRENT_TIMESTAMP as now,
-- 日期在一年/月/周里的位置
dayofyear(CURRENT_TIMESTAMP) as dayofyear,
dayofmonth(CURRENT_TIMESTAMP) as dayofmonth,
dayofweek(CURRENT_TIMESTAMP) as dayofweek,
weekday(CURRENT_TIMESTAMP) as weekday,
-- 一周在一年里的位置
weekofyear(CURRENT_TIMESTAMP) as weekofyear,
week(CURRENT_TIMESTAMP) as week,
-- 返回年份+week的位置
yearweek(CURRENT_TIMESTAMP) as yearweek;
1
2
3
4
5
6
7
8
9
10
11

MySQLweekday() 函数和 dayofweek() 类似，都是返回“某天”在一周中的位置。不同在于参考的标准
weekday：(0 =Monday, 1 = Tuesday, …, 6 = Sunday)
dayofweek：（1 = Sunday, 2 = Monday,…, 7 = Saturday）

参考链接：MySQL 日期时间 Extract（选取） 函数

3. 日期计算

3.1 日期加减法

3.1.1 添加单个时间间隔

加法：DATE_ADD()：DATE_ADD(date,INTERVAL expr type)
减法：DATE_SUB()：DATE_SUB(date,INTERVAL expr type)

• date 参数是合法的日期表达式

• expr 参数是您希望添加/减少的时间间隔。

• type 参数是希望添加/减少的年/月/日/小时…

  select now() as now,
  date_add(now(),INTERVAL ‘5’ day) as ‘add 5 days’,
  date_sub(now(), INTERVAL ‘5’ day) as ‘sub 5 days’,
  date_add(now(), INTERVAL ‘5’ year) as ‘add 5 years’,
  date_add(now(), INTERVAL ‘5’ month) as ‘add 5 months’,
  date_add(now(), INTERVAL ‘5’ week) as ‘add 5 weeks’,
  date_add(now(), INTERVAL ‘5’ hour) as ‘add 5 hours’,
  date_add(now(), INTERVAL ‘5’ minute) as ‘add 5 minutes’;

3.1.2 添加多个时间间隔

type类型有下面几种，都是表示从 xx到xx。比如 year_month表示从年到月，hour_sencond表示从小时到分钟

SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
1
2
3
4
5
6
7
8
9
10
11

Examples:

select now() as now, 
date_add(now(), INTERVAL '1:1' YEAR_MONTH) as 'add 1year 1 month',
date_add(now(), INTERVAL '1:1:1' HOUR_SECOND) as 'add 1H 1minute 1 second';
1
2
3

更多参考：MySQL DATE_ADD() 函数

3.2 计算时间差

3.2.1 DATEDIFF() / TIMEDIFF()

DATEDIFF(date1,date2): date different = d1 - d2
TIMEDIFF(time1,time2): time different = t1 - t2

Examples:

select 
DATEDIFF('2021-11-02 10:00:50', '2021-11-01 11:00:00') as date_diff,
TIMEDIFF('2021-11-02 10:00:50', '2021-11-01 11:00:00') as time_diff;
1
2
3

3.2.2 TIMESTAMPDIFF()

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2): time different = date2 - date1

-- unit 类型
MICROSECOND  微秒
SECOND  秒
MINUTE  分钟
HOUR  小时
DAY  天
WEEK  周
MONTH  月份
QUARTER 
YEAR  年份
1
2
3
4
5
6
7
8
9
10

Example：

select '2021-11-01 10:00:50' as date1, '2021-11-03 11:00:00' as date2,
timestampdiff(year,'2021-11-01 10:00:50', '2021-11-03 11:00:0') as year_diff,
timestampdiff(month,'2021-11-01 10:00:50', '2021-11-03 11:00:0') as month_diff,
timestampdiff(day,'2021-11-01 10:00:50', '2021-11-03 11:00:0') as day_diff,
timestampdiff(hour,'2021-11-01 10:00:50', '2021-11-03 11:00:0') as hour_diff;
1
2
3
4
5

更多其他时间函数：MySQL 8.0 Reference Manua：12.7 Date and Time Functions