Impala Date and Time Functions

The underlying Impala data type for date and time data is TIMESTAMP, which has both a date and a time portion. Functions that extract a single field, such as hour() or minute(), typically return an integer value. Functions that format the date portion, such as date_add() or to_date(), typically return a string value.

You can also adjust a TIMESTAMP value by adding or subtracting an INTERVAL expression. See TIMESTAMP Data Type for details. INTERVAL expressions are also allowed as the second argument for the date_add() and date_sub() functions, rather than integers.

Some of these functions are affected by the setting of the -use_local_tz_for_unix_timestamp_conversions startup flag for the impalad daemon. This setting is off by default, meaning that functions such as from_unixtime() and unix_timestamp() consider the input values to always represent the UTC time zone. This setting also applies when you CAST() a BIGINT value to TIMESTAMP, or a TIMESTAMP value to BIGINT. When this setting is enabled, these functions and operations convert to and from values representing the local time zone. See TIMESTAMP Data Type for details about how Impala handles time zone considerations for the TIMESTAMP data type.

Impala supports the following data and time functions:

add_months(timestamp date, int months), add_months(timestamp date, bigint months)

Purpose: Returns the specified date and time plus some number of months.

Return type: timestamp

Usage notes:

Same as months_add(). Available in Impala 1.4 and higher. For compatibility when porting code with vendor extensions.

Examples:

The following examples demonstrate adding months to construct the same day of the month in a different month; how if the current day of the month does not exist in the target month, the last day of that month is substituted; and how a negative argument produces a return value from a previous month.

select now(), add_months(now(), 2);
+-------------------------------+-------------------------------+
| now()                         | add_months(now(), 2)          |
+-------------------------------+-------------------------------+
| 2016-05-31 10:47:00.429109000 | 2016-07-31 10:47:00.429109000 |
+-------------------------------+-------------------------------+

select now(), add_months(now(), 1);
+-------------------------------+-------------------------------+
| now()                         | add_months(now(), 1)          |
+-------------------------------+-------------------------------+
| 2016-05-31 10:47:14.540226000 | 2016-06-30 10:47:14.540226000 |
+-------------------------------+-------------------------------+

select now(), add_months(now(), -1);
+-------------------------------+-------------------------------+
| now()                         | add_months(now(), -1)         |
+-------------------------------+-------------------------------+
| 2016-05-31 10:47:31.732298000 | 2016-04-30 10:47:31.732298000 |
+-------------------------------+-------------------------------+

adddate(timestamp startdate, int days), adddate(timestamp startdate, bigint days),

Purpose: Adds a specified number of days to a TIMESTAMP value. Similar to date_add(), but starts with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.

Return type: timestamp

Examples:

The following examples show how to add a number of days to a TIMESTAMP. The number of days can also be negative, which gives the same effect as the subdate() function.

select now() as right_now, adddate(now(), 30) as now_plus_30;
+-------------------------------+-------------------------------+
| right_now                     | now_plus_30                   |
+-------------------------------+-------------------------------+
| 2016-05-20 10:23:08.640111000 | 2016-06-19 10:23:08.640111000 |
+-------------------------------+-------------------------------+

select now() as right_now, adddate(now(), -15) as now_minus_15;
+-------------------------------+-------------------------------+
| right_now                     | now_minus_15                  |
+-------------------------------+-------------------------------+
| 2016-05-20 10:23:38.214064000 | 2016-05-05 10:23:38.214064000 |
+-------------------------------+-------------------------------+

current_timestamp()

Purpose: Alias for the now() function.

Return type: timestamp

Examples:

select now(), current_timestamp();
+-------------------------------+-------------------------------+
| now()                         | current_timestamp()           |
+-------------------------------+-------------------------------+
| 2016-05-19 16:10:14.237849000 | 2016-05-19 16:10:14.237849000 |
+-------------------------------+-------------------------------+

select current_timestamp() as right_now,
  current_timestamp() + interval 3 hours as in_three_hours;
+-------------------------------+-------------------------------+
| right_now                     | in_three_hours                |
+-------------------------------+-------------------------------+
| 2016-05-19 16:13:20.017117000 | 2016-05-19 19:13:20.017117000 |
+-------------------------------+-------------------------------+

date_add(timestamp startdate, int days), date_add(timestamp startdate, interval_expression)

Purpose: Adds a specified number of days to a TIMESTAMP value. With an INTERVAL expression as the second argument, you can calculate a delta value using other units such as weeks, years, hours, seconds, and so on; see TIMESTAMP Data Type for details.

Return type: timestamp

Examples:

The following example shows the simplest usage, of adding a specified number of days to a TIMESTAMP value:

select now() as right_now, date_add(now(), 7) as next_week;
+-------------------------------+-------------------------------+
| right_now                     | next_week                     |
+-------------------------------+-------------------------------+
| 2016-05-20 11:03:48.687055000 | 2016-05-27 11:03:48.687055000 |
+-------------------------------+-------------------------------+

The following examples show the shorthand notation of an INTERVAL expression, instead of specifying the precise number of days. The INTERVAL notation also lets you work with units smaller than a single day.

select now() as right_now, date_add(now(), interval 3 weeks) as in_3_weeks;
+-------------------------------+-------------------------------+
| right_now                     | in_3_weeks                    |
+-------------------------------+-------------------------------+
| 2016-05-20 11:05:39.173331000 | 2016-06-10 11:05:39.173331000 |
+-------------------------------+-------------------------------+

select now() as right_now, date_add(now(), interval 6 hours) as in_6_hours;
+-------------------------------+-------------------------------+
| right_now                     | in_6_hours                    |
+-------------------------------+-------------------------------+
| 2016-05-20 11:13:51.492536000 | 2016-05-20 17:13:51.492536000 |
+-------------------------------+-------------------------------+

Like all date/time functions that deal with months, date_add() handles nonexistent dates past the end of a month by setting the date to the last day of the month. The following example shows how the nonexistent date April 31st is normalized to April 30th:

select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31st';
+---------------------+
| april_31st          |
+---------------------+
| 2016-04-30 00:00:00 |
+---------------------+

date_part(string, timestamp)

