SPL 的日期时间函数

在数据分析计算中,日期时间类数据是比较特殊的类型,这里我们就来研究一下如何在SPL中使用日期时间类数据。

1日期时间数据的转换和生成

日期时间类数据,通常会用字符串来输入或显示。在使用集算器时,可以点击Tool>Option,在选项配置的Environment页面中,设定日期时间类型数据所使用的默认格式,如:

使用SPL时,日期时间类型的数据会按照默认的格式显示出来,如:


A
1 =now()

运行后查看A1中的结果如下:

这里用到的函数now() 是日期时间计算中比较常用的函数,可以获得系统中当前的日期时间。日期时间的显示在不同的语言环境中是不同的,特别是月份和星期等数据,下面都将以英文版的格式为例加以说明。

当需要输入日期时间类型的常数时,也可以直接按照格式输入字符串,此时SPL会自动将数据解析为日期时间数据,如:


A B C
1 2019-2-1 12:45:30 2019-02-01 10:30:00

A1,B1C1会分别被解析为日期类型、日期时间类型和时间类型数据,如下:

SPL可以把直接输入的常数解析为日期时间类型,但对于已经是字符串类型的,就需要用date(),time() 或者datetime() 将字符串转换为日期、时间或者日期时间数据,如:


A

B

C

1

2019

2

20

2

=A1/"-"/B1/"-"/C1

=12/":"/22/":00"

=A2+" "+B2

3

=ifdate(A2)

=iftime(B2)

=ifdate(C2)

4

=date(A2)

=time(B2)

=datetime(C2)

5

=ifdate(A4)

=iftime(B4)

=ifdate(C4)

A2,B2C2中是通过字符串运算得到的字符串:

