阅读量:0
目录
日期转时间戳：unix_timestamp(日期值,'日期格式')
时间戳转日期from_unixtime(时间戳,'要转换的日期格式')
将yyyy-MM-dd HH:MM:SS截取为yyyy-MM-dd
1、常用日期的表示方法
基本的日期表示方法遵循如下格式
'${#锚定日期:日期格式#}'
例如：今天是20230523，我想以昨天的日期20220522为基准即date(0,0,-1)，得到本月的1号数据，那么日期格式就是yyyyMM01；
如果以上月的T-1日为基准，即date(0,-1,-1)...以此类推，下表总结了日常取数时会用到的各种日期写法。
| 日期 | 语句 |
|---|---|
| 昨日（20230522） | '{#date(0,0,-1):yyyyMMdd#}' `T-1` 或 '{dateSuffix}' T-1 |
| 前7日（20230516） T-7 | '{#date(0,0,-7):yyyyMMdd#}'`T-7` 或 '{sevenDaysBeforeSuffix}' |
| 本月1号（20230501） （取数日期为T-1日，后面同理） | '${#date(0,0,-1):yyyyMM01#}' |
| 上月1号（20230401） | '${#date(0,-1,-1):yyyyMM01#}' |
| T-1日上月同期（20230422） | '${#date(0,-1,-1):yyyyMMdd#}' |
| 去年当月1号（20220501） | '${#date(-1,0,-1):yyyyMM01#}' |
| T-1日去年同期（20220522） | '${#date(-1,0,-1):yyyyMMdd#}' |
| T-1日去年同期（如:2022-05-22） | '${#date(-1,0,-1):yyyy-MM-dd#}' |
这里可以发现，月初直接就写成yyyyMM01即可，但是月末因为有28、29、30、31各种情况，所以不能直接写，要用到last_day()函数计算月末，但是这里要注意：last_day()函数只能识别yyyy-mm-dd格式的日期，在使用时要注意转化
| 日期（以 T-1为基准日期） | 语法 |
|---|---|
| 本月底yyyyMMdd格式（20230531） | replace(last_day('${#date(0,0,-1):yyyy-MM-dd#}'),'-','') |
| 本月底yyyy-MM-dd格式（20230531） | last_day('${#date(0,0,-1):yyyy-MM-dd#}') |
| 上月底yyyyMMdd格式（20230430） | replace(last_day('${#date(0,-1,-1):yyyy-MM-dd#}'),'-','') |
2、时间格式的转换
我们常见的时间格式有如下几种
- 时间戳10位（精度到秒）或13位（精度到毫秒）
- 日期yyyyMMdd或yyyy-MM-dd
- 时间yyyy-MM-dd HH:MM:SS
2.1、时间戳和日期格式切换
日期转时间戳：unix_timestamp(日期值,'日期格式')
unix_timestamp('20171205','yyyymmdd')
unix_timestamp('2017-12-05','yyyy-mm-dd')
时间戳转日期from_unixtime(时间戳,'要转换的日期格式')
这里注意时间戳的位数，如果是13位，需要除以1000再进行转换
from_unixtime(cast(1657700687791/1000 as bigint),'yyyy-MM-dd HH:MM:SS')
from_unixtime(cast(1657700687791/1000 as bigint),'yyyy-MM-dd')
...
2.2、日期格式切换
yyyyMMdd和yyyy-MM-dd进行相互转换时，可以有两种方式：
方法一：先用unix_timestamp()转为时间戳，再使用from_unixtime()转换成需要的格式
方法二：先用substr()将时间截取出年月日的字符，再使用concat进行组合
方法1: from_unixtime+ unix_timestamp --20171205转成2017-12-05 select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual; --2017-12-05转成20171205 select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual; 方法2: substr + concat --20171205转成2017-12-05 select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',substr('20171205',7,2)) from dual; --2017-12-05转成20171205 select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),substr('2017-12-05',9,2)) from dual; 2.3、时间格式截取日期、小时等
将yyyy-MM-dd HH:MM:SS截取为yyyy-MM-dd
to_date(sell_order_create_time)
截取时间戳中的天数和小时数
通过调整from_unixtime的格式即可
from_unixtime(cast(1657700687791/1000 as BIGINT),'yyyy-MM-dd HH:MM:SS') , from_unixtime(cast(ts/1000 as BIGINT),'yyyy-MM-dd HH') as day_hour, from_unixtime(cast(ts/1000 as BIGINT),'yyyy-MM-dd') as day, from_unixtime(cast(ts/1000 as BIGINT),'HH') as hour, #截取小时数还有一个简便写法 hour(from_unixtime(cast(ts/1000 as BIGINT)))结果如下
| ts | hour(from_unixtime(cast(ts/1000 as BIGINT))), | day_hour | day | hour |
|---|---|---|---|---|
| 1657700687791 | 16 | 2022-07-13 16 | 2022-07-13 | 16 |
3、日期维度切换
| 语法 | 结果 |
|---|---|
| year('2023-05-23 12:23:14') | 2023 |
| month('2023-05-23 12:23:14') | 5 |
| day('2023-05-23 12:23:14') | 23 |
| hour('2023-05-23 12:23:14') | 12 |
| minute('2023-05-23 12:23:14') | 23 |
| second('2023-05-23 12:23:14') | 14 |
| weekofyear('2023-01-01 12:23:14') | 这里注意：跨年的那一周是按上年的周数统计，只有第一个当年的完整周才是第一周，所以2023年1月1日的周数是第52周 |
4、日期计算
4.1、计算日期差、月份差、时间差
使用datediff(后日期,前日期)计算日期差，yyyy-mm-dd格式可以直接计算，yyyymmdd需要处理之后计算
--yyyy-mm-dd格式计算日期差 datediff('2022-01-02', '2022-01-01') --yyyymmdd格式计算日期差 datediff(to_date(from_unixtime(UNIX_TIMESTAMP('20220102','yyyyMMdd'))),to_date(from_unixtime(UNIX_TIMESTAMP('20220101','yyyyMMdd'))))使用month_BETWEEN()计算月份，得到的是一个小数值，可以使用ceiling()和floor()函数向上或向下取整，一般实际场景下，使用floor比较多，
select '2022-01-01', '2022-02-28', months_between('2022-02-28','2022-01-01'),---1,87096774 floor(months_between('2022-02-28','2022-01-01')),--1 ceiling(months_between('2022-02-28','2022-01-01')),--2 floor(months_between( to_date(from_unixtime(UNIX_TIMESTAMP('20220228','yyyyMMdd'))), to_date(from_unixtime(UNIX_TIMESTAMP('20220101','yyyy-MM-dd'))) )) as mon_gap使用时间戳相减可以得到两个时间相差的秒数，再除60得到分钟数，除3600得到小时数
(unix_timestamp(time1)-unix_timestamp(time2)) as second (unix_timestamp(time1)-unix_timestamp(time2))/60 as minute (unix_timestamp(time1)-unix_timestamp(time2))/3600 as hour 4.2、日期加减
使用date_add()和date_sub()计算日期加减法
- date_add('2023-01-01',5) 得到2023-01-06
- date_sub('2023-01-03',2) 得到2023-01-01
通过日期加减可以计算我们想要的各种时间，这里简单举两个常用的例子：
取日期字段days所在周的周一和周日
date_add(days,2-if(dayofweek('yyyy-mm-dd')=1,8,dayofweek(days))) as `周一`, date_add(days,8-if(dayofweek('yyyy-mm-dd')=1,8,dayofweek(days))) as `周日`以2023-05-23（周二）为例，dayofweek('days')=3，这里周日是1，周六是7
2-3=-1，在2023-05-23的基础上加上-1，得到2023-05-22周一
8-3=5，在2023-05-23的基础上加上5，得到2023-05-28周日
取上个月的月份
以2022-01-01为例，2022-01-01截取日期值01，用2022-01-01减去01，得到2021-12-31，再截取前1-7位，得到2021-12
substr(date_sub('2022-01-01',cast(substr('2022-01-01',9,2))),1,7)--得到结果2021-12如果想得到202112，用replace()函数，把-替换即可
replace(待替换字段,'-','')
