【Hive】Hive日期和时间戳函数

原文链接

Hive Date and Timestamp functions are used to manipulate Date and Time on HiveQL queries over Hive CLI, Beeline, and many more applications Hive supports.

The default date format of Hive is yyyy-MM-dd, and for Timestamp yyyy-MM-dd HH:mm:ss.

When using Date and Timestamp in string formats, Hive assumes these are in default formats, if the format is in a different format you need to explicitly specify the input pattern in order for Hive to understand and parse. Hive supports all formats specified in Java SimpleDateFormat.

Hive also supports Unix Epoch time which starts from 1970-01-01 00:00:00 UTC

In this article, you will learn the following date operations by using Hive Date Functions.

Also, you will learn the following timestamp operations by using Hive Timestamp Functions.

1. Hive Date and Timestamp Functions List

FUNCTION	HIVE DATE & TIMESTAMP FUNCTIONS USAGE
from_unixtime()	Syntax: `from_unixtime(bigint unixtime[, string format])` – Returns timestamp in a string after converting the number of seconds from Unix epoch (1970-01-01 00:00:00 UTC) Return: String
unix_timestamp()	Syntax1: unix_timestamp() Syntax2: unix_timestamp(string date) Syntax3: unix_timestamp(string date, string pattern) – Function from syntax1 has been deprecated; when used it returns the system timestamp. – The other two functions take date, timestamp strings, and return Unix epoch time. Return: Bigint
to_date()	Syntax: `to_date(string timestamp)` Returns the date part of a timestamp string. Return: Date (String prior to 2.1.0)
year()	Syntax: `year(string date)` Returns a year from the date or a timestamp string Return: Int
quarter()	Syntax: `quarter(date/timestamp/string)` Returns the quarter of the year from a date, timestamp, or string in the range 1 to 4 Return: Int
month()	Syntax: `month(string date)` Returns the month part of a date or a timestamp string. Return: Int
day() dayofmonth()	Syntax: `day(string date)` Syntax: `dayofmonth(date)` Returns the day part of a date or a timestamp string Return: Int
hour()	Syntax: `hour(string date)` Returns the hour part of the timestamp Return: Int
minute()	Syntax: `minute(string date)` Returns the minute part of the timestamp. Return: Int
second()	Syntax: `second(string date)` Returns the second part of the timestamp. Return: Int
weekofyear()	Syntax: `weekofyear(string date)` Returns the week number of the year of a timestamp string Return: Int
extract()	Syntax: `extract(field FROM source)` Extract the specific unit from Date and Time. It support fields: day, dayofweek, hour, minute, month, quarter, second, week and year Return: Int
datediff()	Syntax: `datediff(string enddate, string startdate)` Returns the number of days between startdate and enddate. Return: Int
date_add()	Syntax: `date_add(date startdate, tinyint/smallint/int days)` Syntax: `date_add(timestamp startdate, tinyint/smallint/int days)` Syntax: `date_add(string startdate, tinyint/smallint/int days)` Adds a number of days to startdate Return: Date (String prior to 2.1.0)
date_sub()	Syntax: `date_sub(date startdate, tinyint/smallint/int days)` Syntax: `date_sub(timestamp startdate, tinyint/smallint/int days)` Syntax: `date_sub(string startdate, tinyint/smallint/int days)` Subtracts a number of days to startdate: Return: Date (String prior to 2.1.0)
from_utc_timestamp()	Syntax: `from_utc_timestamp ({<em>primitive type</em>} ts, string timezone)` Converts a timestamp* in UTC to a given timezone Return: Timestamp
to_utc_timestamp()	Syntax: `to_utc_timestamp ({<em>any primitive type</em>} ts, string timezone)` Converts a timestamp* in a given timezone to UTC Return: Timestamp
current_date()	Syntax: `current_date()` Returns the current system date at the start of query evaluation Return: Date
current_timestamp()	Syntax: `current_timestamp()` Returns the current system timestamp at the start of query evaluation Return: Timestamp
add_months()	Syntax: `add_months(string start_date, int num_months, output_date_format)` – Returns the date that is num_months after start_date – The default output format is ‘yyyy-MM-dd’. – when output_date_format is used, it returns data in a specified pattern Return: String
last_day()	Syntax: `last_day(string date)` Returns the last day of the month Return: String
next_day()	Syntax: `next_day(string start_date, string day_of_week)` Returns the first date which is later than start_date and named as day_of_week Return: String
trunc()	Syntax: `trunc(string date, string format)` Returns truncated date to the unit specified by the format. Return: String
months_between()	Syntax: `months_between(date1, date2)` Returns the number of months between date1 and date2. Return: Double
date_format()	Syntax: `date_format(date/timestamp/string ts, string fmt)` Converts a date/timestamp/string to a string value specified in the formatted pattern. Return: String