Purpose: Similar to EXTRACT(), with the argument order reversed. Supports the same date and time units as EXTRACT(). For compatibility with SQL code containing vendor extensions.

Return type: int

Examples:

select date_part('year',now()) as current_year;
+--------------+
| current_year |
+--------------+
| 2016         |
+--------------+

select date_part('hour',now()) as hour_of_day;
+-------------+
| hour_of_day |
+-------------+
| 11          |
+-------------+

date_sub(timestamp startdate, int days), date_sub(timestamp startdate, interval_expression)

Purpose: Subtracts a specified number of days from a TIMESTAMP value. With an INTERVAL expression as the second argument, you can calculate a delta value using other units such as weeks, years, hours, seconds, and so on; see TIMESTAMP Data Type for details.

Return type: timestamp

Examples:

The following example shows the simplest usage, of subtracting a specified number of days from a TIMESTAMP value:

select now() as right_now, date_sub(now(), 7) as last_week;
+-------------------------------+-------------------------------+
| right_now                     | last_week                     |
+-------------------------------+-------------------------------+
| 2016-05-20 11:21:30.491011000 | 2016-05-13 11:21:30.491011000 |
+-------------------------------+-------------------------------+

select now() as right_now, date_sub(now(), interval 3 weeks) as 3_weeks_ago;
+-------------------------------+-------------------------------+
| right_now                     | 3_weeks_ago                   |
+-------------------------------+-------------------------------+
| 2016-05-20 11:23:05.176953000 | 2016-04-29 11:23:05.176953000 |
+-------------------------------+-------------------------------+

select now() as right_now, date_sub(now(), interval 6 hours) as 6_hours_ago;
+-------------------------------+-------------------------------+
| right_now                     | 6_hours_ago                   |
+-------------------------------+-------------------------------+
| 2016-05-20 11:23:35.439631000 | 2016-05-20 05:23:35.439631000 |
+-------------------------------+-------------------------------+

select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st';
+---------------------+
| april_31st          |
+---------------------+
| 2016-04-30 00:00:00 |
+---------------------+

datediff(timestamp enddate, timestamp startdate)

Purpose: Returns the number of days between two TIMESTAMP values.

Return type: int

Usage notes:

If the first argument represents a later date than the second argument, the return value is positive. If both arguments represent the same date, the return value is zero. The time portions of the TIMESTAMP values are irrelevant. For example, 11:59 PM on one day and 12:01 on the next day represent a datediff() of -1 because the date/time values represent different days, even though the TIMESTAMP values differ by only 2 minutes.

Examples:

The following example shows how comparing a "late" value with an "earlier" value produces a positive number. In this case, the result is (365 * 5) + 1, because one of the intervening years is a leap year.

select now() as right_now, datediff(now() + interval 5 years, now()) as in_5_years;
+-------------------------------+------------+
| right_now                     | in_5_years |
+-------------------------------+------------+
| 2016-05-20 13:43:55.873826000 | 1826       |
+-------------------------------+------------+

The following examples show how the return value represent the number of days between the associated dates, regardless of the time portion of each TIMESTAMP. For example, different times on the same day produce a date_diff() of 0, regardless of which one is earlier or later. But if the arguments represent different dates, date_diff() returns a non-zero integer value, regardless of the time portions of the dates.

select now() as right_now, datediff(now(), now() + interval 4 hours) as in_4_hours;
+-------------------------------+------------+
| right_now                     | in_4_hours |
+-------------------------------+------------+
| 2016-05-20 13:42:05.302747000 | 0          |
+-------------------------------+------------+

select now() as right_now, datediff(now(), now() - interval 4 hours) as 4_hours_ago;
+-------------------------------+-------------+
| right_now                     | 4_hours_ago |
+-------------------------------+-------------+
| 2016-05-20 13:42:21.134958000 | 0           |
+-------------------------------+-------------+

select now() as right_now, datediff(now(), now() + interval 12 hours) as in_12_hours;
+-------------------------------+-------------+
| right_now                     | in_12_hours |
+-------------------------------+-------------+
| 2016-05-20 13:42:44.765873000 | -1          |
+-------------------------------+-------------+

select now() as right_now, datediff(now(), now() - interval 18 hours) as 18_hours_ago;
+-------------------------------+--------------+
| right_now                     | 18_hours_ago |
+-------------------------------+--------------+
| 2016-05-20 13:54:38.829827000 | 1            |
+-------------------------------+--------------+

day(timestamp date), dayofmonth(timestamp date)

Purpose: Returns the day field from the date portion of a TIMESTAMP. The value represents the day of the month, therefore is in the range 1-31, or less for months without 31 days.

Return type: int

Examples:

The following examples show how the day value corresponds to the day of the month, resetting back to 1 at the start of each month.

select now(), day(now());
+-------------------------------+------------+
| now()                         | day(now()) |
+-------------------------------+------------+
| 2016-05-20 15:01:51.042185000 | 20         |
+-------------------------------+------------+

select now() + interval 11 days, day(now() + interval 11 days);
+-------------------------------+-------------------------------+
| now() + interval 11 days      | day(now() + interval 11 days) |
+-------------------------------+-------------------------------+
| 2016-05-31 15:05:56.843139000 | 31                            |
+-------------------------------+-------------------------------+

select now() + interval 12 days, day(now() + interval 12 days);
+-------------------------------+-------------------------------+
| now() + interval 12 days      | day(now() + interval 12 days) |
+-------------------------------+-------------------------------+
| 2016-06-01 15:06:05.074236000 | 1                             |
+-------------------------------+-------------------------------+

The following examples show how the day value is NULL for nonexistent dates or misformatted date strings.

-- 2016 is a leap year, so it has a Feb. 29.
select day('2016-02-29');
+-------------------+
| day('2016-02-29') |
+-------------------+
| 29                |
+-------------------+

-- 2015 is not a leap year, so Feb. 29 is nonexistent.
select day('2015-02-29');
+-------------------+
| day('2015-02-29') |
+-------------------+
| NULL              |
+-------------------+

-- A string that does not match the expected YYYY-MM-DD format
-- produces an invalid TIMESTAMP, causing day() to return NULL.
select day('2016-02-028');
+--------------------+
| day('2016-02-028') |
+--------------------+
| NULL               |
+--------------------+

