MaxCompute SQL使用小技巧之时间日期处理

2023-10-11 12:53:10

我们分析需求时经常会遇到如：最近一周、最近一个月、月初、月末、本季度...等等的时间修饰词，这类修饰词就构成了派生指标。使用Maxcomputer的童鞋都知道，其内置函数和Hive还是有些区别的，接下来我们就看看如何使用Maxcomputer内置日期函数。

时间戳与日期的相互转换

select from_unixtime(1629785626);--2021-08-24 14:13:46
select unix_timestamp(datetime '2021-08-24 14:13:46');--1629785626

yyyymmdd与yyyy-mm-dd之间相互转换

select to_char(to_date('20210824','yyyymmdd'),'yyyy-mm-dd');--2021-08-24
select to_char(to_date('2021-08-24','yyyy-mm-dd'),'yyyymmdd');--20210824

获取年份

select year('2021-08-24 14:13:46');--2021

获取月份

select datepart(datetime '2021-08-24 14:13:46','mm');--8
select month('2021-08-24 14:13:46');--8

获取日

select datepart(datetime '2021-08-24 14:13:46','dd');--24
select day('2021-08-24 14:13:46');--24

获取小时

select datepart(datetime '2021-08-24 14:13:46','hh');--14
select hour('2021-08-24 14:13:46');--14

获取分钟

select datepart(datetime '2021-08-24 14:13:46','mi');--13
select minute('2021-08-24 14:13:46');--13

获取秒

本周第几天

select weekday(to_date('2021-08-24','yyyy-mm-dd'));--1 周一返回的是0，周日为6

本月第几天

select dayofmonth('2021-08-24');--24

本年第几周

select weekofyear(to_date('2021-08-24','yyyy-mm-dd'));--34

本年第几天

select datediff(to_date('2021-08-24','yyyy-mm-dd'),datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'yyyy'));--235

本年第几季度

select quarter('2021-08-24');--3

本周第一天

select date_add(next_day('2021-08-24','MO'),-7);--2021-08-23

本周最后一天

select date_add(next_day('2021-08-24','MO'),-1);--2021-08-29

最近一周

select date_add('2021-08-24',-7);--2021-08-17

已知日期返回周几,2018-01-01刚好是周一

select case pmod(datediff('2021-08-24','2018-01-01'),7) 
            when 0 then '周一'
            when 1 then '周二'
            when 2 then '周三'
            when 3 then '周四'
            when 4 then '周五'
            when 5 then '周六'
            when 6 then '周日'
        end ;--周二

上周一

select date_add(next_day('2021-08-24','MO'),-14);--2021-08-16

上周末

select date_add(next_day('2021-08-24','MO'),-8);--2021-08-22

本月第一天

select datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'mm');--2021-08-01 00:00:00
select date_sub('2021-08-24',dayofmonth('2021-08-24')-1);--2021-08-01

本月最后一天

select lastday(to_date('2021-08-24','yyyy-mm-dd'));--2021-08-31
select last_day('2021-08-24');--2021-08-31

上个月月末

select dateadd(to_date(last_day('2021-08-24'),'yyyy-mm-dd'),-1,'mm');--2021-07-31 00:00:00

上个月初

select add_months(date_sub('2021-08-24',dayofmonth('2021-08-24')-1),-1);--2021-07-01

上个月同期

select add_months(to_date('2021-08-24','yyyy-mm-dd'),-1);--2021-07-24

本季度第一天

select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),quarter(to_date('2021-08-24','yyyy-mm-dd'))*3,'month'),-1,'mm');--2021-09-01 00:00:00

本季度最后一天

select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),quarter(to_date('2021-08-24','yyyy-mm-dd'))*3,'month'),-1,'dd');--2021-09-30 00:00:00

上个季度第一天

select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),(quarter(to_date('2021-08-24','yyyy-mm-dd'))-1)*3 ,'month'),-1,'mm');--2021-06-01 00:00:00

上个季度末一天

select dateadd(dateadd(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),(quarter(to_date('2021-08-24','yyyy-mm-dd'))-1)*3 ,'month'),-1,'dd');--2021-06-30 00:00:00

本年第一天

select datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year');--2021-01-01 00:00:00

本年最后一天

select date_add(add_months(datetrunc(to_date('2021-08-24','yyyy-mm-dd'),'year'),12),-1);--2021-12-31

去年同期

select add_months(to_date('2021-08-24','yyyy-mm-dd'),-12);--2020-08-24

关于日期之间的转换有很多中方式，不限于以上方式，希望能帮助到小伙伴

拜了个拜

码农公寓

相关文章