Extract function in Hive with examples
Contents
- 1 Extract function in Hive
- 1.1 Syntax for Extract function
- 1.2 Examples for Extract function in Hive
- 1.2.1 Extract Year from Current Date
- 1.2.2 Extract Year from a Date value
- 1.2.3 Extract Month from a Date
- 1.2.4 Extract Day from a Date
- 1.2.5 Extract Day of week from a Date
- 1.2.6 Extract Hour from Current Timestamp
- 1.2.7 Extract Minutes from a Timestamp
- 1.2.8 Extract Second from a Timestamp
- 1.2.9 Extract month from a Interval
- 1.2.10 Extract minute from a Interval
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
|
1 |
extract(<date_or_time_part> FROM <date_or_time_expr>) |
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 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 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 Day from a Date
Similar to year/month, we have extracted the day from date using Extract function.
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 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 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 Second from a Timestamp
In this example, the extract function returns the seconds from the given timestamp value.
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.
|
1 2 3 4 5 |
interval '1-2' year to month #explanation year - 1 month - 2 |
Then we applied the Extract function to get the month from interval. So it returned the month as 2.
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
|
1 2 3 4 5 6 7 |
interval '3 12:55:30' day to second #explanation day - 3 hour - 12 minute - 55 second - 30 |
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
Your Suggestions
