[Hive系列06] 日期函数详解

数据分析师的FIRE人生 2021-06-17

767

点击上方「数据分析师的FIRE人生」→

点击右上角「...」→设为星标⭐

大家好，我是风影楼，一名互联网公司的数据分析师。之前我曾在CSDN编写了Hive系列的付费博客专栏，目前收获了25W次的访问，不过由于CSDN的付费设置无法取消，所以我决定在公众号重新免费分享一遍。这一篇主要介绍Hive中的日期函数。

1、时间戳函数 unix_timestamp

时间戳函数unix_timestamp有三种不同的传参用法，具体如下：

1）unix_timestamp()

功能：返回该机器的当前时间戳。

举例：

hive (app)> select unix_timestamp();
1561224376

2）unix_timestamp(string date)

功能：将格式为yyyy-MM-dd hh:mm:ss的时间字符串转换成时间戳。

举例：

hive (app)> select unix_timestamp('2019-07-12 22:00:00');
1562940000
 
不符合yyyy-MM-dd HH:mm:ss的格式会返回NULL
hive (app)> select unix_timestamp('2019-07-12');
NULL

3）unix_timestamp(string date, string pattern)

功能：将时间字符串按指定格式转换成Unix时间戳。

pattern的格式可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，“yyyy-MM”，“yyyy”等。

举例：

输入年，返回2019-01-01 00:00:00的秒时间戳
hive (app)> select unix_timestamp('2019','yyyy');
1546272000
 
输入年月，返回2019-07-01 00:00:00的秒时间戳，注意月必须用大写的MM，否则7会分配到分钟上。
hive (app)> select unix_timestamp('2019-07','yyyy-MM');
1561910400


输入年月日，返回2019-07-12 00:00:00的秒时间戳
hive (app)> select unix_timestamp('2019-07-12','yyyy-MM-dd');
1562860800


输入年月日 时，返回2019-07-12 22:00:00的秒时间戳
hive (app)> select unix_timestamp('2019-07-12 22','yyyy-MM-dd hh');
1562940000


输入年月日 时分，返回2019-07-12 22:30:00的秒时间戳
hive (app)> select unix_timestamp('2019-07-12 22:30','yyyy-MM-dd hh:mm');
1562941800


输入年月日 时分秒，返回2019-07-12 22:30:30的秒时间戳
hive (app)> select unix_timestamp('2019-07-12 22:30:30','yyyy-MM-dd hh:mm:ss');
1562941830


如果输入的date内容比后边设定的格式少，返回NULL
hive (app)> select unix_timestamp('2019-07-12','yyyy-MM-dd hh:mm:ss');
NULL


如果输入的date内容比后边设定的格式多，返回设定的格式的时间戳，这里返回的是2019-07-12 00:00:00
hive (app)> select unix_timestamp('2019-07-12 22:30:30','yyyy-MM-dd');
1562860800

2、秒时间戳转换为可读时间字符串 from_unixtime

from_unixtime(bigint unixtime[, string format])

功能：unixtime传入秒值的时间戳。

format可指定的日期展示格式，可由yyyy、MM、dd、hh、mm、ss 6个元素使用任何字符进行任意连接组合。format如果不填则默认返回yyyy-MM-dd hh:mm:ss格式的时间字符串。

举例：

format如果不填则默认返回yyyy-MM-dd hh:mm:ss格式的时间字符串：


hive (app)> select from_unixtime(1546272000);
2019-01-01 00:00:00


hive (app)> select from_unixtime(1546272000,'yyyy');
2019


hive (app)> select from_unixtime(1546272000,'yyyy-MM');
2019-01


hive (app)> select from_unixtime(1546272000,'yyyyMMdd');
20190101


hive (app)> select from_unixtime(1546272000,'yyyy/MM/dd hh');
2019/01/01 12


hive (app)> select from_unixtime(1546272000,'yyyy-MM-dd hh:mm');
2019-01-01 12:00


hive (app)> select from_unixtime(1546272000,'yyyy-MM-dd hh:mm:ss');
2019-01-01 12:00:00

3、返回秒值时间戳的日期 to_date

to_date(string timestamp)

功能：timestamp传入秒值的时间戳，返回yyyy-MM-dd格式的日期时间字符串。

举例：