dayname(timestamp date)

Purpose: Returns the day field from a TIMESTAMP value, converted to the string corresponding to that day name. The range of return values is 'Sunday' to 'Saturday'. Used in report-generating queries, as an alternative to calling dayofweek() and turning that numeric return value into a string using a CASE expression.

Return type: string

Examples:

The following examples show the day name associated with TIMESTAMP values representing different days.

select now() as right_now,
  dayofweek(now()) as todays_day_of_week,
  dayname(now()) as todays_day_name;
+-------------------------------+--------------------+-----------------+
| right_now                     | todays_day_of_week | todays_day_name |
+-------------------------------+--------------------+-----------------+
| 2016-05-31 10:57:03.953670000 | 3                  | Tuesday         |
+-------------------------------+--------------------+-----------------+

select now() + interval 1 day as tomorrow,
  dayname(now() + interval 1 day) as tomorrows_day_name;
+-------------------------------+--------------------+
| tomorrow                      | tomorrows_day_name |
+-------------------------------+--------------------+
| 2016-06-01 10:58:53.945761000 | Wednesday          |
+-------------------------------+--------------------+

dayofweek(timestamp date)

Purpose: Returns the day field from the date portion of a TIMESTAMP, corresponding to the day of the week. The range of return values is 1 (Sunday) to 7 (Saturday).

Return type: int

Examples:

select now() as right_now,
  dayofweek(now()) as todays_day_of_week,
  dayname(now()) as todays_day_name;
+-------------------------------+--------------------+-----------------+
| right_now                     | todays_day_of_week | todays_day_name |
+-------------------------------+--------------------+-----------------+
| 2016-05-31 10:57:03.953670000 | 3                  | Tuesday         |
+-------------------------------+--------------------+-----------------+

dayofyear(timestamp date)

Purpose: Returns the day field from a TIMESTAMP value, corresponding to the day of the year. The range of return values is 1 (January 1) to 366 (December 31 of a leap year).

Return type: int

Examples:

The following examples show return values from the dayofyear() function. The same date in different years returns a different day number for all dates after February 28, because 2016 is a leap year while 2015 is not a leap year.

select now() as right_now,
  dayofyear(now()) as today_day_of_year;
+-------------------------------+-------------------+
| right_now                     | today_day_of_year |
+-------------------------------+-------------------+
| 2016-05-31 11:05:48.314932000 | 152               |
+-------------------------------+-------------------+

select now() - interval 1 year as last_year,
  dayofyear(now() - interval 1 year) as year_ago_day_of_year;
+-------------------------------+----------------------+
| last_year                     | year_ago_day_of_year |
+-------------------------------+----------------------+
| 2015-05-31 11:07:03.733689000 | 151                  |
+-------------------------------+----------------------+

days_add(timestamp startdate, int days), days_add(timestamp startdate, bigint days)

Purpose: Adds a specified number of days to a TIMESTAMP value. Similar to date_add(), but starts with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.

Return type: timestamp

Examples:

select now() as right_now, days_add(now(), 31) as 31_days_later;
+-------------------------------+-------------------------------+
| right_now                     | 31_days_later                 |
+-------------------------------+-------------------------------+
| 2016-05-31 11:12:32.216764000 | 2016-07-01 11:12:32.216764000 |
+-------------------------------+-------------------------------+

days_sub(timestamp startdate, int days), days_sub(timestamp startdate, bigint days)

Purpose: Subtracts a specified number of days from a TIMESTAMP value. Similar to date_sub(), but starts with an actual TIMESTAMP value instead of a string that is converted to a TIMESTAMP.

Return type: timestamp

Examples:

select now() as right_now, days_sub(now(), 31) as 31_days_ago;
+-------------------------------+-------------------------------+
| right_now                     | 31_days_ago                   |
+-------------------------------+-------------------------------+
| 2016-05-31 11:13:42.163905000 | 2016-04-30 11:13:42.163905000 |
+-------------------------------+-------------------------------+

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

Purpose: Returns one of the numeric date or time fields from a TIMESTAMP value.

Unit argument: The unit string can be one of year, month, day, hour, minute, second, or millisecond. This argument value is case-insensitive.

In Impala 2.0 and higher, you can use special syntax rather than a regular function call, for compatibility with code that uses the SQL-99 format with the FROM keyword. With this style, the unit names are identifiers rather than STRING literals. For example, the following calls are both equivalent:

extract(year from now());
extract(now(), "year");

Usage notes:

Typically used in GROUP BY queries to arrange results by hour, day, month, and so on. You can also use this function in an INSERT ... SELECT into a partitioned table to split up TIMESTAMP values into individual parts, if the partitioned table has separate partition key columns representing year, month, day, and so on. If you need to divide by more complex units of time, such as by week or by quarter, use the TRUNC() function instead.

Return type: int

Examples:

select now() as right_now,
  extract(year from now()) as this_year,
  extract(month from now()) as this_month;
+-------------------------------+-----------+------------+
| right_now                     | this_year | this_month |
+-------------------------------+-----------+------------+
| 2016-05-31 11:18:43.310328000 | 2016      | 5          |
+-------------------------------+-----------+------------+

select now() as right_now,
  extract(day from now()) as this_day,
  extract(hour from now()) as this_hour;
+-------------------------------+----------+-----------+
| right_now                     | this_day | this_hour |
+-------------------------------+----------+-----------+
| 2016-05-31 11:19:24.025303000 | 31       | 11        |
+-------------------------------+----------+-----------+

from_unixtime(bigint unixtime[, string format])

Purpose: Converts the number of seconds from the Unix epoch to the specified time into a string in the local time zone.

Return type: string

In Impala 2.2.0 and higher, built-in functions that accept or return integers representing TIMESTAMP values use the BIGINT type for parameters and return values, rather than INT. This change lets the date and time functions avoid an overflow error that would otherwise occur on January 19th, 2038 (known as the "Year 2038 problem" or "Y2K38 problem"). This change affects the from_unixtime() and unix_timestamp() functions. You might need to change application code that interacts with these functions, change the types of columns that store the return values, or add CAST() calls to SQL statements that call these functions.

