观远 BI
登录
进入工单系统

Spark日期函数及应用

创建于 2022-11-01 / 最近更新于 2022-11-16 / 10453
字体： [默认] [大] [更大]

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)

【最终效果】

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/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，得到一年内所有月的第二个周二和周三的日期。

【最终效果】

image.png


32 人点赞过