Spark日期函数及应用
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() | ||
生成今天的日期 | 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 | 当前月内第几个自然周。 | date_format('2021-08-22','EEEE') 返回 Sunday； |
更多的参数和用法请参考官方文档：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，得到一年内所有月的第二个周二和周三的日期。
【最终效果】
