hive sql中常用时间处理函数(还有很多慢慢相遇)

时间处理函数

将时间格式的数据由一种格式转化为另一种格式

SELECT		from_unixtime(
					cast(
					unix_timestamp('2022-11-27 21:18:39.0', 'yyyy-MM-dd HH:mm:ss') 
					as bigint )
					,'yyyyMMdd') ; 
					
--转化前的日期格式: 2022-11-27 21:18:39.0

-- 转化后的日期格式: 20221127

将包含毫秒值的时间串转化为包含毫秒值的时间格式。例(1697099582434 -> 2023-10-12 16:33:02.434)

package com.*.bigdata.*.udf.collect
import java.text.SimpleDateFormat
import java.util.Date
import org.apache.spark.sql.api.java.UDF1
/**
  * @Description:将毫秒字符串转化为标准时间(含毫秒值)
  *               例如 (1697099582434 -> 2023-10-12 16:33:02.434)
  * @Author: xxx
  * @Date:2023 /10/12/15:56
  */
class TimestampToDate extends UDF1[String,String]{
//object TimestampToDate{
  // override
  override def call(info:String): String = {
     val longTime: Long = info.toLong
     val date: Date = new Date(longTime)
     val sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")
     val dateString: String = sdf.format(date)
    dateString
  }
//  def main(args: Array[String]): Unit = {
//    println(call("1696975074366"))
//  }
}

--注册udf函数(scala版本)
spark.udf.register("timeStampToDate",new TimestampToDate,DataTypes.StringType)
-- 调用udf函数(scala版本)
timeStampToDate(cast(sensor_user.`time` as string))

获取当月第一天
select 
  trunc('2020-08-07 00:00:00','MM'); 
  -- 输出结果:2020-08-01 00:00:00

获取当月最后一天
select 
  last_day('2020-08-07')  ; 
  -- 输出结果: 2020-08-31 00:00:00

from_unixtime(cast(substr(u_time,0,10) as bigint),‘yyyy-MM-dd HH:mm:ss’)

1694009894389 此时间戳为毫秒值,正常使用from_unixtime() 函数中传入的是秒
所以需要进行截取处理一下 substr(`u_time`,0,10)

select substr(‘2022-01-11 15:29:03’,1,10);

2022-01-11
-- 等价于 select substr('2022-01-11 15:29:03',0,10);

select to_date(‘2022-01-11 15:29:03’);

2022-01-11

select from_unixtime(1641886143 , ‘yyyy-MM-dd HH:mm:ss’);

2022-01-11 15:29:03

select from_unixtime(cast(‘1641886143’ AS INT),‘yyyy-MM-dd HH:mm:ss’) ;

2022-01-11 15:29:03   --时间戳为字符串时要转化为int 或者bigint 类型

将 23/Aug/2023:00:56:45 +0800 时间处理为正常日期格式

select 
    from_unixtime(unix_timestamp('23/Aug/2023:00:56:45 +0800','dd/MMM/yyy:HH:mm:ss')) ;
	-- 输出结果 2023-08-23 00:56:45

select from_utc_timestamp(‘2022-01-11 15:29:03’,‘GMT+8’);

2022-01-11 23:29:03     -- 时区问题 将时间加上八个小时
同理时区问题时间快于当前时间 13 小时
substr( from_utc_timestamp('2023-06-29 06:54:32','GMT-13') ,1,19)

date_diff(date1,date2) 计算两日期相差几日

select 
datediff(date_format('2023-06-03 17:27:04','yyyy-MM-dd'),date_format('2023-06-03 14:46:15','yyyy-MM-dd'))

当date2 > date1 时返回正数，date2 < date1 时返回负值，date2 = date1 时返回值为 0 
实操经验:date_diff()方法中的需要像例句一样格式化 'yyyy-MM-dd' ,
如果 'yyyyMMdd' 这样格式化日期那么该函数返回值为 null

拓展:窗口函数

select 
 id,
 dt,
 cns,
 -- 在一个窗口中,从最后一行开始依次相加直到当前行
 sum(cns) over (partition by sid order by day_time rows between current row and unbounded following) as cnt_sum1
 -- 在一个窗口内,从第一行开始依次相加直到当前行
 sum(cns) over (partition by sid order by day_time)   as cnt_sum2
from order_cnt