Table – Hive Date and Timestamp Functions

2. Hive Date & Timestamp Functions Examples

Below I have explained each of these date and timestamp functions with examples.

2.1 from_unixtime(bigint unixtime[, string format])

Hive from_unixtime() is used to get Date and Timestamp in a default format yyyy-MM-dd HH:mm:ss from Unix epoch seconds.

Specify the second argument in pattern format to return date and timestamp in a custom format.

Syntax – from_unixtime(bigint unixtime[, string format])

Returns – string (date and timestamp in a string)


jdbc:hive2://> select from_unixtime(1605108612);
2020-11-11 15:30:12
jdbc:hive2://> select from_unixtime(1605108612,'MM-dd-yyyy HH:mm:ss');
11-11-2020 15:30:12

2.2 unix_timestamp() – Gets Unix Epoch time in seconds

This returns the Unix epoch time in seconds for a date and time specified in the input string. It also takes an optional pattern that is used to specify the input date string format.

Note: Function unix_timestamp(void) has been deprecated and recommends to use current_timestamp(), when used this returns the current time in Unix epoch seconds.

Syntax1: unix_timestamp()
Syntax2: unix_timestamp(string date)
Syntax3: unix_timestamp(string date, string pattern)

Returns – bigint (Unix epoch time in seconds)


0: jdbc:hive2://> select unix_timestamp();
1605108404
jdbc:hive2://> select unix_timestamp('2020-11-11 15:30:12.084');
1605108612
jdbc:hive2://> select unix_timestamp('11-11-2020 15:30:12.084','MM-dd-yyyy HH:mm:ss');
1605108612
jdbc:hive2://> select unix_timestamp('11-11-2020 15:30:12.084');
-61619473788

2.3 to_date(string timestamp) – Converts Timestamp string to Date type

to_date() function takes timestamp as an input string in the default format yyyy-MM-dd HH:mm:ss and converts into Date type.

Syntax: to_date(string timestamp)

Returns – date (String prior to 2.1.0)


jdbc:hive2://> select to_date('2020-11-11 15:30:12.084');
2020-11-11

2.4 current_date() – Get today/current Date of the system without Time

Returns current system date in date type without time.

Return – date


jdbc:hive2://> select current_date();
2020-11-11

2.5 current_timestamp() – Get current Timestamp along with Data and Time

Returns current system Timestamp (including date & time) in timestamp format.

Return – timestamp


0: jdbc:hive2://> select current_timestamp();
2020-11-11 15:27:05.741

2.6 from_utc_timestamp({any primitive type} ts, string timezone)

Converts Timestamp in Unix epoch time to a specified timezone

Syntax: from_utc_timestamp({any primitive type} ts, string timezone)

Return – timestamp


jdbc:hive2://> select from_utc_timestamp(1605108612,'PST')
1970-01-19 05:51:48.612
jdbc:hive2://> select from_utc_timestamp(1605108612,'CST');
1970-01-19 07:51:48.612

2.7 to_utc_timestamp({any primitive type} ts, string timezone)

Syntax: to_utc_timestamp({any primitive type} ts, string timezone)

Return – timestamp


jdbc:hive2://> select to_utc_timestamp(1605108612,'PST')
1970-01-19 21:51:48.612
jdbc:hive2://> select to_utc_timestamp(1605108612,'CST')
1970-01-19 19:51:48.612

3. Extract Year, Quarter, Month, Day from Hive Date and Timestamp

