Hive SQL时间函数及用法

·  阅读 258

本文已参与「新人创作礼」活动,一起开启掘金创作之路。

Hive SQL时间函数

当前官方提供的日期函数共27个,内容如下:

1. 获取当前系统时间

函数: current_timestamp
返回值:timestamp
返回查询计算开始时的当前时间戳(从Hive 1.2.0开始)。在同一个查询中对current_timestamp的所有调用都返回相同的值。 (hive 2.0 <= version)建议使用 CURRENT_TIMESTAMP 常量进行获取当前系统时间。

> select current_timestamp as ts;
+--------------------------+
| ts |
+--------------------------+
| 2021-10-19 10:27:00.042 |
+--------------------------+
1 row selected (0.208 seconds)
复制代码

函数: current_date
返回值:date
返回查询计算开始时的当前日期(从Hive 1.2.0开始)。在同一个查询中current_date的所有调用都返回相同的值。

> select current_date as t;
+-------------+
| t |
+-------------+
| 2021-10-19 |
+-------------+
1 row selected (0.217 seconds)
复制代码

函数: unix_timestamp()
返回值:bigint
(hive 2.0 > version)通过 unix_timestamp() 函数获取,返回当前Unix时间戳(以秒为单位)。

> select unix_timestamp();
+-------------+
| _c0 |
+-------------+
| 1630647636 |
+-------------+
复制代码

2. 将秒值转换为指定格式的字符串

函数: from_unixtime(bigint unixtime[, string format])
返回值:string
将 unix 纪元 (1970-01-01 00:00:00 UTC) 的秒数转换为表示当前系统时区中该时刻时间戳的字符串表示形式,格式为“1970-01-01 00:00: 00”。 示例:

> select from_unixtime(1634638621);
+----------------------+
| _c0 |
+----------------------+
| 2021-10-19 10:17:01 |
+----------------------+
1 row selected (0.206 seconds)
--指定字符串格式
> select from_unixtime(1634638621,'yyyy-MM-dd HH-mm-ss');
+----------------------+
| _c0 |
+----------------------+
| 2021-10-19 10-17-01 |
+----------------------+
1 row selected (0.222 seconds)
复制代码

3. 将时间字符串转化为秒值

函数: unix_timestamp(string date)
返回值:bigint
将时间字符串格式yyyy-MM-dd HH:mm:ss转换为Unix时间戳(以秒为单位),使用默认时区和默认地区,如果失败返回null

> select unix_timestamp('2021-10-19 10:17:01');
+-------------+
| _c0 |
+-------------+
| 1634638621 |
+-------------+
1 row selected (0.249 seconds)
> select unix_timestamp('2021-10-19 00');
+-------+
| _c0 |
+-------+
| NULL |
+-------+
1 row selected (0.217 seconds)
--可以接收current_date和current_timestamp
> select unix_timestamp(current_timestamp);
+-------------+
| _c0 |
+-------------+
| 1634639759 |
+-------------+
1 row selected (0.217 seconds)
> select unix_timestamp(current_date);
+-------------+
| _c0 |
+-------------+
| 1634601600 |
+-------------+
1 row selected (0.232 seconds)
复制代码

函数: unix_timestamp(string date, string pattern)
返回值:bigint
将具有给定模式的时间字符串转换为 Unix 时间戳(以秒为单位),如果失败则返回null。

> select unix_timestamp('2021-10-19', 'yyyy-MM-dd');
+-------------+
| _c0 |
+-------------+
| 1634601600 |
+-------------+
1 row selected (0.23 seconds)
> select unix_timestamp('2021-10-19 10:17:01', 'yyyy-MM-dd');
+-------------+
| _c0 |
+-------------+
| 1634601600 |
+-------------+
1 row selected (0.239 seconds)
> select unix_timestamp('20211019', 'yyyy-MM-dd');
+-------+
| _c0 |
+-------+
| NULL |
+-------+
1 row selected (0.21 seconds)
复制代码

4. 将date/timesatmp/str转化为日期格式fmt指定格式的字符串值

函数: date_format(date/timestamp/string ts, string fmt)
返回值:string
将日期/时间戳/字符串转换为日期格式 fmt 指定格式的字符串值(从 Hive 1.2.0 开始)。 支持的格式是Java SimpleDateFormat 格式。第二个参数fmt应该是常量。date_format可用于实现其他udf。

