Spark SQL中日期和时间函数

Posted on Edited on In Views:

数据类型

日期时间类型

DateType

TimestampType

基础数据类型

参考类org.apache.spark.sql.catalyst.expressions.Cast中的castToTimestamp()和castToDate()

字符串类型

数值类型

时间间隔类型

YearMonthIntervalType

DayTimeIntervalType

时区

可以通过以下三种方式来设置时区参数：

  • 区域ID所表示的时区：区域ID的格式必须是：area/city，例如，America/Los_Angeles、Asia/Shanghai。
  • 时区偏移量：时区偏移量的格式必须是：(+|-)HH、 (+|-)HH:mm、(+|-)HH:mm:ss；例如-08、 +01:00、-13:33:33。
  • 也可以使用短名称UTC来表示+00:00，其他短名称不建议使用。

查看时区：

1
2
sql> select current_timezone();
sql> SET spark.sql.session.timeZone;

设置时区

TIMEZONE参数

1
2
3
4
5
-- 使用区域ID设置时区
sql> SET timezone = America/Los_Angeles;

-- 使用时区偏移量设置时区
sql> SET timezone = +08:00;

TIME ZONE关键字

1
2
3
4
5
6
7
8
9
10
11
12
-- 设置为系统默认时区
sql> SET TIME ZONE LOCAL;

-- 使用区域ID设置时区
sql> SET TIME ZONE 'America/Los_Angeles';

-- 使用时区偏移量设置时区
sql> SET TIME ZONE '+08:00';

-- 使用间隔量设置时区
sql> SET TIME ZONE INTERVAL 1 HOUR 30 MINUTES;
sql> SET TIME ZONE INTERVAL '08:30:00' HOUR TO SECOND;

spark.sql.session.timeZone参数

1
2
3
4
5
-- 使用区域ID设置时区
sql> SET spark.sql.session.timeZone = America/Los_Angeles;

-- 使用时区偏移量设置时区
sql> SET spark.sql.session.timeZone = +08:00;

函数

转换为UTC时间

unix_timestamp

函数 unix_timestamp(timeExp, String fmt)
返回值类型 bigint
描述 将当前系统时区表示的时间转换为UTC秒数
参数 timeExp：可选参数，Spark中该参数可以是date、timestamp、string类型，Hive中该参数是string类型。
fmt：可选参数，当timeExp不是string类型时，会忽略该参数。默认值为yyyy-MM-dd HH:mm:ss。参考日期时间格式

示例1：

1
sql> SELECT unix_timestamp();

示例2：入参格式串没有时区信息，默认使用系统时区

1
2
3
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT unix_timestamp('1970-01-01 08:00:00', 'yyyy-MM-dd HH:mm:ss');
0

示例3：入参格式串包含明确的时区信息，则使用入参中的时区

1
2
3
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT unix_timestamp('1970-01-01T08:00:00.000+0800', "yyyy-MM-dd'T'HH:mm:ss.SSSZ");
0

示例4：入参格式串包含明确的时区信息，则使用入参中的时区

1
2
3
sql> SET TIME ZONE 'Etc/UTC';
sql> SELECT unix_timestamp('1970-01-01T08:00:00.000+0800', "yyyy-MM-dd'T'HH:mm:ss.SSSZ");
0

to_unix_timestamp

该函数与unix_timestamp()函数功能类似，有以下几点需要注意：

  1. 该函数只是unix_timestamp()函数相比，unix_timestamp()函数有空函数的形式，返回当前时间的UTC时间；
  2. 该函数只有Spark支持，Hive不支持该函数；

时间与时区的运算

from_utc_timestamp

该函数理解起来比较困难，它实际的运算路径是：时间 –(等价变换)–> 目标时区 –(加/减时区差)–>结果时间，从语义上它表达是在时间加上某个时区相对于UTC偏移量后的时间。

函数 from_utc_timestamp(ts, string to_tz)
返回值类型 timestamp
描述 时间加上某个时区偏移量后的时间
参数 ts：UTC时间；Spark中可以是timestamp/date、时间字符串类型；Hive中可以是timestamp/date、tinyint/smallint/int/bigint、float/double、decimal。
to_tz：目标时区(从UTC到目标时区)

示例1：UTC 2016-08-31加上8小时（上海时区与UTC时区为8小时）后为UTC 2016-08-31T08:00:00.000

1
2
3
4
5
6
7
8
9
sql> SET TIME ZONE 'UTC';
sql> SELECT \
from_utc_timestamp('2016-08-31', 'UTC'), \
from_utc_timestamp('2016-08-31', 'Asia/Shanghai'), \
from_utc_timestamp('2016-08-31', 'Asia/Seoul');

2016-08-31T00:00:00.000+0000
2016-08-31T08:00:00.000+0000
2016-08-31T09:00:00.000+0000

示例2：

1
2
3
4
5
6
7
8
9
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT \
from_utc_timestamp('2016-08-31', 'UTC'), \
from_utc_timestamp('2016-08-31', 'Asia/Shanghai'), \
from_utc_timestamp('2016-08-31', 'Asia/Seoul');

2016-08-31T00:00:00.000+0800
2016-08-31T08:00:00.000+0800
2016-08-31T09:00:00.000+0800

to_utc_timestamp

与from_utc_timestamp()函数相对应，从语义上该函数表达是在时间减去某个时区相对于UTC偏移量后的时间。

函数 to_utc_timestamp(ts, string to_tz)
返回值类型 timestamp
描述 时间减去某个时区偏移量后的时间
参数 ts：UTC时间；Spark中可以是timestamp/date、时间字符串类型；Hive中可以是timestamp/date、tinyint/smallint/int/bigint、float/double、decimal。
to_tz：目标时区(从目标时区到UTC)