Usage notes:

The format string accepts the variations allowed for the TIMESTAMP data type: date plus time, date by itself, time by itself, and optional fractional seconds for the time. See TIMESTAMP Data Type for details.

Currently, the format string is case-sensitive, especially to distinguish m for minutes and M for months. In Impala 1.3 and later, you can switch the order of elements, use alternative separator characters, and use a different number of placeholders for each unit. Adding more instances of y, d, H, and so on produces output strings zero-padded to the requested number of characters. The exception is M for months, where M produces a non-padded value such as 3, MM produces a zero-padded value such as 03, MMM produces an abbreviated month name such as Mar, and sequences of 4 or more M are not allowed. A date string including all fields could be "yyyy-MM-dd HH:mm:ss.SSSSSS", "dd/MM/yyyy HH:mm:ss.SSSSSS", "MMM dd, yyyy HH.mm.ss (SSSSSS)" or other combinations of placeholders and separator characters.

The way this function deals with time zones when converting to or from TIMESTAMP values is affected by the --use_local_tz_for_unix_timestamp_conversions startup flag for the impalad daemon. See TIMESTAMP Data Type for details about how Impala handles time zone considerations for the TIMESTAMP data type.

Examples:

select from_unixtime(1392394861,"yyyy-MM-dd HH:mm:ss.SSSS");
+-------------------------------------------------------+
| from_unixtime(1392394861, 'yyyy-mm-dd hh:mm:ss.ssss') |
+-------------------------------------------------------+
| 2014-02-14 16:21:01.0000                              |
+-------------------------------------------------------+

select from_unixtime(1392394861,"yyyy-MM-dd");
+-----------------------------------------+
| from_unixtime(1392394861, 'yyyy-mm-dd') |
+-----------------------------------------+
| 2014-02-14                              |
+-----------------------------------------+

select from_unixtime(1392394861,"HH:mm:ss.SSSS");
+--------------------------------------------+
| from_unixtime(1392394861, 'hh:mm:ss.ssss') |
+--------------------------------------------+
| 16:21:01.0000                              |
+--------------------------------------------+

select from_unixtime(1392394861,"HH:mm:ss");
+---------------------------------------+
| from_unixtime(1392394861, 'hh:mm:ss') |
+---------------------------------------+
| 16:21:01                              |
+---------------------------------------+

unix_timestamp() and from_unixtime() are often used in combination to convert a TIMESTAMP value into a particular string format. For example:

select from_unixtime(unix_timestamp(now() + interval 3 days),
  'yyyy/MM/dd HH:mm') as yyyy_mm_dd_hh_mm;
+------------------+
| yyyy_mm_dd_hh_mm |
+------------------+
| 2016/06/03 11:38 |
+------------------+

from_utc_timestamp(timestamp, string timezone)

Purpose: Converts a specified UTC timestamp value into the appropriate value for a specified time zone.

Return type: timestamp

Usage notes: Often used to translate UTC time zone data stored in a table back to the local date and time for reporting. The opposite of the to_utc_timestamp() function.

Examples:

See discussion of time zones in TIMESTAMP Data Type for information about using this function for conversions between the local time zone and UTC.

The following example shows how when TIMESTAMP values representing the UTC time zone are stored in a table, a query can display the equivalent local date and time for a different time zone.

with t1 as (select cast('2016-06-02 16:25:36.116143000' as timestamp) as utc_datetime)
  select utc_datetime as 'Date/time in Greenwich UK',
    from_utc_timestamp(utc_datetime, 'PDT')
      as 'Equivalent in California USA'
  from t1;
+-------------------------------+-------------------------------+
| date/time in greenwich uk     | equivalent in california usa  |
+-------------------------------+-------------------------------+
| 2016-06-02 16:25:36.116143000 | 2016-06-02 09:25:36.116143000 |
+-------------------------------+-------------------------------+

The following example shows that for a date and time when daylight savings is in effect (PDT), the UTC time is 7 hours ahead of the local California time; while when daylight savings is not in effect (PST), the UTC time is 8 hours ahead of the local California time.

select now() as local_datetime,
  to_utc_timestamp(now(), 'PDT') as utc_datetime;
+-------------------------------+-------------------------------+
| local_datetime                | utc_datetime                  |
+-------------------------------+-------------------------------+
| 2016-05-31 11:50:02.316883000 | 2016-05-31 18:50:02.316883000 |
+-------------------------------+-------------------------------+

select '2016-01-05' as local_datetime,
  to_utc_timestamp('2016-01-05', 'PST') as utc_datetime;
+----------------+---------------------+
| local_datetime | utc_datetime        |
+----------------+---------------------+
| 2016-01-05     | 2016-01-05 08:00:00 |
+----------------+---------------------+

hour(timestamp date)

Purpose: Returns the hour field from a TIMESTAMP field.

Return type: int

Examples:

select now() as right_now, hour(now()) as current_hour;
+-------------------------------+--------------+
| right_now                     | current_hour |
+-------------------------------+--------------+
| 2016-06-01 14:14:12.472846000 | 14           |
+-------------------------------+--------------+

select now() + interval 12 hours as 12_hours_from_now,
  hour(now() + interval 12 hours) as hour_in_12_hours;
+-------------------------------+-------------------+
| 12_hours_from_now             | hour_in_12_hours  |
+-------------------------------+-------------------+
| 2016-06-02 02:15:32.454750000 | 2                 |
+-------------------------------+-------------------+

hours_add(timestamp date, int hours), hours_add(timestamp date, bigint hours)

Purpose: Returns the specified date and time plus some number of hours.

Return type: timestamp

Examples:

select now() as right_now,
  hours_add(now(), 12) as in_12_hours;
+-------------------------------+-------------------------------+
| right_now                     | in_12_hours                   |
+-------------------------------+-------------------------------+
| 2016-06-01 14:19:48.948107000 | 2016-06-02 02:19:48.948107000 |
+-------------------------------+-------------------------------+

hours_sub(timestamp date, int hours), hours_sub(timestamp date, bigint hours)

Purpose: Returns the specified date and time minus some number of hours.

