SQL时间函数整理
SELECT unix_timestamp(); --获取当前时间戳
SELECT current_timestamp; --获取当前时间
SELECT from_unixtime(unix_timestamp()); --获取当前时间
SELECT CURRENT_DATE; --获取当前日期
SELECT datediff(CURRENT_DATE,'2021-12-01'); --日期差值 datediff(结束日期，开始日期)
SELECT date_add(CURRENT_DATE,12); --日期增加 date_add(日期，增加天数)
SELECT date_sub(CURRENT_DATE,12); --日期减少 date_sub(日期，减少天数)
SELECT (hour('2018-02-27 10:00:00')-hour('2018-02-25 19:00:00')+(datediff('2018-02-27 10:00:00','2018-02-25 19:00:00'))*24) as hour_subValue;
--两个时间之间的小时差
SELECT year(current_timestamp); --当前日期的年
SELECT month(current_timestamp); --当前日期的月
SELECT day(current_timestamp); --当前日期的日
SELECT hour(current_timestamp); --当前日期的小时
SELECT minute(current_timestamp); --当前日期的分钟
SELECT second(current_timestamp); --当前日期的秒
SELECT weekofyear(current_timestamp); --当前日期的周数(可设置星期几开始计算)
SELECT to_date(current_timestamp); --转成yyyy-MM-dd格式
SELECT to_date('20211229','yyyyMMdd'); --转成yyyy-MM-dd格式
SELECT last_day(current_timestamp); --当月最后一天
select trunc(current_date,'MM') as day; --当月第一天
select trunc(current_date,'YY') as day; --当年第一天
select next_day(current_timestamp, 'TU'); --返回当前时间下一个星期几对应的日期
unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)
1640769191
current_timestamp()
2021-12-29 17:13:16.825
from_unixtime(unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss), yyyy-MM-dd HH:mm:ss)
2021-12-29 17:13:32
current_date()
2021-12-29
datediff(current_date(), CAST(2021-12-01 AS DATE))
28
date_add(current_date(), 12)
2022-01-10
date_sub(current_date(), 12)
2021-12-17
hour_subValue
39
year(CAST(current_timestamp() AS DATE))
2021
month(CAST(current_timestamp() AS DATE))
12
dayofmonth(CAST(current_timestamp() AS DATE))
29
hour(current_timestamp())
17
minute(current_timestamp())
16
second(current_timestamp())
29
weekofyear(CAST(current_timestamp() AS DATE))
52
to_date(current_timestamp())
2021-12-29
to_date('20211229', 'yyyyMMdd')
2021-12-29
last_day(CAST(current_timestamp() AS DATE))
2021-12-31
day
2021-12-01
day
2021-01-01
next_day(CAST(current_timestamp() AS DATE), TU)
2022-01-04
SELECT dayofweek(current_timestamp); --当周第几天，周日是第一天
select date_add(current_timestamp,1 - dayofweek(current_timestamp)); --当周第一天日期，周日是第一天
select date_add(current_timestamp,7 - dayofweek(current_timestamp)); --当周最后一天日期，周六是最后一天
select case when dayofweek(current_timestamp) = 1 then 7 else dayofweek(current_timestamp) - 1 end;--当周第几天，周一是第一天
select date_add(current_timestamp,1 - case when dayofweek(current_timestamp) = 1 then 7 else dayofweek(current_timestamp) - 1 end);
select date_add(current_timestamp,7 - case when dayofweek(current_timestamp) = 1 then 7 else dayofweek(current_timestamp) - 1 end);
SELECT case when month(current_timestamp) != month(date_add(current_timestamp,7)) then 1 else 0 end; --判断当前日期是否是当年最后一周
SELECT trunc(current_timestamp,'Q');--当季第一天
select quarter(current_timestamp); --当年第几个季度
SELECT ceil(month(current_timestamp)/3);--当年第几个季度
SELECT lpad(ceil(month(current_timestamp)/3),2,0);--当年第几个季度
SELECT to_date(concat(year(current_timestamp),'-',lpad(ceil(month(current_timestamp)/3) * 3 -2,2,0),'-01'));--当季第一天
SELECT last_day(to_date(concat(year(current_timestamp),'-',lpad(ceil(month(current_timestamp)/3) * 3,2,0),'-01')));--当季最后一天
--SELECT sec_to_time(180);
dayofweek(CAST(current_timestamp() AS DATE))
5
date_add(CAST(current_timestamp() AS DATE), (1 - dayofweek(CAST(current_timestamp() AS DATE))))
2021-12-26
date_add(CAST(current_timestamp() AS DATE), (7 - dayofweek(CAST(current_timestamp() AS DATE))))
2022-01-01
CASE WHEN (dayofweek(CAST(current_timestamp() AS DATE)) = 1) THEN 7 ELSE (dayofweek(CAST(current_timestamp() AS DATE)) - 1) END
4
date_add(CAST(current_timestamp() AS DATE), (1 - CASE WHEN (dayofweek(CAST(current_timestamp() AS DATE)) = 1) THEN 7 ELSE (dayofweek(CAST(current_timestamp() AS DATE)) - 1) END))
2021-12-27
date_add(CAST(current_timestamp() AS DATE), (7 - CASE WHEN (dayofweek(CAST(current_timestamp() AS DATE)) = 1) THEN 7 ELSE (dayofweek(CAST(current_timestamp() AS DATE)) - 1) END))
2022-01-02
CASE WHEN (NOT (month(CAST(current_timestamp() AS DATE)) = month(date_add(CAST(current_timestamp() AS DATE), 7)))) THEN 1 ELSE 0 END
1
trunc(CAST(current_timestamp() AS DATE), Q)
NULL
quarter(CAST(current_timestamp() AS DATE))
4
CEIL((CAST(month(CAST(current_timestamp() AS DATE)) AS DOUBLE) / CAST(3 AS DOUBLE)))
4
lpad(CAST(CEIL((CAST(month(CAST(current_timestamp() AS DATE)) AS DOUBLE) / CAST(3 AS DOUBLE))) AS STRING), 2, CAST(0 AS STRING))
04
to_date(concat(CAST(year(CAST(current_timestamp() AS DATE)) AS STRING), '-', lpad(CAST(((CEIL((CAST(month(CAST(current_timestamp() AS DATE)) AS DOUBLE) / CAST(3 AS DOUBLE))) * CAST(3 AS BIGINT)) - CAST(2 AS BIGINT)) AS STRING), 2, CAST(0 AS STRING)), '-01'))
2021-10-01
last_day(to_date(concat(CAST(year(CAST(current_timestamp() AS DATE)) AS STRING), '-', lpad(CAST((CEIL((CAST(month(CAST(current_timestamp() AS DATE)) AS DOUBLE) / CAST(3 AS DOUBLE))) * CAST(3 AS BIGINT)) AS STRING), 2, CAST(0 AS STRING)), '-01')))
2021-12-31
注意事项：
1、测试SELECT trunc(current_timestamp,'Q');--当季第一天失败；
2、mysql函数sec_to_time可以用来秒数规整为时分秒，例如60规整为00：01：00
3、lpad(ceil(month(current_timestamp)/3) * 3,2,0)这部分计算当前日期当前季度第一个月和最后一个月使用等差计算公式来计算；
参考链接
