Commonly used Cloudera Impala Date Functions and Examples

This article is about short descriptions and examples of the commonly used Cloudera Impala date functions that you can use to manipulate date columns in Impala SQL.

In the real word scenarios many application manipulate the date and time data types. Impala SQL supports most of the date and time functions that relational databases supports. Date types are highly formatted and very complicated. Each date value contains the century, year, month, day, hour, minute, and second. We shall see how to use the Impala date functions with an examples.

Cloudera Impala Date Functions

Just like other relational databases, Cloudera Impala provides many way to handle the date data types. Below are the some of the commonly used Impala date functions.

Date Function	Description
add_months(timestamp date, int months)	Adds month to specified timestamp data types.
to_date(timestamp date)	Converts timestamp to date data type
now()	Returns the current date and time of the system
current_timestamp()	Returns the current date and time of the system. Alias for now()
date_add(timestamp startdate, int days/interval expression)	Adds days to specified timestamp value. It also accepts INTERVAL as a second argument.
extract(timestamp, string unit) or extract(unit FROM timestamp)	This function returns one of year, month, day, hour, minute, second, or millisecond in numeric format.
date_part(‘string’, timestamp)	This function returns one of year, month, day, hour, minute, second, or millisecond in numeric format.
date_sub(timestamp startdate, int days//interval expression)	Substract specified number of days from the timestamp value. It can accepts the interval expression as well.
datediff(timestamp enddate, timestamp startdate)	Returns number of days between the two timestamp values.
from_unixtime(bigint unixtime[, string format])	Converts the number of seconds from the Unix epoch to the specified time into a string.
from_utc_timestamp(timestamp, string timezone)	Converts a specified UTC timestamp value into the appropriate value for a specified time zone
months_between(timestamp newer, timestamp older)	Returns number of month between two dates.
month(timestamp date), minute(timestamp date), hour(timestamp date), day(timestamp date), second(timestamp date), millisecond(timestamp date)	Returns month, minutes, hours, days, seconds, milliseconds from the timestamp.
trunc(timestamp, string unit)	Strips off fields from a TIMESTAMP value.

Impala Date Functions Examples

Get current date and timestamp in Impala SQL

+-------------------------------+
| now() |
+-------------------------------+
| 2017-09-30 22:19:11.124347000 |
+-------------------------------+

+-------------------------------+
| now() |
+-------------------------------+
| 2017-09-30 22:19:11.124347000 |
+-------------------------------+

Add 1 month to current date in Impala SQL

+-------------------------------+ 
| add_months(now(), 1) | 
+-------------------------------+ 
| 2017-10-31 22:27:50.700057000 | 
+-------------------------------+

+-----------------------------------+ 
| date_add(now(), interval 1 month) | 
+-----------------------------------+ 
| 2017-10-30 22:29:15.518564000 | 
+-----------------------------------+

Subtract 1 month from current date using Impala SQL

+-------------------------------+ 
| add_months(now(), -1) | 
+-------------------------------+ 
| 2017-08-31 22:31:15.154199000 | 
+-------------------------------+

+------------------------------------+ 
| date_add(now(), interval -1 month) | 
+------------------------------------+ 
| 2017-08-30 22:32:06.244774000 | 
+------------------------------------+

Extract Date Part using Impala SQL

+-------------------------+ 
| extract(day from now()) | 
+-------------------------+ 
| 30 | 
+-------------------------+

+-------------------------+ 
| date_part('day', now()) | 
+-------------------------+ 
| 30 | 
+-------------------------+

Add hours to Current date using Impala SQL

select (now() + interval 1 hour) as "1hour_later";

+-------------------------------+ 
| 1hour_later | 
+-------------------------------+ 
| 2017-09-30 23:37:31.576763000 | 
+-------------------------------+

Get first day of the given timstamp using Impala SQL

+-----------------------+ 
| trunc(now(), 'month') | 
+-----------------------+ 
| 2017-09-01 00:00:00 | 
+-----------------------+

Get last day of the given month using Impala SQL

+----------------------------------------+ 
| trunc(now(), 'month') - interval 1 day | 
+----------------------------------------+ 
| 2017-08-31 00:00:00 | 
+----------------------------------------+

Convert timestamp to date format using Imapal SQL

+----------------+ 
| to_date(now()) | 
+----------------+ 
| 2017-09-30 | 
+----------------+

Convert Timestamp to YYYYMMDD format in Impala SQL

Impala Impala Date format example:

+--------------------------------------------------+ 
| from_unixtime(unix_timestamp(now()), 'yyyymmdd') | 
+--------------------------------------------------+ 
| 20170930 | 
+--------------------------------------------------+

Read:

• Cloudera Impala Regular Expression Functions and Examples
• Cloudera Impala Generate Sequence Numbers without UDF
• Netezza ROWNUM Pseudo Column Alternative
• Run Impala SQL Script File Passing argument and Working Example 
• An Introduction to Hadoop Cloudera Impala Architecture
• Commonly used Hadoop Hive Commands