Return type: timestamp

Examples:

select now() as right_now,
  hours_sub(now(), 18) as 18_hours_ago;
+-------------------------------+-------------------------------+
| right_now                     | 18_hours_ago                  |
+-------------------------------+-------------------------------+
| 2016-06-01 14:23:13.868150000 | 2016-05-31 20:23:13.868150000 |
+-------------------------------+-------------------------------+

microseconds_add(timestamp date, int microseconds), microseconds_add(timestamp date, bigint microseconds)

Purpose: Returns the specified date and time plus some number of microseconds.

Return type: timestamp

Examples:

select now() as right_now,
  microseconds_add(now(), 500000) as half_a_second_from_now;
+-------------------------------+-------------------------------+
| right_now                     | half_a_second_from_now        |
+-------------------------------+-------------------------------+
| 2016-06-01 14:25:11.455051000 | 2016-06-01 14:25:11.955051000 |
+-------------------------------+-------------------------------+

microseconds_sub(timestamp date, int microseconds), microseconds_sub(timestamp date, bigint microseconds)

Purpose: Returns the specified date and time minus some number of microseconds.

Return type: timestamp

Examples:

select now() as right_now,
  microseconds_sub(now(), 500000) as half_a_second_ago;
+-------------------------------+-------------------------------+
| right_now                     | half_a_second_ago             |
+-------------------------------+-------------------------------+
| 2016-06-01 14:26:16.509990000 | 2016-06-01 14:26:16.009990000 |
+-------------------------------+-------------------------------+

milliseconds_add(timestamp date, int milliseconds), milliseconds_add(timestamp date, bigint milliseconds)

Purpose: Returns the specified date and time plus some number of milliseconds.

Return type: timestamp

Examples:

select now() as right_now,
  milliseconds_add(now(), 1500) as 1_point_5_seconds_from_now;
+-------------------------------+-------------------------------+
| right_now                     | 1_point_5_seconds_from_now    |
+-------------------------------+-------------------------------+
| 2016-06-01 14:30:30.067366000 | 2016-06-01 14:30:31.567366000 |
+-------------------------------+-------------------------------+

milliseconds_sub(timestamp date, int milliseconds), milliseconds_sub(timestamp date, bigint milliseconds)

Purpose: Returns the specified date and time minus some number of milliseconds.

Return type: timestamp

Examples:

select now() as right_now,
  milliseconds_sub(now(), 1500) as 1_point_5_seconds_ago;
+-------------------------------+-------------------------------+
| right_now                     | 1_point_5_seconds_ago         |
+-------------------------------+-------------------------------+
| 2016-06-01 14:30:53.467140000 | 2016-06-01 14:30:51.967140000 |
+-------------------------------+-------------------------------+

minute(timestamp date)

Purpose: Returns the minute field from a TIMESTAMP value.

Return type: int

Examples:

select now() as right_now, minute(now()) as current_minute;
+-------------------------------+----------------+
| right_now                     | current_minute |
+-------------------------------+----------------+
| 2016-06-01 14:34:08.051702000 | 34             |
+-------------------------------+----------------+

minutes_add(timestamp date, int minutes), minutes_add(timestamp date, bigint minutes)

Purpose: Returns the specified date and time plus some number of minutes.

Return type: timestamp

Examples:

select now() as right_now, minutes_add(now(), 90) as 90_minutes_from_now;
+-------------------------------+-------------------------------+
| right_now                     | 90_minutes_from_now           |
+-------------------------------+-------------------------------+
| 2016-06-01 14:36:04.887095000 | 2016-06-01 16:06:04.887095000 |
+-------------------------------+-------------------------------+

minutes_sub(timestamp date, int minutes), minutes_sub(timestamp date, bigint minutes)

Purpose: Returns the specified date and time minus some number of minutes.

Return type: timestamp

Examples:

select now() as right_now, minutes_sub(now(), 90) as 90_minutes_ago;
+-------------------------------+-------------------------------+
| right_now                     | 90_minutes_ago                |
+-------------------------------+-------------------------------+
| 2016-06-01 14:36:32.643061000 | 2016-06-01 13:06:32.643061000 |
+-------------------------------+-------------------------------+

month(timestamp date)

Purpose: Returns the month field, represented as an integer, from the date portion of a TIMESTAMP.

Return type: int

Examples:

select now() as right_now, month(now()) as current_month;
+-------------------------------+---------------+
| right_now                     | current_month |
+-------------------------------+---------------+
| 2016-06-01 14:43:37.141542000 | 6             |
+-------------------------------+---------------+

months_add(timestamp date, int months), months_add(timestamp date, bigint months)

Purpose: Returns the specified date and time plus some number of months.

Return type: timestamp

Examples:

The following example shows the effects of adding some number of months to a TIMESTAMP value, using both the months_add() function and its add_months() alias. These examples use trunc() to strip off the time portion and leave just the date.

with t1 as (select trunc(now(), 'dd') as today)
  select today, months_add(today,1) as next_month from t1;
+---------------------+---------------------+
| today               | next_month          |
+---------------------+---------------------+
| 2016-05-19 00:00:00 | 2016-06-19 00:00:00 |
+---------------------+---------------------+

with t1 as (select trunc(now(), 'dd') as today)
  select today, add_months(today,1) as next_month from t1;
+---------------------+---------------------+
| today               | next_month          |
+---------------------+---------------------+
| 2016-05-19 00:00:00 | 2016-06-19 00:00:00 |
+---------------------+---------------------+

The following examples show how if months_add() would return a nonexistent date, due to different months having different numbers of days, the function returns a TIMESTAMP from the last day of the relevant month. For example, adding one month to January 31 produces a date of February 29th in the year 2016 (a leap year), and February 28th in the year 2015 (a non-leap year).

with t1 as (select cast('2016-01-31' as timestamp) as jan_31)
  select jan_31, months_add(jan_31,1) as feb_31 from t1;
+---------------------+---------------------+
| jan_31              | feb_31              |
+---------------------+---------------------+
| 2016-01-31 00:00:00 | 2016-02-29 00:00:00 |
+---------------------+---------------------+