Use year() function to extract the year, quarter() function to get a quarter (between 1 to 4), month() to get a month (1 to 12), weekofyear() to get the week of the year from Hive Date and Timestamp. Below are the examples of each of these. when the Date is not in the right format, these functions return NULL.

3.1 year(string date) – Get year from date and timestamp


jdbc:hive2://> select year('2020-11-11');
2020
jdbc:hive2://> select year('11-11-2020');
NULL
jdbc:hive2://> select year('2020-11-11 15:30:12.084');
2020

3.2 quarter(date/timestamp/string)


jdbc:hive2://> select quarter('2020-11-11');
4
jdbc:hive2://> select quarter('2020-11-11 15:30:12.084');
2020
jdbc:hive2://> select quarter('2020-02-28');
2

3.3 month(string date)


jdbc:hive2://> select month('2020-02-28');
2

3.4 day(string date) dayofmonth(date)


jdbc:hive2://> select day('2020-02-28');
28
jdbc:hive2://> select day('2020-11-11 15:30:12.084');
11
select dayofmonth('2020-02-28');
28

3.5 weekofyear(string date)


jdbc:hive2://> select weekofyear('2020-11-11 15:30:12.084');
46
jdbc:hive2://> select weekofyear('2020-11-11');
46

4. Extract Hour, Minute, and Seconds from Hive Timestamp

Use hour() function to extract the hour(1 to 24), minute() function to get minute(between 1 to 60), second() to get second (1 to 60) from Hive Timestamp. Below are the examples of each of these. When the input Timestamp is not in the right format, these functions return NULL.

4.1 hour(string date)

Return hour from timestamp


jdbc:hive2://> select hour('2020-11-11');
0
jdbc:hive2://> select hour('2020-11-11 15:30:12.084');
15

4.2 minute(string date)

Return minute from timestamp


jdbc:hive2://> select minute('2020-11-11 15:30:12.084');
30

4.3 second(string date)

Return second from timestamp


jdbc:hive2://> select second('2020-11-11 15:30:12.084');
12

4.4 extract(field FROM source)

Extract the specific unit from Date and Time. It supports fields day, dayofweek, hour, minute, month, quarter, second, week, and year

Syntax: extract(field FROM source)

Return: int


jdbc:hive2://> select extract(month from "2020-11-20")
11
jdbc:hive2://> select extract(year from "2020-11-20")
2020
jdbc:hive2://> select extract(hour from "2020-11-20 06:10:45")
6

5. Data Difference, Add and Subtract Dates

5.1 datediff(string enddate, string startdate)

Hive datediff() returns number of days between two dates (enddate and startdate). if the first argument is lesser than the second parameter, then it returns the difference in negative.

Syntax: datediff(string enddate, string startdate)

Returns – int


jdbc:hive2://> select datediff('2020-11-01','2020-11-11');
-11
jdbc:hive2://> select datediff('2020-11-11','2020-11-01');
10

5.2 date_add(date/timestamp/string startdate, tinyint/smallint/int days)

Hive date_add() takes arguments either date, timestamp or string in default format and returns the date by adding the value from the second argument.

Syntax: date_add(date/timestamp/string startdate, tinyint/smallint/int days)

Returns – date (String prior to 2.1.0)


jdbc:hive2://> select date_add('2020-11-11 15:30:12.084',2);
2020-11-13
jdbc:hive2://> select date_add('2020-11-11',2);
2020-11-13

5.3 date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

Hive date_sub() takes arguments either date, timestamp or string in default format and returns the date by subtracting (minus) the value from the second argument.

Syntax: date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

Return – date (String prior to 2.1.0)


jdbc:hive2://> select date_sub('2020-11-11 15:30:12.084',2);
2020-11-09
jdbc:hive2://> select date_sub('2020-11-11',2);
2020-11-09

5.4 add_months(string start_date, int num_months, output_date_format)

add_months() returns the string after adding months to the date. If you want date output in a custom format instead of default use third argument with pattern you want.

Syntax: add_months(string start_date, int num_months, output_date_format)

Return – string


