1/50 hive sql 日期处理函数

一、获取当前时间

--获取当前格式化日期字符串
select current_date()
--获取当前格式化时间字符串
select current_timestamp()
--获取当前unix数字时间戳
select unix_timestamp() 
============================
｜result｜
 2022-11-24
 2022-11-24 16:10:26    
 1669277426

二、获取指定时间

--year/quarter/month/day/hour/minute/second() 获取日期中的年/季度/月/日/时/分/秒
select  year(current_date()) as year
       ,quarter(current_date()) as quarter
       ,month(current_date()) as month
       ,day(current_date()) as day
       ,hour(current_timestamp()) as hour
       ,minute(current_timestamp()) as minute
       ,second(current_timestamp()) as second
========================================
|result|
| year | quarter | month | day | hour | minute | second|
| 2022 |    4    |   11  |  24 |  16  |    3   |   42  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
select  last_day(current_date())--返回当前日期当月的最后一天的日期
       ,next_day(current_date(),'MO')--返回当前日期下周一的日期,例子周一（‘MO’）、下周二（‘TU’）、下周三（‘WE’）、下周四（‘TH’）、下周五（‘FR’）、下周六（‘SA’）、下周日（‘SU’）
       ,trunc(current_date(),'MM')--取当前日期月的第一天YYYY/当年的第一天MM
===================================================================================================================================================================
|result|
|     c0     |      c1    |     c2     |
| 2022-11-30 | 2022-11-28 | 2022-11-01 |

三、转换时间格式

【1】to_date(string timestamp)

作用：将格式化日期字符串转换成日期格式 yyyy-MM-dd

返回类型：>>> date

--格式化日期字符串：yyyy-MM-dd
select  to_date('2022-11-24') --日期字符串
       ,to_date('2022-11-24 16:18:21') --时间戳字符串
==========================================================
|result|
|     c0     |     c1     | 
| 2022-11-24 | 2022-11-24 |

【2】date_format(date string timestamp,string format)

作用：将格式化日期字符串转换成指定的格式

返回类型： >>> string

select  date_format('2022-11-24','yyyy-MM-dd HH:mm:ss') --yyyy-MM-dd HH:mm:ss
       ,date_format('2022-11-24 16:18:21','yyyy-MM-dd') --yyyy-MM-dd
       ,date_format('2022-11-24 16:18:21','yyyyMMdd') --yyyyMMdd
       ,date_format('2022-11-24','yyyy/MM/dd') --yyyy/MM/dd
===============================================================================
|result|
|         c0         |      c1    |    c2    |     c3     |
| 2022-11-24 00:00:00| 2022-11-24 | 20221124 | 2022/11/24 |

【3】from_unixtime(bigint unixtime,string format)

作用：将 unix 时间戳转换成指定的格式

返回类型： >>> string

select  from_unixtime(1669277426) --yyyy-MM-dd HH:mm:ss
       ,from_unixtime(1669277426,'yyyy-MM-dd') --yyyy-MM-dd
       ,from_unixtime(1669277426,'yyyyMMdd') --yyyyMMdd
       ,from_unixtime(1669277426,'yyyy/MM/dd') --yyyy/MM/dd
       ,from_unixtime(cast(1669277426000/1000 as int))--unixtime为13位的，需先转成10位
===================================================================================
|result|
|         c0         |      c1    |    c2    |     c3     |        c4         |
| 2022-11-24 08:10:26| 2022-11-24 | 20221124 | 2022/11/24 |2022-11-24 08:10:26|

【4】unix_timestamp(date string timestamp,string pattern)

作用：将时间转换成当前时区unix时间戳

注：第一个参数是字符串的时间，第二个 pattern 匹配第一个参数时间字符串格式，识别时间

只传入第一个参数，格式必须是‘yyyy-mm-dd HH:mm:ss’ ，否则返回null

返回类型： >>> bigint

select  unix_timestamp('2022-11-24 08:10:26')--pattern默认是yyyy-mm-dd HH:mm:ss
       ,unix_timestamp('2022-11-24','yyyy-MM-dd') --yyyy-MM-dd
       ,unix_timestamp('2022-11-24 08:10:26','yyyy-MM-dd HH:mm:ss')
       ,unix_timestamp('2022-11-24','yyyyMMdd')--yyyyMMdd
=================================================================================
|result|
|     c0     |     c1     |     c2     |     c3     |
| 1669277426 | 1669248000 | 1669277426 | 1635724800 |

【5】其余格式的指定转换 regexp_replace()、from_unixtime(unix_timestamp())

select  regexp_replace('2022-11-24','-','/')--将-替换成/ yyyy/MM/dd
       ,regexp_replace('2022/11/24','/','')--yyyyMMdd
       ,from_unixtime(unix_timestamp('20221124','yyyyMMdd'),'yyyy-MM-dd')--yyyy-MM-dd
       ,from_unixtime(unix_timestamp('2022/11/24','yyyy/MM/dd'),'yyyy-MM-dd')--yyyy-MM-dd
=============================================================================================
|result|
|     c0     |    c1    |     c2     |     c3     |
| 2022/11/24 | 20221124 | 2022-11-24 | 2022-11-24 |

【6】截取和拼接 substr()、concat()

select  concat(substr('20221124',1,4),'-',substr('20221124',5,2))
       ,substr('2022-11-24 08:10:26',1,10)
====================================================================
|result|
|    c0   |     c1     |    
| 2022-11 | 2022-11-24 |

四、计算时间函数

【1】日期加减 date_add(date,int)、add_month(date string timestamp,int)

返回类型： >>> date

select  date_add('2022-11-24',1)--必须满足yyyy-MM-dd格式 
       ,date_add('2022-11-24',-1)--等同于date_sub
       ,add_months('2022-11-24 08:10:26',1)
       ,add_months('2022-11-24',-1)
=============================================================
|result|
|     c0     |     c1     |     c2     |     c3     |
| 2022/11/25 | 2022-11-23 | 2022-12-24 | 2022-10-24 |

【2】日期差值 months_between()、datediff()、

注：日期是前减后

select  months_between('2022-11-24','2022-10-24')--日期相同，比较月差值
       ,months_between('2022-11-24','2022-10-25')--日期不同，比较月差值、小数
       ,months_between(concat(substr('2022-11-24',1,7),'-01'),concat(substr('2022-10-27',1,7),'-01'))--日期不同，比较月差值，整数
==================================================================================================================================
|result|
| c0 |     c1     | c2  |
|  1 | 0.96774194 |  1  |
------------------------------------------------------------------------------------------------------------------------------------
select  datediff(current_timestamp(),'2022-10-24')
       ,datediff(current_date() ,'2022-10-25')
       ,datediff('2022-11-24','2022-11-25')
========================================================
|result|
|  c0 | c1 |  c2  |
|  32 | 31 |  -1  |