with t1 as (select cast('2015-01-31' as timestamp) as jan_31)
  select jan_31, months_add(jan_31,1) as feb_31 from t1;
+---------------------+---------------------+
| jan_31              | feb_31              |
+---------------------+---------------------+
| 2015-01-31 00:00:00 | 2015-02-28 00:00:00 |
+---------------------+---------------------+

months_sub(timestamp date, int months), months_sub(timestamp date, bigint months)

Purpose: Returns the specified date and time minus some number of months.

Return type: timestamp

Examples:

with t1 as (select trunc(now(), 'dd') as today)
  select today, months_sub(today,1) as last_month from t1;
+---------------------+---------------------+
| today               | last_month          |
+---------------------+---------------------+
| 2016-06-01 00:00:00 | 2016-05-01 00:00:00 |
+---------------------+---------------------+

nanoseconds_add(timestamp date, int nanoseconds), nanoseconds_add(timestamp date, bigint nanoseconds)

Purpose: Returns the specified date and time plus some number of nanoseconds.

Return type: timestamp

Examples:

select now() as right_now, nanoseconds_add(now(), 1) as 1_nanosecond_later;
+-------------------------------+-------------------------------+
| right_now                     | 1_nanosecond_later            |
+-------------------------------+-------------------------------+
| 2016-06-01 15:42:00.361026000 | 2016-06-01 15:42:00.361026001 |
+-------------------------------+-------------------------------+

-- 1 billion nanoseconds = 1 second.
select now() as right_now, nanoseconds_add(now(), 1e9) as 1_second_later;
+-------------------------------+-------------------------------+
| right_now                     | 1_second_later                |
+-------------------------------+-------------------------------+
| 2016-06-01 15:42:52.926706000 | 2016-06-01 15:42:53.926706000 |
+-------------------------------+-------------------------------+

nanoseconds_sub(timestamp date, int nanoseconds), nanoseconds_sub(timestamp date, bigint nanoseconds)

Purpose: Returns the specified date and time minus some number of nanoseconds.

Return type: timestamp

select now() as right_now, nanoseconds_sub(now(), 1) as 1_nanosecond_earlier;
+-------------------------------+-------------------------------+
| right_now                     | 1_nanosecond_earlier          |
+-------------------------------+-------------------------------+
| 2016-06-01 15:44:14.355837000 | 2016-06-01 15:44:14.355836999 |
+-------------------------------+-------------------------------+

-- 1 billion nanoseconds = 1 second.
select now() as right_now, nanoseconds_sub(now(), 1e9) as 1_second_earlier;
+-------------------------------+-------------------------------+
| right_now                     | 1_second_earlier              |
+-------------------------------+-------------------------------+
| 2016-06-01 15:44:54.474929000 | 2016-06-01 15:44:53.474929000 |
+-------------------------------+-------------------------------+

now()

Purpose: Returns the current date and time (in the local time zone) as a TIMESTAMP value.

Return type: timestamp

Usage notes:

To find a date/time value in the future or the past relative to the current date and time, add or subtract an INTERVAL expression to the return value of now(). See TIMESTAMP Data Type for examples.

To produce a TIMESTAMP representing the current date and time that can be shared or stored without interoperability problems due to time zone differences, use the to_utc_timestamp() function and specify the time zone of the server. When TIMESTAMP data is stored in UTC form, any application that queries those values can convert them to the appropriate local time zone by calling the inverse function, from_utc_timestamp().

Any references to the now() function are evaluated at the start of a query. All calls to now() within the same query return the same value, and the value does not depend on how long the query takes.

Examples:

select now() as 'Current time in California USA',
  to_utc_timestamp(now(), 'PDT') as 'Current time in Greenwich UK';
+--------------------------------+-------------------------------+
| current time in california usa | current time in greenwich uk  |
+--------------------------------+-------------------------------+
| 2016-06-01 15:52:08.980072000  | 2016-06-01 22:52:08.980072000 |
+--------------------------------+-------------------------------+

select now() as right_now,
  now() + interval 1 day as tomorrow,
  now() + interval 1 week - interval 3 hours as almost_a_week_from_now;
+-------------------------------+-------------------------------+-------------------------------+
| right_now                     | tomorrow                      | almost_a_week_from_now        |
+-------------------------------+-------------------------------+-------------------------------+
| 2016-06-01 15:55:39.671690000 | 2016-06-02 15:55:39.671690000 | 2016-06-08 12:55:39.671690000 |
+-------------------------------+-------------------------------+-------------------------------+

second(string date)

Purpose: Returns the second field from a date represented as a string.

Return type: int

Examples:

select now() as right_now,
  second(now()) as seconds_in_current_minute;
+-------------------------------+---------------------------+
| right_now                     | seconds_in_current_minute |
+-------------------------------+---------------------------+
| 2016-06-01 16:03:57.006603000 | 57                        |
+-------------------------------+---------------------------+

seconds_add(timestamp date, int seconds), seconds_add(timestamp date, bigint seconds)

Purpose: Returns the specified date and time plus some number of seconds.

Return type: timestamp

Examples:

select now() as right_now,
  seconds_add(now(), 10) as 10_seconds_from_now;
+-------------------------------+-------------------------------+
| right_now                     | 10_seconds_from_now           |
+-------------------------------+-------------------------------+
| 2016-06-01 16:05:21.573935000 | 2016-06-01 16:05:31.573935000 |
+-------------------------------+-------------------------------+

seconds_sub(timestamp date, int seconds), seconds_sub(timestamp date, bigint seconds)

Purpose: Returns the specified date and time minus some number of seconds.

Return type: timestamp

Examples:

select now() as right_now,
  seconds_sub(now(), 10) as 10_seconds_ago;
+-------------------------------+-------------------------------+
| right_now                     | 10_seconds_ago                |
+-------------------------------+-------------------------------+
| 2016-06-01 16:06:03.467931000 | 2016-06-01 16:05:53.467931000 |
+-------------------------------+-------------------------------+

subdate(timestamp startdate, int days), subdate(timestamp startdate, bigint days),

Return type: timestamp

Examples:

The following examples show how to subtract a number of days from a TIMESTAMP. The number of days can also be negative, which gives the same effect as the adddate() function.