> select date_format('2021-10-20','yyyy');
+-------+
| _c0 |
+-------+
| 2021 |
+-------+
1 row selected (0.185 seconds)
复制代码

5. 获取时间字符串的日期部分

函数: to_date(string timestamp)
返回值:
pre 2.1.0: string
2.1.0 on: date
返回时间戳字符串(pre-Hive 2.1.0)的日期部分,在Hive 2.1.0 (Hive -13248)之前,返回类型是String,因为创建方法时没有Date类型存在。发生错误时返回null。

> select to_date('2021-10-20 01:47:57');
+-------------+
| _c0 |
+-------------+
| 2021-10-20 |
+-------------+
1 row selected (0.185 seconds)
> select to_date(current_date);
+-------------+
| _c0 |
+-------------+
| 2021-10-20 |
+-------------+
1 row selected (0.214 seconds)
> select to_date(current_timestamp);
+-------------+
| _c0 |
+-------------+
| 2021-10-20 |
+-------------+
1 row selected (0.222 seconds)
复制代码

6. 获取时间字符串的年、月、日等信息

函数:
year(string date)
quarter(date/timestamp/string)

month(string date)
day(string date) dayofmonth(date)
hour(string date)
minute(string date)
second(string date)
weekofyear(string date)

7. 获取指定单位的时间信息

函数: extract(field FROM source)
返回值:int
从source(从 Hive 2.2.0 开始)检索字段,例如天数或小时数。 source必须是日期、时间戳、间隔或可以转换为日期或时间戳的字符串。 支持的字段包括: day , dayofweek , hour , minute , month ,quarter , second , week 和 year 。

> select extract(month from "2016-10-20")
+------+
| _c0 |
+------+
| 10 |
+------+
1 row selected (0.201 seconds)
> select extract(hour from "2016-10-20 05:06:07");
+------+
| _c0 |
+------+
| 5 |
+------+
1 row selected (0.194 seconds)
> select extract(minute from interval '3 12:20:30' day to second);
+------+
| _c0 |
+------+
| 20 |
+------+
1 row selected (0.244 seconds)
复制代码

8. 计算两个日期之间的天数

函数: datediff(string enddate, string startdate)
返回值:int
返回从开始日期到结束日期的天数

> select datediff('2021-10-18','2019-10-19');
+------+
| _c0 |
+------+
| 730 |
+------+
1 row selected (0.183 seconds)
> select datediff('2021-10-18 05:06:07','2021-10-19');
+------+
| _c0 |
+------+
| -1 |
+------+
1 row selected (0.18 seconds)
复制代码

9. 计算两个日期之间的月数

函数: months_between(date1, date2)
返回值:double
返回日期 date1 和 date2 之间的月数(从 Hive 1.2.0 开始)。 如果 date1 晚于 date2,则结果为正。如果 date1 早于 date2,则结果为负数。 如果 date1 和 date2 是该月的同一天或都是该月的最后几天,则结果始终为整数。 否则,UDF 会根据有 31 天的月份计算结果的小数部分,并考虑时间分量 date1 和 date2 的差异。(计算结果为时间差值并除以31)date1 和 date2 类型可以是日期、时间戳或字符串,格式为“yyyy-MM-dd”或“yyyy-MM-dd HH:mm:ss”。 结果四舍五入到小数点后 8 位。 示例:months_between('1997-02-28 10:30:00', '1996-10-30') =3.94959677

> select months_between('2021-10-21','2021-10-20');
+-------------+
| _c0 |
+-------------+
| 0.03225806 |
+-------------+
1 row selected (0.232 seconds)
> select months_between('2021-10-19','2021-10-20');
+--------------+
| _c0 |
+--------------+
| -0.03225806 |
+--------------+
1 row selected (0.212 seconds)
复制代码

10. 日期加指定天数

函数: date_add(date/timestamp/string startdate, tinyint/smallint/int days)
返回值:
pre 2.1.0: string
2.1.0 on: date
给startdate添加天数,在Hive 2.1.0 (Hive -13248)之前,返回类型是String,因为创建方法时没有Date类型存在。

> select date_add('2021-10-22',2);
+-------------+
| _c0 |
+-------------+
| 2021-10-24 |
+-------------+
1 row selected (0.189 seconds)
> select date_add(current_timestamp,2);
+-------------+
| _c0 |
+-------------+
| 2021-10-22 |
+-------------+
1 row selected (0.195 seconds)
复制代码

