观远 BI

Spark日期函数及应用

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

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


30 人点赞过