jdbc:hive2://> select add_months('2020-11-11',2);
2021-01-11
jdbc:hive2://> select add_months('2020-11-11 15:30:12.084',2);
2021-01-11
jdbc:hive2://> select add_months('2020-11-11',2,'MM-dd-yyyy');
01-11-2021

5.5 last_day(string date)

Hive last_day() is used to find the last day of the date.

Syntax: last_day(string date)

Return – string


jdbc:hive2://> select last_day('2020-11-11');
2020-11-30
jdbc:hive2://> select last_day('2020-02-11');
2020-02-29

5.6 next_day(string start_date, string day_of_week)

Returns the next day of the date or timestamp. specify the day you want to find as a second argument.

Syntax: next_day(string start_date, string day_of_week)

Return – string


jdbc:hive2://> select next_day('2020-02-11','FRIDAY');
2020-02-14
jdbc:hive2://> select next_day('2020-02-11','FRI');
2020-02-14

5.7 trunc(string date, string format)

Truncates date or timestamp to a specified format.

Syntax: trunc(string date, string format)

Return – string


jdbc:hive2://> select trunc('2020-02-11','MM');
2020-02-01
jdbc:hive2://> select trunc('2020-02-11','YYYY');
2020-01-01

5.8 months_between(date1, date2)

Hive months_between() is used to return the number of months in between two dates.

Syntax: months_between(date1, date2)

Return – double


jdbc:hive2://> select months_between('2020-11-11','2020-02-11');
9.0
jdbc:hive2://> select months_between('2020-11-09','2020-02-11');
8.93548387

6. Convert Date & Timestamp into String Format

6.1 date_format(date/timestamp/string ts, string fmt)

Hive date_format() is used to format date and timestamp in a specified pattern, this is also used to convert date into a string.

Syntax: date_format(date/timestamp/string ts, string fmt)

Return – string


jdbc:hive2://> select date_format('2020-11-09','dd-MM-YYYY');
09-11-2020
jdbc:hive2://> select date_format('2020-11-11 15:30:12.084','dd-MM-yyyy');
11-11-2020

Happy Learning !!

posted @ 2022-06-25 14:48 梦醒江南·Infinite 阅读(998) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 【Hive】Hive数据类型

· 【Hive】Hive客户端命令行官方说明文档

· hive开发中实际的日期转换函数

· [SQL] Hive日期时间函数整理

· Hive 日期时间相关函数总结

阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 单元测试从入门到精通
· 上周热点回顾（3.3-3.9）
· winform 绘制太阳，地球，月球运作规律

1. Hive Date and Timestamp Functions List

2. Hive Date & Timestamp Functions Examples

2.1 from_unixtime(bigint unixtime[, string format])

2.2 unix_timestamp() – Gets Unix Epoch time in seconds

2.3 to_date(string timestamp) – Converts Timestamp string to Date type

2.4 current_date() – Get today/current Date of the system without Time

2.5 current_timestamp() – Get current Timestamp along with Data and Time

2.6 from_utc_timestamp({any primitive type} ts, string timezone)

2.7 to_utc_timestamp({any primitive type} ts, string timezone)

3. Extract Year, Quarter, Month, Day from Hive Date and Timestamp

3.1 year(string date) – Get year from date and timestamp

3.2 quarter(date/timestamp/string)

3.3 month(string date)

3.4 day(string date) dayofmonth(date)

3.5 weekofyear(string date)

4. Extract Hour, Minute, and Seconds from Hive Timestamp

4.1 hour(string date)

4.2 minute(string date)

4.3 second(string date)

4.4 extract(field FROM source)

5. Data Difference, Add and Subtract Dates

5.1 datediff(string enddate, string startdate)

5.2 date_add(date/timestamp/string startdate, tinyint/smallint/int days)

5.3 date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

5.4 add_months(string start_date, int num_months, output_date_format)

5.5 last_day(string date)

5.6 next_day(string start_date, string day_of_week)

5.7 trunc(string date, string format)

5.8 months_between(date1, date2)

6. Convert Date & Timestamp into String Format

6.1 date_format(date/timestamp/string ts, string fmt)

公告

搜索

常用链接

随笔档案