select now() as right_now, subdate(now(), 30) as now_minus_30;
+-------------------------------+-------------------------------+
| right_now                     | now_minus_30                  |
+-------------------------------+-------------------------------+
| 2016-05-20 11:00:15.084991000 | 2016-04-20 11:00:15.084991000 |
+-------------------------------+-------------------------------+

select now() as right_now, subdate(now(), -15) as now_plus_15;
+-------------------------------+-------------------------------+
| right_now                     | now_plus_15                   |
+-------------------------------+-------------------------------+
| 2016-05-20 11:00:44.766091000 | 2016-06-04 11:00:44.766091000 |
+-------------------------------+-------------------------------+

to_date(timestamp)

Purpose: Returns a string representation of the date field from a timestamp value.

Return type: string

Examples:

select now() as right_now,
  concat('The date today is ',to_date(now()),'.') as date_announcement;
+-------------------------------+-------------------------------+
| right_now                     | date_announcement             |
+-------------------------------+-------------------------------+
| 2016-06-01 16:30:36.890325000 | The date today is 2016-06-01. |
+-------------------------------+-------------------------------+

to_utc_timestamp(timestamp, string timezone)

Purpose: Converts a specified timestamp value in a specified time zone into the corresponding value for the UTC time zone.

Return type: timestamp

Usage notes:

Often used in combination with the now() function, to translate local date and time values to the UTC time zone for consistent representation on disk. The opposite of the from_utc_timestamp() function.

See discussion of time zones in TIMESTAMP Data Type for information about using this function for conversions between the local time zone and UTC.

Examples:

The simplest use of this function is to turn a local date/time value to one with the standardized UTC time zone. Because the time zone specifier is not saved as part of the Impala TIMESTAMP value, all applications that refer to such data must agree in advance which time zone the values represent. If different parts of the ETL cycle, or different instances of the application, occur in different time zones, the ideal reference point is to convert all TIMESTAMP values to UTC for storage.

select now() as 'Current time in California USA',
  to_utc_timestamp(now(), 'PDT') as 'Current time in Greenwich UK';
+--------------------------------+-------------------------------+
| current time in california usa | current time in greenwich uk  |
+--------------------------------+-------------------------------+
| 2016-06-01 15:52:08.980072000  | 2016-06-01 22:52:08.980072000 |
+--------------------------------+-------------------------------+

Once a value is converted to the UTC time zone by to_utc_timestamp(), it can be converted back to the local time zone with from_utc_timestamp(). You can combine these functions using different time zone identifiers to convert a TIMESTAMP between any two time zones. This example starts with a TIMESTAMP value representing Pacific Daylight Time, converts it to UTC, and converts it to the equivalent value in Eastern Daylight Time.

select now() as 'Current time in California USA',
  from_utc_timestamp
  (
    to_utc_timestamp(now(), 'PDT'),
    'EDT'
  ) as 'Current time in New York, USA';
+--------------------------------+-------------------------------+
| current time in california usa | current time in new york, usa |
+--------------------------------+-------------------------------+
| 2016-06-01 18:14:12.743658000  | 2016-06-01 21:14:12.743658000 |
+--------------------------------+-------------------------------+

trunc(timestamp, string unit)

Purpose: Strips off fields from a TIMESTAMP value.

Unit argument: The unit argument value is case-sensitive. This argument string can be one of:

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y: Year.
Q: Quarter.
MONTH, MON, MM, RM: Month.
WW, W: Same day of the week as the first day of the month.
DDD, DD, J: Day.
DAY, DY, D: Starting day of the week. (Not necessarily the current day.)
HH, HH12, HH24: Hour. A TIMESTAMP value truncated to the hour is always represented in 24-hour notation, even for the HH12 argument string.
MI: Minute.

Usage notes:

Typically used in GROUP BY queries to aggregate results from the same hour, day, week, month, quarter, and so on. You can also use this function in an INSERT ... SELECT into a partitioned table to divide TIMESTAMP values into the correct partition.

Because the return value is a TIMESTAMP, if you cast the result of TRUNC() to STRING, you will often see zeroed-out portions such as 00:00:00 in the time field. If you only need the individual units such as hour, day, month, or year, use the EXTRACT() function instead. If you need the individual units from a truncated TIMESTAMP value, run the TRUNCATE() function on the original value, then run EXTRACT() on the result.

Return type: timestamp

Examples:

The following example shows how the argument 'Q' returns a TIMESTAMP representing the beginning of the appropriate calendar quarter. This return value is the same for input values that could be separated by weeks or months. If you stored the trunc() result in a partition key column, the table would have four partitions per year.

select now() as right_now, trunc(now(), 'Q') as current_quarter;
+-------------------------------+---------------------+
| right_now                     | current_quarter     |
+-------------------------------+---------------------+
| 2016-06-01 18:32:02.097202000 | 2016-04-01 00:00:00 |
+-------------------------------+---------------------+

select now() + interval 2 weeks as 2_weeks_from_now,
  trunc(now() + interval 2 weeks, 'Q') as still_current_quarter;
+-------------------------------+-----------------------+
| 2_weeks_from_now              | still_current_quarter |
+-------------------------------+-----------------------+
| 2016-06-15 18:36:19.584257000 | 2016-04-01 00:00:00   |
+-------------------------------+-----------------------+

unix_timestamp(), unix_timestamp(string datetime), unix_timestamp(string datetime, string format), unix_timestamp(timestamp datetime)

Purpose: Returns a Unix time, which is a number of seconds elapsed since '1970-01-01 00:00:00' UTC. If called with no argument, the current date and time is converted to its Unix time. If called with arguments, the first argument represented as the TIMESTAMP or STRING is converted to its Unix time.

Return type: bigint

Usage notes:

See from_unixtime() for details about the patterns you can use in the format string to represent the position of year, month, day, and so on in the date string. In Impala 1.3 and higher, you have more flexibility to switch the positions of elements and use different separator characters. In CDH 5.4.3 and higher, you can include a trailing uppercase Z qualifier to indicate "Zulu" time, a synonym for UTC.

unix_timestamp() and from_unixtime() are often used in combination to convert a TIMESTAMP value into a particular string format. For example:

