Extract function in Hive with examples

Extract function in Hive

Extract function is used to get the date or time part values from a Date/Timestamp/Interval/String that can be converted into either a date or timestamp.

The date or time part values includes day, day of week, hour, minute, second, week, month, quarter and year.

  • Date format – The Date must match the ‘YYYY-MM-DD’ format.
  • Time format – The Timestamp must match the ‘YYYY-MM-DD HH:MM:SS.SSS’ format.

Syntax for Extract function

Examples for Extract function in Hive

Extract Year from Current Date

Extract function can be used to get the specific date values from current_date. In this example, we have extracted the year from current_date.

Extract year from current_date in Hive
Extract year from current_date in Hive

Extract Year from a Date value

In this example, extract function returns the year from the specified Date value. Since the given date is ‘2022-08-31″, it returned the year as 2022.

Extract year from date value in Hive
Extract year from date value in Hive

Extract Month from a Date

Next we tried to extract the month from the date value. Since the given date has a month as “8”, extract function returned the same as output.

Extract month from date value in Hive
Extract month from date value in Hive

Extract Day from a Date

Similar to year/month, we have extracted the day from date using Extract function.

Extract day from date in Hive
Extract day from date in Hive

Extract Day of week from a Date

The day of week is a date part which returns an integer from 1-7, starting with Sunday. In this example, we have specified the date as “2022-08-31” which is Wednesday. So the Extract function returned the day of week as 4.

Extract day of week from date in Hive
Extract day of week from date in Hive

Extract Hour from Current Timestamp

As we mentioned earlier, Extract function can be used to get the time parts from the time stamp values. In this example, we have extracted the hour from current_timestamp value. Since the current_timestamp is “2022-09-06 20:04:27.879″, the extract function returned the hour as 20.

Extract hour from current_timestamp in Hive
Extract hour from current_timestamp in Hive

Extract Minutes from a Timestamp

In this example, we have extracted the minutes from the specified timestamp value. Since the timestamp value follows the format as “YYYY-MM-DD HH:MM:SS.SSS”, the extract function returns the minutes as 4.

Extract minutes from time in Hive
Extract minutes from time in Hive

Extract Second from a Timestamp

In this example, the extract function returns the seconds from the given timestamp value.

Extract seconds from time in Hive
Extract seconds from time in Hive

Extract month from a Interval

Intervals can be used to give the specific time units such as SECONDS / MINUTES / HOURS / DAYS / WEEKS / MONTHS / YEARS. In this example, we have specified time unit 1 year 2 month as Intervals.

Then we applied the Extract function to get the month from interval. So it returned the month as 2.

Extract month from Interval in Hive
Extract month from Interval in Hive

Extract minute from a Interval

In this example, we specified day to second value as interval. It follows the format as SD H:M:S.nnnnnn

  • S: optional sign (+/-)
  • D: day count
  • H: hours 
  • M: minutes
  • S: seconds
  • nnnnnn: optional nanotime
Extract minute from interval in Hive
Extract minute from interval in Hive

Similar to these examples, we can apply the Extract function on the column which has date or time stamp values.

Recommended Articles

References from Apache Hive documentation