时间处理函数
将时间格式的数据由一种格式转化为另一种格式
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