One of the most important column types is the date/time in the data. The date/time helps in understanding the patterns, trends and even business.

For test data, use the following commands in Apache Impala.

hive> CREATE TABLE test_tbl(date1 TIMESTAMP);
hive> INSERT INTO test_tbl VALUES(‘2023-01-23 19:14:20.000′);

Year(string date)

The year function returns the year from the given input.

hive> SELECT YEAR(date1) FROM test_tbl;
OK
2023

Month(string date)

The month function returns the month of the given input.

hive> SELECT MONTH(date1) FROM test_tbl;
1

MINUTE(string date):

The Minute function returns the minute of the given input.

hive> SELECT MINUTE(date1) FROM test_tbl;
14

SECOND(string date):

The second function returns the second of the given input.

hive> SELECT SECOND(date1) FROM test_tbl;
20

DAY(string date)

The day function returns the day of the given input.

hive> SELECT day(date1), day(date2) FROM test_tbl;
23

DayOfMonth

The dayofMonth function returns the day of the given input.

hive> SELECT dayofmonth(date1) FROM test_tbl;

HOUR(string date)

The hour function returns the hour of the given input.

hive> SELECT hour(date1) FROM test_tbl;

WeekofYear(string date)

The weekofyear function returns the week of the given input.

hive> SELECT weekofyear(date1) FROM test_tbl; 
3

Current_Date

This function returns the current date (without timestamp).

hive> SELECT CURRENT_DATE();
2023-04-12

Current_Timestamp:

This function returns the current date and time.

hive> SELECT current_timestamp();
2023-04-12 09:00:20.002

Extract

As per the documentation, this function helps in retrieving fields such as days or hours from source. Source must be a date, timestamp, interval or a string that can be converted into either a date or timestamp. Supported fields include: day, dayofweek, hour, minute, month, quarter, second, week and year. This function introduced in Hive 2.2.0

hive> SELECT EXTRACT(month F “2023-10-20”) 
10
hive> SELECT EXTRACT(hour from “2023-10-20 05:06:07”) 
5
hive> SELECT EXTRACT(dayofweek from “2023-10-20 05:06:07”)
5
hive> SELECT EXTRACT(month from interval ‘1-3’ year to month)
3
hive> SELECT EXTRACT(minute from interval ‘3 12:20:30’ day to second)
20

..