SQL | 时间处理 Time

SQL时间相关处理~

基本语法

年

YEAR()：取日期中的年份

year('2021-12-31')
-- 2021

去年同一天：

add_months(current_date, -12)

月

MONTH()：取日期中的月份

当月第一天：

date_format(current_date, 'yyyy-MM-01')

当月最后一天：

-- date 可以为yyyyMMdd或yyyy-MM-dd
last_day(current_day)

上个月末（上个月最后一天）：

date_sub(date_format(current_date, 'yyyy-MM-01'), 1)
last_day(add_months(date, -1))

上个月同一天：

add_months(current_date, -1)

$n$个月前同一天：

add_months(current_date, -n)

月调度任务（仅每月最后一天有数），取最近有数一个分区：

where 
(
    case    when '${date}' = last_day('${date}') and date = '${date}' then 1 
            when '${date}' < last_day('${date}') and date = last_day(add_months('${date}', -1)) then 1
    else 0 end
) = 1

周

WEEKOFYEAR(STRING date)：返回时间字符出位于一年中的第几周内

weekofyear('2016-12-08 10:05:15')
-- 49

周几/星期几：

case    when cast(pmod(datediff(pt, '1900-01-07'), 7) as int) = 0 then 7 
        else cast(pmod(datediff(pt, '1900-01-07'), 7) 
end as weekdays
-- cast(pmod(datediff(pt, '1900-01-07'), 7)取值范围0-6
-- 或
cast(date_format(pt, 'u') as int)  -- 取值范围1-7

当周的周一：

date_sub(current_date, cast(date_format(current_date, 'u') as int) - 1)

最近一个周日：

date_sub(current_date, cast(date_format(current_date, 'u') as int))

格式转换

日期 to 日期

将yyyy/MM/dd格式的日期转换为yyyy-MM-dd格式：

concat_ws('-', split(date, '/')[0], lpad(split(date, '/')[1], 2, '0'), lpad(split(date, '/')[2], 2, '0'))

将yyyyMMdd格式转换为yyyy-MM-dd格式：

date_sub(date, 0)
date_add(date, 0)
from_unixtime(unix_timestamp(date, 'yyyyMMdd'), 'yyyy-MM-dd')

日期 to 时间戳

时间戳 to 日期

10位时间戳（bigint格式，单位秒）转yyyyMMdd：

from_unixtime(ts, 'yyyyMMdd')

10位时间戳（bigint格式，单位秒）转yyyy-MM-dd或yyyy-MM-dd HH:mm:ss：

from_unixtime(ts, 'yyyy-MM-dd')
from_unixtime(ts, 'yyyy-MM-dd HH:mm:ss')

13位时间戳（单位毫秒）转yyyyMMdd：

from_unixtime(ts / 1000, 'yyyyMMdd')

1毫秒 = $\frac{1}{1000}$ 秒

参考资料