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;
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;
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');
常用格式:
格式 | 描述 |
---|---|
%Y | 年(四位) |
%c | 月(数值(1-12)) |
%m | 月 (数值(00-12)) |
%d | 月的天,数值(00-31) |
%H | 小时 (00-23) |
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;
⚠️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
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';
更多参考: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;
3.2.2 TIMESTAMPDIFF()
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2): time different = date2 - date1
-- unit 类型
MICROSECOND 微秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 周
MONTH 月份
QUARTER
YEAR 年份
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;
更多其他时间函数:MySQL 8.0 Reference Manua:12.7 Date and Time Functions