11. 日期减去指定天数

函数: date_sub(date/timestamp/string startdate, tinyint/smallint/int days)
返回值:
pre 2.1.0: string
2.1.0 on: date
给startdate减去天数,在Hive 2.1.0 (Hive -13248)之前,返回类型是String,因为创建方法时没有Date类型存在。

> select date_sub('2021-10-22',2)
+-------------+
| _c0 |
+-------------+
| 2021-10-20 |
+-------------+
1 row selected (0.214 seconds)
> select date_sub(current_timestamp,2);
+-------------+
| _c0 |
+-------------+
| 2021-10-18 |
+-------------+
1 row selected (0.189 seconds)
复制代码

12. 日期加上指定月

函数: add_months(string start_date, int num_months, output_date_format)
返回值:string
返回start_date后的num_months日期(从Hive 1.1.0开始)。start_date可以是一个字符串、日期或时间戳。num_months是一个整数。如果 start_date 是该月的最后一天,或者如果结果月份的天数少于start_date 的日期部分,则结果是结果月份的最后一天。否则,结果与 start_date 具有相同的 day 部分。 默认输出格式为“yyyy-MM-dd”。

  • 在Hive 4.0.0之前,日期的时间部分会被忽略。
  • 在Hive 4.0.0中,add_months支持一个可选参数output_date_format,它接受一个String,表示输出的有效日期格式。这允许在输出中保留时间格式。
> select add_months('2021-08-31', 1);
+-------------+
| _c0 |
+-------------+
| 2021-09-30 |
+-------------+
1 row selected (0.214 seconds)
> select add_months('2021-12-31 14:15:16', 2, 'YYYY-MM-dd HH:mm:ss');
+----------------------+
| _c0 |
+----------------------+
| 2022-02-28 14:15:16 |
+----------------------+
1 row selected (0.206 seconds)
复制代码

13. 获取日期所属月份的最后一天

函数: last_day(string date)
返回值:string
返回该日期所属的月份的最后一天(截至Hive 1.1.0)。date为字符串,格式为“yyyy-MM-dd HH:mm:ss”或“yyyy-MM-dd”。日期的时间部分被忽略。

> select last_day(current_timestamp);
+-------------+
| _c0 |
+-------------+
| 2021-10-31 |
+-------------+
1 row selected (0.22 seconds)
> select last_day('2021-10-20');
+-------------+
| _c0 |
+-------------+
| 2021-10-31 |
+-------------+
1 row selected (0.244 seconds)
复制代码

14. 获取指定日期后的第一个指定星期几

函数: next_day(string start_date, string day_of_week)
返回值:string
返回晚于 start_date 并命名为 day_of_week 的第一个日期(从 Hive 1.2.0 开始)。 start_date 是一个字符串/日期/时间戳。 day_of_week 是星期几的 2 个字母、3 个字母或全名(例如 Mo、tue、FRIDAY)。 start_date 的时间部分被忽略。

--10-20号的第一个星期二
> select next_day('2021-10-20', 'TU');
+-------------+
| _c0 |
+-------------+
| 2021-10-26 |
+-------------+
1 row selected (0.224 seconds)
复制代码

15. 获取指定日期指定的单位日期

函数: trunc(string date, string format)
返回值:string
返回截断为格式指定单位的日期(从 Hive 1.2.0 开始)。支持的格式:MONTH/MON/MM、YEAR/YYYY/YY。

> select trunc('2021-10-20', 'YY');
+-------------+
| _c0 |
+-------------+
| 2021-01-01 |
+-------------+
1 row selected (0.213 seconds)
> select trunc('2021-10-20', 'MM');
+-------------+
| _c0 |
+-------------+
| 2021-10-01 |
+-------------+
1 row selected (0.21 seconds)
复制代码

16. 转换UTC timestamp 到指定时区

函数: from_utc_timestamp({any primitive type} ts, string timezone)
timestamp是一种原始类型,包括timestamp/date, tinyint/smallint/int/bigint,float/double and decimal。UTC为世界标准时间。

17. 将给定时区的 timestamp 转换为UTC

函数: to_utc_timestamp({any primitive type} ts, string timezone)
timestamp是一种原始类型,包括timestamp/date, tinyint/smallint/int/bigint, float/double and decimal。

参考:
Hive Functions

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改