示例1：

1
2
3
4
5
6
7
8
9
10
11
sql> SET TIME ZONE 'UTC'
sql> SELECT \
to_utc_timestamp('1970-01-02 08:00:00.000+0800', 'Etc/UTC'),  \
to_utc_timestamp('1970-01-02 08:00:00.000', 'Etc/UTC'), \
to_utc_timestamp('1970-01-02 08:00:00.000+0800', 'Asia/Shanghai'), \
to_utc_timestamp('1970-01-02 08:00:00.000', 'Asia/Shanghai');

1970-01-02T00:00:00.000+0000
1970-01-02T08:00:00.000+0000
1970-01-01T16:00:00.000+0000
1970-01-02T00:00:00.000+0000

示例2：

1
2
3
4
5
6
7
8
9
10
11
sql> SET TIME ZONE 'Asia/Shanghai'
sql> SELECT \
to_utc_timestamp('1970-01-02 08:00:00.000+0800', 'Etc/UTC'),  \
to_utc_timestamp('1970-01-02 08:00:00.000', 'Etc/UTC'), \
to_utc_timestamp('1970-01-02 08:00:00.000+0800', 'Asia/Shanghai'), \
to_utc_timestamp('1970-01-02 08:00:00.000', 'Asia/Shanghai');

1970-01-02T08:00:00.000+0800
1970-01-02T08:00:00.000+0800
1970-01-02T00:00:00.000+0800
1970-01-02T00:00:00.000+0800

字符串转换时间

to_timestamp

函数 to_timestamp(string ts[, string fmt])
返回值类型 timestamp
描述 将时间字符串按指定格式解析为当前系统时区表示的时间
参数 ts：时间字符串。
fmt：格式化字符串，参考Datetime Patterns、Java SimpleDateFormat。

注意：Hive不支持该函数。

示例1：入参时间字符串没有时区信息，则默认使用系统当前时区对其进行解析

1
2
3
sql> SET TIME ZONE 'UTC';
sql> SELECT to_timestamp('2022-05-15 19:00:00', 'yyyy-MM-dd HH:mm:ss');
2022-05-15T19:00:00.000+0000

示例2：入参时间字符串没有时区信息，则默认使用系统当前时区对其进行解析

1
2
3
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT to_timestamp('2022-05-15 19:00:00', 'yyyy-MM-dd HH:mm:ss');
2022-05-15T19:00:00.000+0800

示例3：入参时间字符串包含明确的时区信息

1
2
3
sql> SET TIME ZONE 'UTC';
sql> SELECT to_timestamp('2022-05-15T19:00:00.000+0800', "yyyy-MM-dd'T'HH:mm:ss.SSSZ");
2022-05-15T11:00:00.000+0000

示例4：入参时间字符串包含明确的时区信息

1
2
3
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT to_timestamp('2022-05-15T19:00:00.000+0800', "yyyy-MM-dd'T'HH:mm:ss.SSSZ");
2022-05-15T19:00:00.000+0800

to_date

函数 to_date(string date[, string fmt])
返回值类型 date
描述 将时间字符串按指定格式解析为当前系统时区表示的时间
参数 date：时间字符串。
fmt：格式化字符串，参考Datetime Patterns、Java SimpleDateFormat。

示例参考to_timestamp()函数。

格式化函数

from_unixtime

函数 from_unixtime(bigint unixtime[, string to_fmt])
返回值类型 string
描述 将时间戳按当前系统时区表示的时间进行格式化
参数 unixtime：从Unix纪元(1970-01-01 00:00:00 UTC)计数的秒数；
fmt：格式化字符串，参考Datetime Patterns、Java SimpleDateFormat

示例1：

1
2
3
sql> SET TIME ZONE 'UTC';
sql> SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
1970-01-01 00:00:00

示例2：

1
2
3
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
1970-01-01 08:00:00

示例3：

1
2
3
sql> SET TIME ZONE 'Asia/Shanghai';
sql> SELECT from_unixtime(0, "yyyy-MM-dd'T'HH:mm:ss.SSSZ");
1970-01-01T08:00:00.000+0800

date_format

函数 date_format(ts, string to_fmt)
返回值类型 string
描述 将时间按指定格式进行格式化
参数 ts：date/timestamp、或时间字符串。
to_fmt：格式化字符串，参考Datetime Patterns、Java SimpleDateFormat。

示例1：入参时间字符串包含明确的时区信息

1
2
3
sql> SET TIME ZONE 'UTC';
sql> SELECT date_format('2022-05-15T19:00:00.000+0800', 'yyyy-MM-dd HH:mm:ss');
2022-05-15 11:00:00

示例2：入参时间字符串没有时区信息，则入参时间字符串默认为系统当前时区

1
2
3
sql> SET TIME ZONE 'UTC';
sql> SELECT date_format('2022-05-15 19:00:00', "yyyy-MM-dd'T'HH:mm:ss.SSSZ");
2022-05-15T19:00:00.000+0000

算数运算函数

date_add

date_sub

datediff

last_day

next_day

add_months

months_between

trunc

获取函数

参考

  1. https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/parameters/timezone
  2. https://docs.microsoft.com/en-us/azure/databricks/sql/language-manual/sql-ref-syntax-aux-conf-mgmt-set-timezone
  3. https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#date-and-timestamp-functions
  4. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions
  5. https://spark.apache.org/docs/latest/api/sql/index.html