Spark日期函数及应用 - 观远 BI - 观远数据帮助中心

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

1. 时间或日期截取函数

原字段为日期(date)或日期时间(datetime/timestamp)

返回非日期

用途	函数	举例	结果
取年份	year()	year('2009-07-30')	2009
取季度数	quarter()	quarter('2021-08-18')	3
取月份	month()	month('2009-07-30')	7
按月取天（1-31）	dayofmonth()	dayofmonth('2009-07-30'); dayofmonth('2020-07-30 13:39:37')	30
按年取天（1-365）	dayofyear()	dayofyear('2022-02-01')	32
取星期	dayofweek()星期日为1; weekday() 星期一为 0	dayofweek('2021-08-18')-1; weekday('2021-08-18')+1	3
取小时	hour()	hour('2018-12-11 11:12:13')	11
取分钟	minute()	minute('2018-12-11 11:12:13')	12
取秒	second()	second('2018-12-11 11:12:13')	13
取周数	weekofyear()	weekofyear('2008-02-20')	8
取时分秒	date_format(ts,'HH:mm:ss')	date_format('2018-12-11 11:12:13','HH:mm:ss')	11:12:13

返回日期或时间

date_trunc(format, [字段])  --按照目标格式截取日期或日期时间后返回timestamp格式。
trunc([字段], format)       --按照目标格式截取日期或日期时间后返回date格式。

可用格式format:

Formatter	含义
"YEAR","YYYY","YY"	年
"QUARTER"	季度
"MONTH","MM","MON"	月
"WEEK"	周
"DAY","DD"	天
"HOUR"	小时
"MINUTE"	分钟
"SECOND"	秒
"MILLISECOND"	毫秒
"MICROSECOND"	微秒

注意:

1）不区分大小写;

2）引号必须是半角符号,单引号双引号都可以用;

3）天('day'/'dd')及以下单位（时分秒）不能用于 trunc()。

用途	函数	举例	结果
取所在周的周一	trunc(date,'week')	trunc('2021-07-30', 'week')	2021-07-26
取所在周的周一	date_trunc('week',ts)	date_trunc('week', '2021-07-30 15:48:08')	2021-07-26 00:00:00
取所在月第一天	trunc(date,'MM')	trunc('2021-07-30', 'MM')	2021-07-01
取所在月第一天	date_trunc('MM',ts)	date_trunc('MM', '2021-07-30 15:48:08')	2021-07-01 00:00:00
取所在月最后一天	last_day(date)	last_day('2021-07-30 15:48:08')	2021-07-31
取所在季第一天	trunc(date,'quarter')	trunc('2021-07-30', 'quarter')	2021-07-01
取所在季第一天	date_trunc('quarter', ts)	date_trunc('quarter', '2021-07-30 15:48:08')	2021-07-01 00:00:00
取所在年第一天	trunc(date,'year')	trunc('2021-07-30','year')	2021-01-01
取所在年第一天	date_trunc('yyyy', ts)	date_trunc('yyyy', '2021-07-30 15:48:08')	2021-01-01 00:00:00
截取时间日期到天（之后归零）	date_trunc('day', ts)	date_trunc('day', '2021-07-30 15:48:08')	2021-07-30 00:00:00
截取时间日期到小时（之后归零）	date_trunc('hour', ts)	date_trunc('hour', '2021-07-30 15:48:08')	2021-07-30 15:00:00
截取时间日期到分钟（之后归零）	date_trunc('minute', ts)	date_trunc('minute', '2021-07-30 15:48:08')	2021-07-30 15:48:00

2. 日期或时间日期生成函数

用途	函数	结果
生成当前的时间	now()	2021-08-21 14:43:09
生成当前的时间	current_timestamp()	2021-08-21 14:43:09
生成今天的日期	current_date()	2021-08-21
生成当前时间戳	unix_timestamp()	1629528189

3. 日期时间计算

