Built with Tutify.io

    AIStudyLabs

    Hive Date and Timestamp Functions Examples

    Hive Date and Timestamp Functions Examples

    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.

    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

    Related: Apache Spark Date & Timestamp Functions

    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

    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.

    <a name="from_unixtime"></a>

    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

    <a name="unix_timestamp"></a>

    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

    <a name="to_date"></a>

    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

    <a name="current_date"></a>

    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

    <a name="current_timestamp"></a>

    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

    <a name="from_utc_timestamp"></a>

    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

    <a name="to_utc_timestamp"></a>

    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

    <a name="extract-year-quarter-month-day-from-hive-date-timestamp"></a>

    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.

    <a name="year"></a>

    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

    <a name="quarter"></a>

    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

    <a name="month"></a>

    3.3 month(string date)

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

    <a name="day"></a>

    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

    <a name="weekofyear"></a>

    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

    <a name="extract-hour-minute-and-seconds-from-timestamp"></a>

    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.

    <a name="hour"></a>

    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

    <a name="minute"></a>

    4.2 minute(string date)

    Return minute from timestamp

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

    <a name="second"></a>

    4.3 second(string date)

    Return second from timestamp

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

    <a name="extract"></a>

    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

    <a name="datediff"></a>

    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

    <a name="date_add"></a>

    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

    <a name="date_sub"></a>

    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

    <a name="add_months"></a>

    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

    <a name="last_day"></a>

    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

    <a name="next_day"></a>

    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

    <a name="trunc"></a>

    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

    <a name="months_between"></a>

    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

    <a name="convert"></a>

    6. Convert Date & Timestamp into String Format

    <a name="date_format"></a>

    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 !!

    <a name="h-related-articles"></a>

    Related Articles