跳到主要内容
版本：6.0.0

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() 星期一为 0dayofweek('2021-08-18')-1; weekday('2021-08-18')+13
取小时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)

【最终效果】

image.png

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/yyyyyy: 不包含纪元的年份; yyyy: 包括纪元的四位数的年份。21; 2021
M/MM月份数字。M:一位数的月份没有前导零; MM:一位数的月份有一个前导零。1; 01
MMM/MMMMMMM:月份的缩写名称; MMMM:月份的完整名称Jan; January
d/dd月中的某一天。d:一位数的日期没有前导零; dd:一位数的日期有一个前导零。01~31
D/DD年中的某一天。D: 一位数的日期没有前导零; DD:一位数的日期有一个前导零。01~365
h/hh12小时制的小时。h: 一位数的小时数没有前导零; hh: 一位数的小时数有前导零。1~12
H/HH24小时制的小时。H: 一位数的小时数没有前导零; HH: 一位数的小时数有前导零0~23
m/mm分钟。m:一位数的分钟数没有前导零; mm:一位数的分钟数有一个前导零。0~59
s/ss秒。s:一位数的秒数没有前导零; ss:一位数的秒数有前导零。0~60
S秒的分数小数，用于秒的更精细统计和显示。978
E/EEEE1至 3 个 E：周中某天星期的缩写名称；4 个 E：周中某天星期的全称Tue; Tuesday
aAM-PMAM；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/20212021-07-30to_date([文本日期],'MM/dd/yyyy')
2021/7/30 13:30:002021-07-30 13:30:00to_timestamp([文本日期],'yyyy/M/dd HH:mm:ss')
2021/7/30 01:30 PM2021-07-30 13:30:00to_timestamp([文本日期],'yyyy/M/dd hh:mm a')
2021年7月30日2021-07-30to_date([文本日期],'yyyy年M月dd日')
2021-07-30T16:00:00.000Z2021-07-31 08:00:00from_utc_timestamp([文本日期],'GMT+8')
2021-07-30T17:25:53+00:002021-07-31 09:25:53from_utc_timestamp([文本日期],'GMT+8')
July 30, 20212021-07-30to_date([文本日期],'MMMM dd, yyyy')
Aug 8, 20212021-08-08to_date([文本日期],'MMM d, yyyy')
202108081216002021-08-08 12:16:00to_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，得到一年内所有月的第二个周二和周三的日期。

【最终效果】

image.png