hive (app)> select to_date("1970-01-01 00:00:00");
1970-01-01

4、时间戳字符串的年、月、日、时、分、秒

year(string date)

month(string date)

day(string date)

hour(string date)

minute(string date)

second(string date)

功能：分别返回时间戳字符串的年、月、日、时、分、秒。

date的格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，“yyyy-MM”，“yyyy”等。

举例：

返回时间戳字符串的年
hive (app)> select year('2019-09-09 12:12:12');
2019


返回时间戳字符串的年
hive (app)> select year('2019-09-09');
2019


返回时间戳字符串的月
hive (app)> select month('2019-09-09 12:12:12');
9


返回时间戳字符串的日
hive (app)> select day('2019-09-09 12:12:12');
9


回时间戳字符串的时
hive (app)> select hour('2019-09-09 12:12:12');
12


返回时间戳字符串的分
hive (app)> select minute('2019-09-09 12:12:12');
12


返回时间戳字符串的秒
hive (app)> select second('2019-09-09 12:12:12');
12

5、日期在当年的第几周 weekofyear

weekofyear(string date)

功能：返回时间字符串位于一年中的第几个周内。

date的格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

举例：

hive (app)> select weekofyear('2019-07-12 00:00:00');
28
 
hive (app)> select weekofyear('2019-07-12');
28


hive (app)> select weekofyear('2019-07');
NULL

6、开始时间到结束时间相差的天数 datediff

datediff(string enddate, string startdate)

功能：返回开始时间startdate到结束时间enddate相差的天数。

第一个参数为结束时间enddate，第二个参数为开始时间startdate，二者的格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

举例：

hive (app)> select datediff('2019-07-12','2019-07-01');
11
 
hive (app)> select datediff('2019-07-12 12:00:00','2019-07-01');
11
 
hive (app)> select datediff('2019-07-12 12:00:00','2019-07-01 9:00:00');
11
 
hive (app)> select datediff('2019-07','2019-07-01');
NULL

7、日期加减天数 date_add&date_sub

date_add(string startdate, int days)

date_sub(string startdate, int days)

功能：返回开始时间startdate加上或减去days后的日期时间（只包含年月日）。

第一个参数startdate为开始时间，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

第二个参数days是加减的天数。

举例：

hive (app)> select date_add('2019-07-12',2);
2019-07-14
 
hive (app)> select date_sub('2019-07-12',2);
2019-07-10
 
hive (app)> select date_sub('2019-07-12 12:00:00',2);
2019-07-10
 
hive (app)> select date_sub('2019-07',2);
NULL

8、返回当前日期（年月日） current_date

current_date

功能：返回当前时间日期，格式为“yyyy-MM-dd”，只包含年月日。(Hive 1.2.0后才有)

举例：

hive (app)> select current_date;
2019-06-24

9、日期加减月份 add_months

add_months(string startdate, int num_months)

功能：返回startdate再增加num_months个月的日期，返回值的格式为“yyyy-MM-dd”，只包含年月日。

第一个参数startdate为开始时间，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

第二个参数num_months为加减的月份数。当其为正整数时，增加相应月份；为负整数时，则减少相应月份。

举例：

hive (app)> select add_months('2019-07-12 12:00:00',2);
2019-09-12
 
hive (app)> select add_months('2019-07-12',2);
2019-09-12
 
hive (app)> select add_months('2019-07-12',-2);
2019-05-12 
 
hive (app)> select add_months('2019-07',2);
NULL

10、当月最后一天的日期 last_day

last_day(string date)

功能：返回这个月的最后一天的日期(Hive 1.1.0后才有)，返回值的格式为“yyyy-MM-dd”，只包含年月日。

参数date为时间字符串，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

举例：

hive (app)> select last_day('2019-07-12');
2019-07-31
 
hive (app)> select last_day('2019-07-12 12:00:00');
2019-07-31
 
hive (app)> select last_day('2019-07');
NULL

11、参数对应的下一个星期X的日期 next_day

next_day(string startdate, string day_of_week)

功能：返回startdate的下一个星期X所对应的日期(Hive 1.2.0后才有)，返回值的格式为“yyyy-MM-dd”，只包含年月日。

第一个参数startdate为时间字符串，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

第二个参数day_of_week格式可以有三种：

