hive时间函数

Hadoop Hive Date Functions and Examples

Many applications manipulate the date and time values. Latest Hadoop Hive query language support most of relational database date functions. In this article, we will check commonly used Hadoop Hive date functions and some of examples on usage of those functions.

Hadoop Hive Date Functions

Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. We shall see how to use the Hadoop Hive date functions with an examples. You can use these functions as Hive date conversion functions to manipulate the date data type as per the application requirements. Below are the most commonly used Hadoop Hive DateTime functions:

Date Function	Description
current_timestamp()	Returns the current date and time of the system. There is no now() function is Hadoop Hive.
current_date()	Returns the current date of the system without any time part.
add_months(timestamp date, int months)	Adds month value to specified date or timestamp values.
to_date(timestamp date)	Converts Hive timestamp value to date data type.
date_add(timestamp startdate, int days)	Adds days to specified timestamp value. Hive does not support interval data type in date_add. You can use interval type directly to add or substract from date values
date_sub(timestamp startdate, int days)	Substract specified number of days from the date or timestamp value.
datediff(timestamp enddate, timestamp startdate)	timestamp startdate)Returns number of days between the two date or timestamp values.
from_unixtime(bigint unixtime[, string format])	Converts the number of seconds from the Unix epoch to the specified time into a string.
month(timestamp date), minute(timestamp date), hour(timestamp date), day(timestamp date), second(timestamp date)	Returns month, minutes, hours, days, seconds from the timestamp.
trunc(timestamp, string unit)	Strips off fields from a TIMESTAMP value.
unix_timestamp()	Gets current time stamp using the default time zone.
unix_timestamp(string date)	Converts time string in format yyyy-MM-dd HH:mm:ss to Unix time stamp.
from_utc_timestamp(timestamp, string timezone)	Converts a specified UTC timestamp value into the appropriate value for a specified time zone

Related Article

• Hive Interval Data Types and Conversion Examples
• Apache Hive Type Conversion Functions and Examples

Hadoop Hive Date Functions Examples

Get current date and timestamp using HiveQL

hive> select current_timestamp();
OK
2017-10-01 00:54:14.736
Time taken: 0.65 seconds, Fetched: 1 row(s)

hive> select current_date();
OK
2017-10-01
Time taken: 0.161 seconds, Fetched: 1 row(s)

Add 1 day to current date using HiveQL

hive> select date_add(current_date(), 1);
OK
2017-10-02
Time taken: 0.123 seconds, Fetched: 1 row(s)

Subtract 1 day from current date using HiveQL

hive> select date_sub(current_date(),1);
OK
2017-09-30
Time taken: 0.107 seconds, Fetched: 1 row(s)

Get first day of the given timstamp using HiveQL

hive> select trunc(current_timestamp(), 'MONTH');
OK
2017-10-01
Time taken: 0.072 seconds, Fetched: 1 row(s)

Convert timestamp to date format using HiveQL

hive> select to_date(current_timestamp());
OK
2017-10-01
Time taken: 0.08 seconds, Fetched: 1 row(s)

Data type conversion using Cast function in HiveQL

hive> select cast(current_timestamp() as date);
OK
2017-10-01
Time taken: 0.094 seconds, Fetched: 1 row(s)

Convert Timestamp to YYYYMMDD format using HiveQL

hive> select from_unixtime(unix_timestamp(current_date()), 'yyyyMMdd');
OK
20170001
Time taken: 0.078 seconds, Fetched: 1 row(s)