Chapter 8 – Date Functions

"An inch of time cannot be bought with an inch of gold."

- Chinese Proverb

Current_Date and Current_Timestamp Functions

You can use the current_date and current_timestamp functions to get the date and time. You must have a FROM clause. Above, we used the Order_Table with a LIMIT statement to retrieve today's date and timestamp.

Extracted the Date from a Time Data Type

You can use the to_date function to extract the day in yyyy-mm-dd format.

Adding and Subtracting Days from a Time Column

You can use the to_date function to extract the day in yyyy-mm-dd format.

Adding Days and Providing a Discount

The example above extracts the date from a time data type, adds days to a time and calculates a discount for an Order_Total.

Getting the Date Extracted From a Time Data Type

The data type hive uses to store a date is String. You can use a substring functions to achieve the same, but the example above uses the regexp_extract function in hive.

Getting the Date Extracted from a Time Using Substring

The data type hive uses to store a date is String. Notice above that the date was a timestamp. We used a substring function to get just the date portion from the string.

Getting the Date Extracted from a Time Using Concat

The data type hive uses to store a date is String. You can use a concat function to get the date portion from the string.

Getting the Date Extracted in Day-Month-Year Format

The data type hive uses to store a date is String. You can use a concat function to get the date portion from the string.

Getting the Date Extracted in Day-Month-Year Format

The data type hive uses to store a date is String. You can use a concat function to get the date portion from the string.

The Date in Perfect Day-Month-Year Format with CASE

This got a little tricky. By using the case statement, we were able to get the date perfectly formatted.

Getting a Count of All Orders Per Year Per Month

The example above calculates the number of orders per year per month for all orders in the year 1999.

Extracting the Year, Month and Day from a Time Data Type

The example above extracts the year, month and day from a time data type.

Extracting the Hour, Minute and Second from Time Data

The example above extracts the hour, minute and seconds from a time data type. This example uses a literal date and time in order to better show technique.

The ADD_MONTHS Command

This is the Add_Months Command. Using this command, you can add a month or many months to your date columns. Can you convert this to one year? There is no ADD_YEAR command!

The ADD_MONTHS Command to Add Years

Notice the clever techniques to add a year and seven years. Let the system do the tricky calculations.

Using Cast to Change a Data Type

Cast stands for Convert and Store. It temporarily changes a data type. Above, we changed the order_time to a date and the order_total to a decimal with a precision of 4.

The Months_Between Command

You can calculate the number of months between two dates by using the MONTHS_BETWEEN command.

NEXT_DAY Command Finds a Future Day of the Week

This example calculates the date of the following Monday from an Order_Date. The Monday date will be later than the Order_Date. We could also put in another day of the week.

NEXT_DAY Command Finds a Future Day of the Week

This example calculates the date of the following Tuesday from an Order_Date. The Tuesday date will be later than the Order_Date. We could also put in another day of the week.

Interval Day

An interval is designed to add or subtract intervals. Above, we have added an interval of 1 day to our current_date.

Hadoop Calendar Knows Leap Year

An interval is designed to add or subtract intervals. Above, we have added an interval of 1 day to our current_date, which is the last day of the month. Some systems will error on an interval that merely adds a day when that day is an invalid date. Hadoop knows it is not leap year so the interval moves to March 1st.

Interval Day, Month, Year Plus Cast

Our example has added one day, three months and five years to our current_date. We also used the CAST command to take the time portion out of our one-day interval.