In general, extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. Apache Hive does not support extract function, you can use other built in functions to extract required units from date value. In this article, we will check Hive extract function alternative and some examples.
Hive Extract Function Alternative
There is no extract function in Hive to extract sub part of date values. You can use Hive built in date function date_format() to extract required values from date fields. Below is the syntax for date_format:
date_format(date/timestamp/string ts, string fmt);
This function converts a date/timestamp/string to a value of string in the format specified by the date format fmt.
Read:
Hive Date Function date_format String Format and Return Value
| String Format or Units | Description |
| yyyy or y | This unit return year from date value |
| MM or M | This unit returns month from date field |
| dd or d | This unit returns days from date field |
| hh or h | This unit returns hours from date field |
| mm or m | Returns minutes from date field |
| ss or s | This unit returns second from date field |
Apache Hive date_format function does not have units for milliseconds.
Hive Extract Function Examples
Hive extract year from date
hive> select date_format(current_timestamp,'yyyy'); OK 2018 Time taken: 0.099 seconds, Fetched: 1 row(s)
Apache Hive extract month from date
hive> select date_format(current_timestamp,'MM'); OK 01 Time taken: 0.098 seconds, Fetched: 1 row(s)
Hive extract day from date
hive> select date_format(current_timestamp,'dd'); OK 10 Time taken: 0.099 seconds, Fetched: 1 row(s)
Hive extract hours from date
hive> select date_format(current_timestamp,'hh'); OK 12 Time taken: 0.106 seconds, Fetched: 1 row(s)
Apache Hive extract Minutes from date
hive> select date_format(current_timestamp,'mm'); OK 43 Time taken: 0.083 seconds, Fetched: 1 row(s)
Hive extract seconds from date
hive> select date_format(current_timestamp,'ss'); OK 54 Time taken: 0.092 seconds, Fetched: 1 row(s)