SQL-时间处理汇总

目录

1、常用日期的表示方法

2、时间格式的转换

2.1、时间戳和日期格式切换

日期转时间戳：unix_timestamp(日期值,'日期格式')

时间戳转日期from_unixtime(时间戳,'要转换的日期格式')

2.2、日期格式切换

2.3、时间格式截取日期、小时等

将yyyy-MM-dd HH:MM:SS截取为yyyy-MM-dd

截取时间戳中的天数和小时数

3、日期维度切换

4、日期计算

4.1、计算日期差、月份差、时间差

4.2、日期加减

---

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(待替换字段,'-','')