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 Timestampyyyy-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
Related: Apache Spark Date & Timestamp Functions
In this article, you will learn the following date operations by using Hive Date Functions.
- How to get current system Date
- Convert Timestamp to Date
- Extract Year, Quarter, Month, Day from Hive Date or Timestamp
- Covert Date into String
Also, you will learn the following timestamp operations by using Hive Timestamp Functions.
- How to get current system Timestamp
- Convert Unix epoch seconds to Timestamp
- Convert Timestamp to Unix epoch seconds
- Extract Hour, Minute, and Seconds from Hive Timestamp
- Convert Timestamp into String format
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 |
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 !!