用途	函数	举例	结果
增减日期时间	[字段] +/- INTERVAL 1 YEAR/MONTH/WEEK/DAY/HOUR/MINUTE (中间数值不能引用其他字段)	'2021-07-30 15:48:08' - interval 1 year	2020-07-30 15:48:08
增减日期时间		'2021-07-30 15:48:08' + interval 2 hour	2021-07-30 17:48:08
计算未来日期 (时间部分不保留)	date_add([字段],数值)	date_add('2021-07-30 17:48:08',3)	2021-08-02
计算未来日期 (时间部分不保留)	add_months([字段],数值) 数值为正值	add_months('2021-07-30',1)	2021-08-30
计算过去日期 (时间部分不保留)	date_sub([字段],数值)	date_sub('2021-07-30 17:48:08',3)	2021-07-27
计算过去日期 (时间部分不保留)	add_months([字段],数值) 数值为负值	add_months('2021-07-30',-1)	2021-06-30
计算日期差	datediff(endDate, startDate) 结果为整数	datediff('2021-07-30', '2021-07-31')	-1
计算月份差	months_between(endTime, startTime) 结果为浮点数	months_between('2021-07-07 14:14:01', '2021-04-22 15:57:59')	2.5138
计算分钟差	自定义函数: MINUTEDIFF()*	MINUTEDIFF('2021-07-02 14:28:53', '2021-07-02 12:00:16')	148
计算秒差	自定义函数: SECONDDIFF()*	SECONDDIFF('2021-07-02 14:28:53', '2021-07-02 12:00:16')	8917

案例一: 时长计算

【需求】 数据集里一个字段为事件开始时间, 另一个字段为事件持续时间（分钟）, 不同事件持续时间不同, 需要计算事件结束时间。此时用 [字段]+ INTERVAL 1 MINUTE 这种方法不能计算出结束时间。

【逻辑】 unix_timestamp是把时间戳 timestamp 转换成一个以秒为单位计算得到的数值，可以方便地对时间戳进行数学计算。最后再把计算结果格式转换回 timestamp。反之，计算两个时间的时间差也可以用同样方式。

【实现】

timestamp(unix_timestamp([开始时间])+[持续时间]*60)；
to_timestamp(to_unix_timestamp([开始时间])+[持续时间]*60)

【最终效果】

4. 日期与时间日期转化

用途	函数	举例	结果
将字符型日期转化为日期型	to_date(date_str[, fmt]) 已经是标准格式时[, fmt]可省略，此时可与 date(date_str) 和cast(date_str as date) 通用	to_date('2009-07-30 04:17:52')	2009-07-30
		date('2009-07-30 04:17:52')
		cast('2009-07-30 04:17:52' as date)
将字符型时间日期转化为时间日期型	to_timestamp(ts_str[, fmt]) 已经是标准格式时[, fmt]可省略，此时可与 timestamp(ts_str) 和cast(ts_str as timestamp) 通用	to_timestamp('2016-12-31', 'yyyy-MM-dd')	2016-12-31 00:00:00
		timestamp('2016-12-31')
		cast('2016-12-31' as timestamp)
将数值型、日期型等格式转化为字符型	string(expr)	string('2021-07-30 15:48:08')	2021-07-30 15:48:08
	CAST([字段] AS string)	cast('2021-07-30 15:48:08' as string)
日期时间格式转化，结果一般为字符型	date_format(timestamp, fmt)	date_format(now(), 'HH:mm:ss')	22:04:50
将日期时间或者字符型日期时间转换为时间戳	to_unix_timestamp([timeExp[, fmt]])	to_unix_timestamp('2021/7/30 01:30 PM','yyyy/M/dd hh:mm a')	1627623000
	unix_timestamp([timeExp[, fmt]]) 日期时间为标准格式时[, fmt] 可省略	unix_timestamp('2021-07-30 13:30:00')	1627623000
时间戳转为日期时间	from_unixtime(unix_time[, fmt]) （已经自动转换为当前时区时间）	from_unixtime(1)	1970-01-01 08:00:01
时区偏移转换	from_utc_timestamp(timestamp, timezone) 用世界标准时间推算其他时区时间	from_utc_timestamp('2021-08-08', 'Asia/Shanghai')	2021-08-08 08:00:00
		from_utc_timestamp('2021-08-08 00:00:00', 'GMT+8')
	to_utc_timestamp(timestamp, timezone) 用所给时区时间去推算世界标准时间	to_utc_timestamp('2021-08-08 00:00:00', 'Asia/Shanghai'); to_utc_timestamp('2021-08-08 00:00:00', 'GMT+8')	2021-08-07 16:00:00

常用日期时间格式化的参数 DateFormatter（注意区分 大小写 ）：

