Cloudera Impala Extract Function and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:BigData
  • Reading time:4 mins read

Cloudera extract function returns one of the numeric date or time fields from a TIMESTAMP value. Cloudera Impala extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. This function is equivalent to Impala date_part() function but parameters reversed. In this article, we will discuss on Impala extract function and its usage with some examples.

Cloudera Impala Extract Function

Cloudera Impala Extract Function Syntax

The extract function complies with SQL-99 standard function. The syntax for extract function is same as other RDBMS extract functions. Below is syntax for Cloudera Impala Extract function:

extract(timestamp, string unit);extract(unit FROM timestamp);

Both function perform same task. You can use either syntax.

Impala Extract Function Usage

Below table represents the descriptions of the different units used in extract function:

Extract Function Units Description
year Returns year from timestamp
month Return the number of the month within the year (1 – 12).
day Return the day of the month. Between (1 – 31).
hour Return the hour of the day (0 – 23).
minute Return the minute of the hour (0 – 59).
second Return the second of the minute, not including fractional parts (0 – 59).
millisecond Return the second of the minute, including fractional parts to one thousandth of a second, multiplied by 1000 (0 – 59999).

This function works with above mentioned units. If you need to divide by more complex units of time, such as by week or by quarter, use the TRUNC() function instead. 

You can read about regular expression and impala string functions in my other posts:

Cloudera Impala Extract Function Examples

Impala extract year from date

Query: select extract(year from now())

+--------------------------+ 
| extract(year from now()) | 
+--------------------------+ 
| 2018 | 
+--------------------------+ 
Fetched 1 row(s) in 0.18s

Cloudera Impala extract month from date

Query: select extract(month from now())

+---------------------------+ 
| extract(month from now()) | 
+---------------------------+ 
| 1 | 
+---------------------------+ 
Fetched 1 row(s) in 0.12s

Impala extract day from date

Query: select extract(day from now())

+-------------------------+ 
| extract(day from now()) | 
+-------------------------+ 
| 10 | 
+-------------------------+ 
Fetched 1 row(s) in 0.11s

Impala extract hours from date

Query: select extract(hour from now())

+--------------------------+ 
| extract(hour from now()) | 
+--------------------------+ 
| 4 | 
+--------------------------+ 
Fetched 1 row(s) in 0.11s

Cloudera Impala extract minutes from date

Query: select extract(minute from now())

+----------------------------+ 
| extract(minute from now()) | 
+----------------------------+ 
| 57 | 
+----------------------------+ 
Fetched 1 row(s) in 0.11s

Impala extract seconds from date

Query: select extract(second from now())

+----------------------------+ 
| extract(second from now()) | 
+----------------------------+ 
| 3 | 
+----------------------------+ 
Fetched 1 row(s) in 0.11s

Impala extract milliseconds from date

Query: select extract(millisecond from now())

+---------------------------------+ 
| extract(millisecond from now()) | 
+---------------------------------+ 
| 727 | 
+---------------------------------+ 
Fetched 1 row(s) in 0.11s
Tags: ,