select from_unixtime(unix_timestamp(now() + interval 3 days),
  'yyyy/MM/dd HH:mm') as yyyy_mm_dd_hh_mm;
+------------------+
| yyyy_mm_dd_hh_mm |
+------------------+
| 2016/06/03 11:38 |
+------------------+

Examples:

The following examples show different ways of turning the same date and time into an integer value. A format string that Impala recognizes by default is interpreted as a UTC date and time. The trailing Z is a confirmation that the timezone is UTC. If the date and time string is formatted differently, a second argument specifies the position and units for each of the date and time values.

select unix_timestamp('2015-05-15 12:00:00');
+---------------------------------------+
| unix_timestamp('2015-05-15 12:00:00') |
+---------------------------------------+
| 1431691200                            |
+---------------------------------------+

select unix_timestamp('2015-05-15 12:00:00Z')
+----------------------------------------+
| unix_timestamp('2015-05-15 12:00:00z') |
+----------------------------------------+
| 1431691200                             |
+----------------------------------------+

select unix_timestamp
(
  'May 15, 2015 12:00:00',
  'MMM dd, yyyy HH:mm:ss'
) as may_15_month_day_year;
+-----------------------+
| may_15_month_day_year |
+-----------------------+
| 1431691200            |
+-----------------------+

-- 2 ways of expressing the same date and time but in a different timezone.
-- The resulting integer is different from the previous examples.

select unix_timestamp
(
  '2015-05-15 12:00:00-07:00',
  'yyyy-MM-dd HH:mm:ss-hh:mm'
) as may_15_year_month_day;
+-----------------------+
| may_15_year_month_day |
+-----------------------+
| 1431716400            |
+-----------------------+

select unix_timestamp
  (to_utc_timestamp(
    '2015-05-15 12:00:00',
    'PDT')
  ) as may_15_pdt;
+------------+
| may_15_pdt |
+------------+
| 1431716400 |
+------------+

weekofyear(timestamp date)

Purpose: Returns the corresponding week (1-53) from the date portion of a TIMESTAMP.

Return type: int

Examples:

select now() as right_now, weekofyear(now()) as this_week;
+-------------------------------+-----------+
| right_now                     | this_week |
+-------------------------------+-----------+
| 2016-06-01 22:40:06.763771000 | 22        |
+-------------------------------+-----------+

select now() + interval 2 weeks as in_2_weeks,
  weekofyear(now() + interval 2 weeks) as week_after_next;
+-------------------------------+-----------------+
| in_2_weeks                    | week_after_next |
+-------------------------------+-----------------+
| 2016-06-15 22:41:22.098823000 | 24              |
+-------------------------------+-----------------+

weeks_add(timestamp date, int weeks), weeks_add(timestamp date, bigint weeks)

Purpose: Returns the specified date and time plus some number of weeks.

Return type: timestamp

Examples:

select now() as right_now, weeks_add(now(), 2) as week_after_next;
+-------------------------------+-------------------------------+
| right_now                     | week_after_next               |
+-------------------------------+-------------------------------+
| 2016-06-01 22:43:20.973834000 | 2016-06-15 22:43:20.973834000 |
+-------------------------------+-------------------------------+

weeks_sub(timestamp date, int weeks), weeks_sub(timestamp date, bigint weeks)

Purpose: Returns the specified date and time minus some number of weeks.

Return type: timestamp

Examples:

select now() as right_now, weeks_sub(now(), 2) as week_before_last;
+-------------------------------+-------------------------------+
| right_now                     | week_before_last              |
+-------------------------------+-------------------------------+
| 2016-06-01 22:44:21.291913000 | 2016-05-18 22:44:21.291913000 |
+-------------------------------+-------------------------------+

year(timestamp date)

Purpose: Returns the year field from the date portion of a TIMESTAMP.

Return type: int

Examples:

select now() as right_now, year(now()) as this_year;
+-------------------------------+-----------+
| right_now                     | this_year |
+-------------------------------+-----------+
| 2016-06-01 22:46:23.647925000 | 2016      |
+-------------------------------+-----------+

years_add(timestamp date, int years), years_add(timestamp date, bigint years)

Purpose: Returns the specified date and time plus some number of years.

Return type: timestamp

Examples:

select now() as right_now, years_add(now(), 1) as next_year;
+-------------------------------+-------------------------------+
| right_now                     | next_year                     |
+-------------------------------+-------------------------------+
| 2016-06-01 22:47:45.556851000 | 2017-06-01 22:47:45.556851000 |
+-------------------------------+-------------------------------+

The following example shows how if the equivalent date does not exist in the year of the result due to a leap year, the date is changed to the last day of the appropriate month.

-- Spoiler alert: there is no Feb. 29, 2017
select cast('2016-02-29' as timestamp) as feb_29_2016,
  years_add('2016-02-29', 1) as feb_29_2017;
+---------------------+---------------------+
| feb_29_2016         | feb_29_2017         |
+---------------------+---------------------+
| 2016-02-29 00:00:00 | 2017-02-28 00:00:00 |
+---------------------+---------------------+

years_sub(timestamp date, int years), years_sub(timestamp date, bigint years)

Purpose: Returns the specified date and time minus some number of years.

Return type: timestamp

Examples:

select now() as right_now, years_sub(now(), 1) as last_year;
+-------------------------------+-------------------------------+
| right_now                     | last_year                     |
+-------------------------------+-------------------------------+
| 2016-06-01 22:48:11.851780000 | 2015-06-01 22:48:11.851780000 |
+-------------------------------+-------------------------------+

The following example shows how if the equivalent date does not exist in the year of the result due to a leap year, the date is changed to the last day of the appropriate month.

-- Spoiler alert: there is no Feb. 29, 2015
select cast('2016-02-29' as timestamp) as feb_29_2016,
  years_sub('2016-02-29', 1) as feb_29_2015;
+---------------------+---------------------+
| feb_29_2016         | feb_29_2015         |
+---------------------+---------------------+
| 2016-02-29 00:00:00 | 2015-02-28 00:00:00 |
+---------------------+---------------------+

Type Conversion Functions

Conditional Functions