符号	含义	举例
yy/yyyy	yy: 不包含纪元的年份; yyyy: 包括纪元的四位数的年份。	21; 2021
M/MM	月份数字。M:一位数的月份没有前导零; MM:一位数的月份有一个前导零。	1; 01
MMM/MMMM	MMM:月份的缩写名称; MMMM:月份的完整名称	Jan; January
d/dd	月中的某一天。d:一位数的日期没有前导零; dd:一位数的日期有一个前导零。	01~31
D/DD	年中的某一天。D: 一位数的日期没有前导零; DD:一位数的日期有一个前导零。	01~365
h/hh	12小时制的小时。h: 一位数的小时数没有前导零; hh: 一位数的小时数有前导零。	1~12
H/HH	24小时制的小时。H: 一位数的小时数没有前导零; HH: 一位数的小时数有前导零	0~23
m/mm	分钟。m:一位数的分钟数没有前导零; mm:一位数的分钟数有一个前导零。	0~59
s/ss	秒。s:一位数的秒数没有前导零; ss:一位数的秒数有前导零。	0~60
S	秒的分数小数，用于秒的更精细统计和显示。	978
E/EEEE	1至 3 个 E：周中某天星期的缩写名称；4 个 E：周中某天星期的全称	Tue; Tuesday
a	AM-PM	AM；PM
z/zzzz	当前时区名字。 z: 时区缩写；zzzz：时区名字全称	CST；China Standard Time
Z	当前时区偏移	+0800
X	当前时区偏移。X: +08; XX: +0800; XXX: +08:00	+08:00
F	当前月内第几个自然周。从1号开始每7天为一个自然周。	date_format('2021-08-22','EEEE') 返回 Sunday； date_format('2021-08-22','F') 返回 4，表示 2021-08-22这天是 2021 年 8 月的第 4 个周日。

更多的参数和用法请参考官方文档： https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html

案例二：时区转换

【需求】 原来时间为东八区时间，需要转换为东九区时间, 偏移1个时区。

from_utc_timestamp([东八区时间],'UTC+1'); from_utc_timestamp([东八区时间],'GMT+1')

【逻辑】 把当前的东八区时间暂时当做世界标准时间，计算偏移1个时区的时间

【最终效果】 转换前：2021-08-08 15:16:00 转换后：2021-08-08 16:16:00

案例三：文本日期转换为标准日期

文本日期	标准格式	函数
07/30/2021	2021-07-30	to_date([文本日期],'MM/dd/yyyy')
2021/7/30 13:30:00	2021-07-30 13:30:00	to_timestamp([文本日期],'yyyy/M/dd HH:mm:ss')
2021/7/30 01:30 PM	2021-07-30 13:30:00	to_timestamp([文本日期],'yyyy/M/dd hh:mm a')
2021年7月30日	2021-07-30	to_date([文本日期],'yyyy年M月dd日')
2021-07-30T16:00:00.000Z	2021-07-31 08:00:00	from_utc_timestamp([文本日期],'GMT+8')
2021-07-30T17:25:53+00:00	2021-07-31 09:25:53	from_utc_timestamp([文本日期],'GMT+8')
July 30, 2021	2021-07-30	to_date([文本日期],'MMMM dd, yyyy')
Aug 8, 2021	2021-08-08	to_date([文本日期],'MMM d, yyyy')
20210808121600	2021-08-08 12:16:00	to_timestamp([文本日期],'yyyyMMddHHmmss')

案例四：标准日期转换为文本日期

日期	目标格式（文本）	函数
2021-08-08 15:16:00	2021-08	substr(string([日期]),1,7)
	202108 （数值）	YEAR([日期])*100+MONTH([日期])
	08-08	substr(string([日期]),6,5)
	15:16	date_format([日期], 'HH:mm')
	03:16 PM	date_format([日期], 'hh:mm a')
	Aug 8, 2021	date_format([日期],'MMM d, yyyy')
	Sunday	date_format([日期],'EEEE')
	2021年8月8日	date_format([日期],'yyyy年M月d日')
	2021-08-08 15:16:00 (+08:00)	date_format([日期],'yyyy-MM-dd HH:mm:ss (XXX)')

案例五：取每月第二个周二和周三的日期

【需求】 从一张日期表里，找出每个月的第二个周二和周三的日期（观远学堂月度产品培训日）

【逻辑】

1. 新建计算字段“周天”得到每天是星期几： dayofweek([日期]) -1

2. 新建计算字段“周数”得到日期在每个月的第几个自然周内： date_format([日期],'F')

3. 筛选“周天”的范围为：大于等于2小于等于3，或者直接筛选2和3；

4. 筛选“周数”为2，得到一年内所有月的第二个周二和周三的日期。

【最终效果】