3行用ifdate() iftime() 函数判断第2行的数据是否已被处理为日期或者时间类型(注意:判断日期类型和日期时间类型的数据,都使用ifdate()A3,B3C3中的结果如下:

从结果可以看出,第2行中单元格值都不是日期或时间类数据,实际上都是字符串,显示也都和日期时间不同。

在第4行中,将字符串按照对应的格式分别转换为日期时间数据,结果如下:

在第5行判断第4行的数据是否已被处理为日期或者时间类型,结果如下:

 

当使用外部数据时,有时需要处理不同格式的日期时间数据,这时可以在使用date(),time() 或者datetime() 时,在字符串后添加使用的显示格式串,如:


A

B

C

1

Feb 2, 2019

'2:30:45 PM

'2019-6-20 2:30:45 PM

2

MMM d,yyyy

hⓂs a

yyyy-M-d hⓂs a

3

=date(A1,A2)

=time(B1,B2)

=datetime(C1,C2)

在第1行中的数据都并没有用默认的日期时间格式,而且B1C1都在前面添加了 ' 字符表示使用字符串常数,A1,B1C1中的数据如下:

需要注意的是,月份格式MMM的设定是和语言环境相关的,英文环境中表示月份的英文缩写,如Feb;而中文环境中则会显示为中文月份。

2行中列出的格式串指示SPL如何解析第1行中的数据,第3行使用这些格式串进行转换,结果如下:

完成类型转换后,查看结果时仍然会用默认的格式显示。如果要用其他格式显示,可以用string(d,fmt)函数,将日期时间类数据转换为指定格式的字符串,如:


A

B

C

1

2019-02-21

12:45:30

2019-12-01 10:30:00

2

MMMM d,yyyy

hⓂs a

MMM d,yyyy hⓂs a

3

=string(A1,A2)

=string(B1,B2)

=string(C1,C2)

A3,B3C3中,将日期时间类数据转换为指定格式的字符串:

当然,也可以根据需要直接修改默认的日期时间显示格式。

 

在用date(),time() 或者datetime() 生成数据类型时,还可以直接依次指定年,,,,,秒等各个分量:


A

B

C

1

=date(2019,2,21)

=time(13,5,0)

=datetime(2019,12,29,13,5,0)

结果如下:

在指定各个分量时,要注意每个整数的合理范围,例如小时分量在0~23之间。

2日期时间数据的显示格式


A

B

C

1

=date(201906,29)

=time(13:5:18)

=datetime(A1,B1)

这个例子中,A1中用date()生成日期,第一个参数使用了6位数,SPL将用其同时表示年和月。而C1则将A1中的日期和B1中的时间合并得到日期时间数据,结果如下:

上一节已经使用到了日期时间数据的一些显示格式,用格式字符串来指定显示样式,例如yyyy表示4位数的年份、dd表示2位数的日期等。下面将具体说明格式串中各个字符的作用,并以C1中的数据为例显示相应的格式化结果:

字符 作用 SPL 结果

y/yy

年,两位数字

=string(C1,"yy")

yyyy

年,四位数字

=string(C1,"yyyy")

M

=string(C1,"M")

MM

月,两位数字,不足用0补齐

=string(C1,"MM")

MMM

月,英文简写

=string(C1,"MMM")

MMMM

月,英文全拼

=string(C1,"MMMM")

d

=string(C1,"d")

dd

日,两位数字,不足用0补齐

=string(C1,"dd")

E

星期,英文简写

=string(C1,"E")

EEEE

星期,英文全拼

=string(C1,"EEEE")

G

Era标识符,公元前/公元后,缩写

=string(C1,"G")

w

本年的第几周

=string(C1,"w")

ww

本年的第几周,两位数字,不足用0补齐

=string(C1,"ww")

W

本月的第几周

=string(C1,"W")

F

在本月的第几周,仅根据日计算

=string(C1,"F")

D

本年的第几天

=string(C1,"D")

H

小时,24小时制,0~23

=string(C1,"H")

HH

小时,24小时制,0~23,两位数字,不足用0补齐

=string(C1,"HH")

k

小时,24小时制,1~24

=string(C1,"k")

kk

小时,24小时制,1~24,两位数字,不足用0补齐

=string(C1,"kk")

h

小时,12小时制,1~12

=string(C1,"h")

hh

小时,12小时制,1~12,两位数字,不足用0补齐

=string(C1,"hh")

K

小时,12小时制,0~11

=string(C1,"K")

KK

小时,12小时制,0~11,两位数字,不足用0补齐

=string(C1,"KK")

m

分钟

=string(C1,"m")

mm

分钟,两位数字,不足用0补齐

=string(C1,"mm")

s

=string(C1,"s")

ss

秒,两位数字,不足用0补齐

=string(C1,"ss")

S

毫秒

=string(C1,"S")

a

上午/下午

=string(C1,"a")

z

时区,缩写

=string(C1,"z")

zzzz

时区,全拼

=string(C1,"zzzz")

Z

时区代码

=string(C1,"Z")

 

3从日期时间数据中获取信息

在日期时间等类型的数据中,具体的年,,,,,秒等常常是处理时需要单独使用的信息,我们可以通过year(),month(),day(),hour(),minute(),second(),millisecond()等函数获得这些日期时间数据中的各个分量:


A

B

C

1

2019-6-30

12:45:30.230

=now()

2

=year(A1)

=month(A1)

=day(A1)

3

=hour(B1)

=minute(B1)

=second(B1)

4

=month(C1)

=hour(C1)

=millisecond(C1)

A1,B1C1中的日期时间数据如下:

A2,B2C2从日期数据中分别获取年、月、日:

A3,B3C3从时间数据中获取时、分、秒:

A4,B4C4now() 函数的日期时间类型结果中获取月,小时和毫秒分量:

从结果中可以看到,now() 函数返回的结果会精确到毫秒,但显示时只会显示到秒。在使用now() 函数时,可以通过添加选项的方法来改变结果的精度。如:


A

B

C

1

=now@d()

=now@t()


2

=now@m()

=now@s()

=millisecond(B2)

A1中添加@d选项,只取日期部分数据,B1中添加@t选项,只取时间部分数据,结果如下:

A2中添加@m选项,数据将精确到分,B2中添加@s选项,获得的数据将精确到秒,结果如下:

C2中可以看到,B2中的数据毫秒分量为0

@m@s选项,也可以用在datetime() time() 函数中,设定转换日期时间数据及时间数据时的精度为分或秒。

 

在日期类型数据中,也可以获取到时间分量,从时间类型数据中也可以获取日期分量,如:


A

B

C

1

2019-6-30

19:05:10.866


2

=hour(A1)

=minute(A1)

=second(A1)

3

=month@y(B1)

=day(B1)


A2,B2C2中结果如下:

也就是说,单独的日期数据,其中的时间指定为00:00:00

A3中的month函数添加了@y选项,获得年月组成的6位数,A3B3中结果如下:

也就是说,单独的时间数据,其中的日期为197011日。

 

除了直接从日期时间类型的数据中获取各个分量,还有一些函数可以用来获得日期相关的数据。

 

使用day@w(),在获取日期分量时添加@w选项,可以获得本日是一周中的第几天:


A

B

C

1

2019-4-3

2019-6-18

2020-2-20

2

=day@w(A1)

=day@w(B1)

=day@w(C1)

3

=string(A1,"EEEE")

=string(B1,"EEEE")

=string(C1,"EEEE")

A2,B2C2获取各个日期分别是一周中的第几天,结果如下:

SPL中,每周的第1天是从周日开始计算的,在第3行中用显示字符串的方式取得了每一天是星期几:

 

另外,我们还可以使用pdate() 函数,配合不同的选项获得日期


A

B

C

1

2019-8-17



2

=pdate@w(A1)

=pdate@m(A1)

=pdate@q(A1)

3

=pdate@we(A1)

=pdate@me(A1)

=pdate@qe(A1)

pdate() 函数中:

   直接使用@w选项可以获得本周第1天的日期,从周日开始计算;

   添加@m选项可以获得本月第1天的日期;

   添加@q选项可以获得本季度第1天的日期;

   添加@e则可以获得某个时间段最后一天的数据,如本周最后一天,本季度最后一天等。A2,B2,C2,A3,B3C3中的结果依次如下:

 

SPL中还可以使用days() 函数计算某日期所在月的总天数,添加@q选项可以得到所在季度的总天数,添加@y选项可以得到整年的总天数,如:


A

B

C

1

2019-02-21



2

=days(A1)

=days@q(A1)

=days@y(A1)

A2,B2C2中的结果如下:

4使用日期时间数据的计算

除了直接从日期时间数据中获取信息,在SPL中还可以使用日期时间类数据来执行各类计算。

最常用的有关日期的计算就是计算年龄:


A

B

C

1

1995-3-30

=now@d()


2

=age(A1)

=age@m(A1)

=age@y(A1)

A1B1中的数据如下:

在第2行用age() 函数,根据A1中的生日来计算年龄,计算年龄时,是以当前的日期为准的,默认情况将精确到日,添加@m可以将精度设为月,添加@y可以将精度设定为年。在不同精度的情况下,计算所得的年龄可能会有区别,A2,B2C2中得到的年龄分别如下:

 

使用age() 函数,类似于计算生日日期和当前日期间隔的年数。更普遍的计算时间间隔的函数是interval() 函数,用这个函数可以计算两个日期时间数据之间相差多少天,添加@y,@q,@m,@s,@ms等选项,可以计算间隔多少年,季度,,秒或毫秒。例如:


A

B

C

1

1995-4-30

2019-4-10


2

=interval(A1,B1)

=interval@y(A1,B1)

=B1-A1

如果只需要计算两个日期之间相差多少天,也可以直接用减法完成,A2,B2C2中的结果如下:

特别的,每个日期时间数据都可以转换为一个对应的长整数,这个长整数其实就是指定的日期时间和197011日,格林威治时间0:00:00的间隔毫秒数,如:


A

B

1

=datetime("1/1/1970 0:00:00   GMT","m/d/yyyy H🇲🇲ss z")

1995-4-30

2

=interval@ms(A1,B1)

=long(B1)

而在B2中,用long() 函数直接将日期时间类数据转换为长整数,可以看到A2B2中的结果是相同的:

 

SPL中的elapse(t,k) 函数,可以根据已有的日期时间t,计算k天后的结果。添加选项@y,可以计算k年后的日期时间。类似的,还可以添加@q,@m,@s,@ms等选项,将间隔单位设定为季度,月,秒或毫秒。如:


A

B

C

1

1995-4-30



2

=elapse(A1,10)

=elapse@y(A1,20)

=elapse@m(A1,-1)

A2,B2C2中分别计算10天后,20年后和1个月前的日期,结果如下:

如果只是需要计算相差若干天的日期,也可以直接简写为加减法,如=A1+10=A1-10等。

由于每个月的天数不同,在计算k个月后的日期时,默认情况下还会考虑当前日期是否是该月的最后一天,并进行对应的调整,如果不需要这样的调整,则需要添加@e选项,如:


A

B

C

1

2020-2-29

=elapse@m(A1,3)

=elapse@me(A1,3)

B1C1中的结果如下:

可以看到,由于2020229日是2月的最后一天,B1中计算3个月后的日期时,同样得到了5月的最后一天,而C1中添加了@e选项,只计算3个月后的日期,而不做调整。

 

由于日期时间数据比较特殊,很多情况下并不方便直接判断是否相等。为此,可以在SPL中使用deq() 函数,只要两个数据在同一天,即认为相等。也可以添加选项@y,@q,@m@t@w等,将精度设为精确到年,季度,月,旬或周。如:


A

B

C

1

2019-2-15 12:33:00

2019-2-15 18:45:20

2019-2-28 10:05:00

2

=deq(A1,B1)

=deq(A1,C1)

=deq@m(A1,C1)

A2,B2C2中的判断结果如下:

 

在使用日期时间数据时,还有一类计算是有关工作日的计算,用workday(t,k,h) 函数可以计算指定日期时间tk个工作日的日期时间,用workdays(b,e,h) 可以计算开始日期b和结束日期e之间的工作日序列。有关工作日的计算比较复杂,正常的工作日是每周的周一到周五,但是有时会受公假的影响,周一到周五的某天是休息日,或者周末的某一天需要正常上班,这样的日期调整可以设置到序列h中,序列中的非周末日期是增加的假期,序列中的周末日期则是需要加班的工作日。如:


A

B

C

1

2019-4-30

2019-4-27

2019-5-5

2

[2019-5-1, 2019-5-2, 2019-5-3,   2019-4-28, 2019-5-5]



3

=workday(A1,2,A2)

=workdays(B1,C1)

=workdays(B1,C1,A2)

A2中设定了2019年五一劳动节的公共假期调休情况:51日至53日的原工作日放假,而428日及55日的原周末调整为工作日。A3中计算结果如下:

注意201951日至3日为劳动节假期,54日为周末,而55日的原周末调整为工作日,因此2019430日后的第2个工作日是201956日。

B3C3中的结果如下:

可以看到,在未指定调整日期序列的情况下,只会显示周一到周五的日期;而A3中返回的结果才是正确的工作日。

 

workdays() 可以得到工作日序列,更通用的计算日期时间序列的函数是periods(s,e,i),用来计算开始日期时间b和结束日期时间e之间的日期时间序列,间隔为i天。通过添加函数选项@y,@q,@m,@t,@s,可以调整时间间隔的单位为年,季度,月,旬或秒。如果不希望设定的结束值在结果中出现,则可以添加@x选项。在时间序列中,出现的每个数据都会调整为指定时间段的第1天,如每月第1天,每年第1天等,如果不需要这种调整,可以添加@o选项。如:


A

B

C

1

2019-1-14

2019-1-20

2019-4-1

2

=periods(A1,B1)

=periods@m(B1,C1)


3

=periods@xm(B1,C1)

=periods@om(B1,C1)

=periods@oxm(B1,C1)

A2,B2中结果如下:

其中,A2中间隔单位为日,B2中间隔单位为月。

A3中设定去除结束日期,B3中设置不调整日期到月初,C3中同时设定@o@x选项,结果如下:

 

有时需要将一段时间等分,此时可以使用range() 函数,如:


A

B

C

1

2019-1-1

2020-1-1


2

=range(A1,B1,1:4)

=range(A1,B1,3:4)

=range(A1,B1,4)

A2中,将201911日至202011日平均分为4段,取出第1段的起止日期;B2中取出第3段的起止日期。C2中将获取平均分为4段时,包括起止时间的每个间隔日期。A2,B2C2中结果如下:

使用range函数时,如果前两个参数为日期时间,则分段时将精确到日;如果前两个参数为日期时间,则将精确到秒。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