星期几的英文全拼（不区分大小写）：MONDAY、TUESDAY、Wednesday、Thursday、Friday、Saturday、sunday

星期几的英文前三个字母（不区分大小写）：MON、TUE、Wed、Thu、Fri、Sat、sun

星期几的英文前两个字母（不区分大小写）：MO、TU、We、Th、Fr、Sa、su

举例：

返回2019-07-12的下一个周一的日期
hive (app)> select next_day('2019-07-12 12:00:00','monday');
2019-07-15
 
hive (app)> select next_day('2019-07-12 12:00:00','mon');
2019-07-15
 
hive (app)> select next_day('2019-07-12 12:00:00','mo');
2019-07-15

12、当年\月的第一天日期 trunc

trunc(string date, string format)

功能：返回date的当年或当月的第一天的日期(Hive 1.2.0后才有)，返回值的格式为“yyyy-MM-dd”，只包含年月日。

第一个参数date为时间字符串，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

第二个参数format所支持的格式：月份为MONTH/MON/MM, 年份为YEAR/YYYY/YY。

举例：

MM则返回当月的第一天日期
hive (app)> select trunc('2019-07-12 12:00:00','MM');
2019-07-01
 
hive (app)> select trunc('2019-07-12','MM');
2019-07-01
 
YY则返回当年的第一天日期
hive (app)> select trunc('2019-07-12 12:00:00','YY');
2019-01-01
 
hive (app)> select trunc('2019-07-12','YY');
2019-01-01

13、两日期之间相差的月份 months_between

months_between(date1, date2)

功能：返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0 (Hive 1.2.0后才有)，返回值是double类型。

date1、date2为时间字符串，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

举例：

2019-07-12 12:00:00和2019-07-11 01:00:00之间相隔0.04704301个月
hive (app)> select months_between('2019-07-12 12:00:00','2019-07-11 01:00:00');
0.04704301
 
hive (app)> select months_between('2019-07-12 12:00:00','2019-07-12 12:00:00');
0.0
 
hive (app)> select months_between('2019-07-12 12:00:00','2019-06-12 12:00:00');
1.0
 
hive (app)> select months_between('2019-07-12','2019-07-11');
0.03225806
 
hive (app)> select months_between('2019-08','2019-07');
NULL

14、按指定格式返回时间 date_format

date_format(timestamp/string ts, string fmt)

功能：按指定格式返回时间date (Hive 1.2.0后才有)，返回值是指定格式的时间字符串。

第一个参数ts为时间字符串，格式可以是“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh：mm”,“yyyy-MM-dd hh”,“yyyy-MM-dd”，至少要包含年月日，否则会返回NULL。

第二个参数fmt为可指定的日期展示格式，可由yyyy、MM、dd、hh、mm、ss 6个元素使用任何字符进行任意连接组合。

举例：

hive (app)> select date_format('2019-07-12 12:00:00','yyyy/MM');
2019/07
 
hive (app)> select date_format('2019-07-12 12:00:00','yyyyMMdd');
20190712
 
hive (app)> select date_format('2019-07-12','yyyyMMdd');
20190712

15、返回该日是星期几

pmod(datediff('要返回的日期', '任意年任意一个星期日的日期'), 7)

功能：返回值为 “0-6”，分别表示 “星期日-星期六”

举例：

--2019-01-06是星期日，放在第二个参数里，返回值为：第一个参数2020-01-06是星期几
hive (app)> select pmod(datediff('2020-01-06', '2019-01-06'), 7);
 
1


所以2020-01-06是星期一

16、返回该日期的本季度第一天

这个并没有函数可以直接实现，需要通过如下代码来实现：

SELECT date(from_unixtime(unix_timestamp(CONCAT (
                        year('$$begindate')
                        ,CASE 
                              WHEN (floor(month('$$begindate')  3.1) * 3) + 1 < 10
                                    THEN CONCAT (
                                                0
                                                ,(floor(month('$$begindate')  3.1) * 3) + 1
                                                )
                              ELSE (floor(month('$$begindate') / 3.1) * 3) + 1
                              END
                        ,'01'
                        ), 'yyyyMMdd')))

数据库

文章转载自数据分析师的FIRE人生，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

[Hive系列06] 日期函数详解

评论