http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/75c46918/docs/build/html/topics/impala_datetime_functions.html ---------------------------------------------------------------------- diff --git a/docs/build/html/topics/impala_datetime_functions.html b/docs/build/html/topics/impala_datetime_functions.html new file mode 100644 index 0000000..222ae8c --- /dev/null +++ b/docs/build/html/topics/impala_datetime_functions.html @@ -0,0 +1,2657 @@ +<!DOCTYPE html + SYSTEM "about:legacy-compat"> +<html lang="en"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><meta charset="UTF-8"><meta name="copyright" content="(C) Copyright 2017"><meta name="DC.rights.owner" content="(C) Copyright 2017"><meta name="DC.Type" content="concept"><meta name="DC.Relation" scheme="URI" content="../topics/impala_functions.html"><meta name="prodname" content="Impala"><meta name="prodname" content="Impala"><meta name="version" content="Impala 2.8.x"><meta name="version" content="Impala 2.8.x"><meta name="DC.Format" content="XHTML"><meta name="DC.Identifier" content="datetime_functions"><link rel="stylesheet" type="text/css" href="../commonltr.css"><title>Impala Date and Time Functions</title></head><body id="datetime_functions"><main role="main"><article role="article" aria-labelledby="ariaid-title1"> + + <h1 class="title topictitle1" id="ariaid-title1">Impala Date and Time Functions</h1> + + + + <div class="body conbody"> + + <p class="p"> + The underlying Impala data type for date and time data is + <code class="ph codeph"><a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP</a></code>, which has both a date and a + time portion. Functions that extract a single field, such as <code class="ph codeph">hour()</code> or + <code class="ph codeph">minute()</code>, typically return an integer value. Functions that format the date portion, such as + <code class="ph codeph">date_add()</code> or <code class="ph codeph">to_date()</code>, typically return a string value. + </p> + + <p class="p"> + You can also adjust a <code class="ph codeph">TIMESTAMP</code> value by adding or subtracting an <code class="ph codeph">INTERVAL</code> + expression. See <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details. <code class="ph codeph">INTERVAL</code> + expressions are also allowed as the second argument for the <code class="ph codeph">date_add()</code> and + <code class="ph codeph">date_sub()</code> functions, rather than integers. + </p> + + <p class="p"> + Some of these functions are affected by the setting of the + <code class="ph codeph">-use_local_tz_for_unix_timestamp_conversions</code> startup flag for the + <span class="keyword cmdname">impalad</span> daemon. This setting is off by default, meaning that + functions such as <code class="ph codeph">from_unixtime()</code> and <code class="ph codeph">unix_timestamp()</code> + consider the input values to always represent the UTC time zone. + This setting also applies when you <code class="ph codeph">CAST()</code> a <code class="ph codeph">BIGINT</code> + value to <code class="ph codeph">TIMESTAMP</code>, or a <code class="ph codeph">TIMESTAMP</code> + value to <code class="ph codeph">BIGINT</code>. + When this setting is enabled, these functions and operations convert to and from + values representing the local time zone. + See <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details about how + Impala handles time zone considerations for the <code class="ph codeph">TIMESTAMP</code> data type. + </p> + + <p class="p"> + <strong class="ph b">Function reference:</strong> + </p> + + <p class="p"> + Impala supports the following data and time functions: + </p> + + + + <dl class="dl"> + + + <dt class="dt dlterm" id="datetime_functions__add_months"> + <code class="ph codeph">add_months(timestamp date, int months)</code>, <code class="ph codeph">add_months(timestamp date, bigint + months)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of months. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + Same as <code class="ph codeph"><a class="xref" href="#datetime_functions__months_add">months_add()</a></code>. + Available in Impala 1.4 and higher. For + compatibility when porting code with vendor extensions. + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + 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. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__adddate"> + <code class="ph codeph">adddate(timestamp startdate, int days)</code>, <code class="ph codeph">adddate(timestamp startdate, bigint + days)</code>, + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Adds a specified number of days to a <code class="ph codeph">TIMESTAMP</code> value. Similar to + <code class="ph codeph">date_add()</code>, but starts with an actual <code class="ph codeph">TIMESTAMP</code> value instead of a + string that is converted to a <code class="ph codeph">TIMESTAMP</code>. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following examples show how to add a number of days to a <code class="ph codeph">TIMESTAMP</code>. + The number of days can also be negative, which gives the same effect as the <code class="ph codeph">subdate()</code> function. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__current_timestamp"> + <code class="ph codeph">current_timestamp()</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Alias for the <code class="ph codeph">now()</code> function. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__date_add"> + <code class="ph codeph">date_add(timestamp startdate, int days)</code>, <code class="ph codeph">date_add(timestamp startdate, + <var class="keyword varname">interval_expression</var>)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Adds a specified number of days to a <code class="ph codeph">TIMESTAMP</code> value. + + With an <code class="ph codeph">INTERVAL</code> + expression as the second argument, you can calculate a delta value using other units such as weeks, + years, hours, seconds, and so on; see <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following example shows the simplest usage, of adding a specified number of days + to a <code class="ph codeph">TIMESTAMP</code> value: + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + + <p class="p"> + The following examples show the shorthand notation of an <code class="ph codeph">INTERVAL</code> + expression, instead of specifying the precise number of days. + The <code class="ph codeph">INTERVAL</code> notation also lets you work with units smaller than + a single day. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + + <p class="p"> + Like all date/time functions that deal with months, <code class="ph codeph">date_add()</code> + 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: + </p> +<pre class="pre codeblock"><code> +select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31st'; ++---------------------+ +| april_31st | ++---------------------+ +| 2016-04-30 00:00:00 | ++---------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__date_part"> + <code class="ph codeph">date_part(string, timestamp)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Similar to + <a class="xref" href="impala_datetime_functions.html#datetime_functions__extract"><code class="ph codeph">EXTRACT()</code></a>, + with the argument order reversed. Supports the same date and time units as <code class="ph codeph">EXTRACT()</code>. + For compatibility with SQL code containing vendor extensions. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +select date_part('year',now()) as current_year; ++--------------+ +| current_year | ++--------------+ +| 2016 | ++--------------+ + +select date_part('hour',now()) as hour_of_day; ++-------------+ +| hour_of_day | ++-------------+ +| 11 | ++-------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__date_sub"> + <code class="ph codeph">date_sub(timestamp startdate, int days)</code>, <code class="ph codeph">date_sub(timestamp startdate, + <var class="keyword varname">interval_expression</var>)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Subtracts a specified number of days from a <code class="ph codeph">TIMESTAMP</code> value. + + With an + <code class="ph codeph">INTERVAL</code> expression as the second argument, you can calculate a delta value using other + units such as weeks, years, hours, seconds, and so on; see <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> + for details. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following example shows the simplest usage, of subtracting a specified number of days + from a <code class="ph codeph">TIMESTAMP</code> value: + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + <p class="p"> + The following examples show the shorthand notation of an <code class="ph codeph">INTERVAL</code> + expression, instead of specifying the precise number of days. + The <code class="ph codeph">INTERVAL</code> notation also lets you work with units smaller than + a single day. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + + <p class="p"> + Like all date/time functions that deal with months, <code class="ph codeph">date_add()</code> + 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: + </p> +<pre class="pre codeblock"><code> +select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st'; ++---------------------+ +| april_31st | ++---------------------+ +| 2016-04-30 00:00:00 | ++---------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__datediff"> + <code class="ph codeph">datediff(timestamp enddate, timestamp startdate)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the number of days between two <code class="ph codeph">TIMESTAMP</code> values. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + 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 <code class="ph codeph">TIMESTAMP</code> + values are irrelevant. For example, 11:59 PM on one day and 12:01 on the next + day represent a <code class="ph codeph">datediff()</code> of -1 because the date/time values + represent different days, even though the <code class="ph codeph">TIMESTAMP</code> values differ by only 2 minutes. + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following example shows how comparing a <span class="q">"late"</span> value with + an <span class="q">"earlier"</span> value produces a positive number. In this case, + the result is (365 * 5) + 1, because one of the intervening years is + a leap year. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+------------+ +</code></pre> + <p class="p"> + The following examples show how the return value represent the number of days + between the associated dates, regardless of the time portion of each <code class="ph codeph">TIMESTAMP</code>. + For example, different times on the same day produce a <code class="ph codeph">date_diff()</code> of 0, + regardless of which one is earlier or later. But if the arguments represent different dates, + <code class="ph codeph">date_diff()</code> returns a non-zero integer value, regardless of the time portions + of the dates. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+--------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__day"> + + <code class="ph codeph">day(timestamp date), <span class="ph" id="datetime_functions__dayofmonth">dayofmonth(timestamp date)</span></code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the day field from the date portion of a <code class="ph codeph">TIMESTAMP</code>. + The value represents the day of the month, therefore is in the range 1-31, or less for + months without 31 days. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + 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. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + <p class="p"> + The following examples show how the day value is <code class="ph codeph">NULL</code> + for nonexistent dates or misformatted date strings. + </p> +<pre class="pre codeblock"><code> +-- 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 | ++--------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__dayname"> + <code class="ph codeph">dayname(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the day field from a <code class="ph codeph">TIMESTAMP</code> value, converted to the string + corresponding to that day name. The range of return values is <code class="ph codeph">'Sunday'</code> to + <code class="ph codeph">'Saturday'</code>. Used in report-generating queries, as an alternative to calling + <code class="ph codeph">dayofweek()</code> and turning that numeric return value into a string using a + <code class="ph codeph">CASE</code> expression. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">string</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following examples show the day name associated with + <code class="ph codeph">TIMESTAMP</code> values representing different days. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+--------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__dayofweek"> + + <code class="ph codeph">dayofweek(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the day field from the date portion of a <code class="ph codeph">TIMESTAMP</code>, corresponding to the day of + the week. The range of return values is 1 (Sunday) to 7 (Saturday). + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+--------------------+-----------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__dayofyear"> + <code class="ph codeph">dayofyear(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the day field from a <code class="ph codeph">TIMESTAMP</code> 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). + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following examples show return values from the + <code class="ph codeph">dayofyear()</code> 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. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+----------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__days_add"> + <code class="ph codeph">days_add(timestamp startdate, int days)</code>, <code class="ph codeph">days_add(timestamp startdate, bigint + days)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Adds a specified number of days to a <code class="ph codeph">TIMESTAMP</code> value. Similar to + <code class="ph codeph">date_add()</code>, but starts with an actual <code class="ph codeph">TIMESTAMP</code> value instead of a + string that is converted to a <code class="ph codeph">TIMESTAMP</code>. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__days_sub"> + <code class="ph codeph">days_sub(timestamp startdate, int days)</code>, <code class="ph codeph">days_sub(timestamp startdate, bigint + days)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Subtracts a specified number of days from a <code class="ph codeph">TIMESTAMP</code> value. Similar to + <code class="ph codeph">date_sub()</code>, but starts with an actual <code class="ph codeph">TIMESTAMP</code> value instead of a + string that is converted to a <code class="ph codeph">TIMESTAMP</code>. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__extract"> + <code class="ph codeph">extract(timestamp, string unit)</code><code class="ph codeph">extract(unit FROM timestamp)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns one of the numeric date or time fields from a <code class="ph codeph">TIMESTAMP</code> value. + <p class="p"> + <strong class="ph b">Unit argument:</strong> The <code class="ph codeph">unit</code> string can be one of <code class="ph codeph">year</code>, + <code class="ph codeph">month</code>, <code class="ph codeph">day</code>, <code class="ph codeph">hour</code>, <code class="ph codeph">minute</code>, + <code class="ph codeph">second</code>, or <code class="ph codeph">millisecond</code>. This argument value is case-insensitive. + </p> + <div class="p"> + 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 <code class="ph codeph">FROM</code> keyword. With this + style, the unit names are identifiers rather than <code class="ph codeph">STRING</code> literals. For example, the + following calls are both equivalent: +<pre class="pre codeblock"><code>extract(year from now()); +extract(now(), "year"); +</code></pre> + </div> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + Typically used in <code class="ph codeph">GROUP BY</code> queries to arrange results by hour, + day, month, and so on. You can also use this function in an <code class="ph codeph">INSERT ... SELECT</code> into a + partitioned table to split up <code class="ph codeph">TIMESTAMP</code> 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 + <code class="ph codeph">TRUNC()</code> function instead. + </p> + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+----------+-----------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__from_unixtime"> + <code class="ph codeph">from_unixtime(bigint unixtime[, string format])</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Converts the number of seconds from the Unix epoch to the specified time into a string in + the local time zone. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">string</code> + </p> + <p class="p"> + In Impala 2.2.0 and higher, built-in functions that accept or return integers representing <code class="ph codeph">TIMESTAMP</code> values + use the <code class="ph codeph">BIGINT</code> type for parameters and return values, rather than <code class="ph codeph">INT</code>. + This change lets the date and time functions avoid an overflow error that would otherwise occur + on January 19th, 2038 (known as the + <a class="xref" href="http://en.wikipedia.org/wiki/Year_2038_problem"; target="_blank"><span class="q">"Year 2038 problem"</span> or <span class="q">"Y2K38 problem"</span></a>). + This change affects the <code class="ph codeph">from_unixtime()</code> and <code class="ph codeph">unix_timestamp()</code> 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 <code class="ph codeph">CAST()</code> calls to SQL statements that + call these functions. + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + The format string accepts the variations allowed for the <code class="ph codeph">TIMESTAMP</code> + data type: date plus time, date by itself, time by itself, and optional fractional seconds for the + time. See <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details. + </p> + <p class="p"> + Currently, the format string is case-sensitive, especially to distinguish <code class="ph codeph">m</code> for + minutes and <code class="ph codeph">M</code> 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 <code class="ph codeph">y</code>, <code class="ph codeph">d</code>, <code class="ph codeph">H</code>, and so on + produces output strings zero-padded to the requested number of characters. The exception is + <code class="ph codeph">M</code> for months, where <code class="ph codeph">M</code> produces a non-padded value such as + <code class="ph codeph">3</code>, <code class="ph codeph">MM</code> produces a zero-padded value such as <code class="ph codeph">03</code>, + <code class="ph codeph">MMM</code> produces an abbreviated month name such as <code class="ph codeph">Mar</code>, and sequences of + 4 or more <code class="ph codeph">M</code> are not allowed. A date string including all fields could be + <code class="ph codeph">"yyyy-MM-dd HH:mm:ss.SSSSSS"</code>, <code class="ph codeph">"dd/MM/yyyy HH:mm:ss.SSSSSS"</code>, + <code class="ph codeph">"MMM dd, yyyy HH.mm.ss (SSSSSS)"</code> or other combinations of placeholders and separator + characters. + </p> + <p class="p"> + The way this function deals with time zones when converting to or from <code class="ph codeph">TIMESTAMP</code> + values is affected by the <code class="ph codeph">-use_local_tz_for_unix_timestamp_conversions</code> startup flag for the + <span class="keyword cmdname">impalad</span> daemon. See <a class="xref" href="../shared/../topics/impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details about + how Impala handles time zone considerations for the <code class="ph codeph">TIMESTAMP</code> data type. + </p> + <div class="note note note_note"><span class="note__title notetitle">Note:</span> + <p class="p"> + The more flexible format strings allowed with the built-in functions do not change the rules about + using <code class="ph codeph">CAST()</code> to convert from a string to a <code class="ph codeph">TIMESTAMP</code> value. Strings + being converted through <code class="ph codeph">CAST()</code> must still have the elements in the specified order and use the specified delimiter + characters, as described in <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a>. + </p> + </div> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code>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 | ++---------------------------------------+</code></pre> + <div class="p"> + <code class="ph codeph">unix_timestamp()</code> and <code class="ph codeph">from_unixtime()</code> are often used in combination to + convert a <code class="ph codeph">TIMESTAMP</code> value into a particular string format. For example: +<pre class="pre codeblock"><code>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 | ++------------------+ +</code></pre> + </div> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__from_utc_timestamp"> + <code class="ph codeph">from_utc_timestamp(timestamp, string timezone)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Converts a specified UTC timestamp value into the appropriate value for a specified time + zone. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> 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 <code class="ph codeph">to_utc_timestamp()</code> function. + </p> + <p class="p"> + To determine the time zone of the server you are connected to, in <span class="keyword">Impala 2.3</span> and + higher you can call the <code class="ph codeph">timeofday()</code> function, which includes the time zone + specifier in its return value. Remember that with cloud computing, the server you interact + with might be in a different time zone than you are, or different sessions might connect to + servers in different time zones, or a cluster might include servers in more than one time zone. + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + See discussion of time zones in <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> + for information about using this function for conversions between the local time zone and UTC. + </p> + <p class="p"> + The following example shows how when <code class="ph codeph">TIMESTAMP</code> 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. + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + <p class="p"> + The following example shows that for a date and time when daylight savings + is in effect (<code class="ph codeph">PDT</code>), the UTC time + is 7 hours ahead of the local California time; while when daylight savings + is not in effect (<code class="ph codeph">PST</code>), the UTC time is 8 hours ahead of + the local California time. + </p> +<pre class="pre codeblock"><code> +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 | ++----------------+---------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__hour"> + <code class="ph codeph">hour(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the hour field from a <code class="ph codeph">TIMESTAMP</code> field. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__hours_add"> + <code class="ph codeph">hours_add(timestamp date, int hours)</code>, <code class="ph codeph">hours_add(timestamp date, bigint + hours)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of hours. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__hours_sub"> + <code class="ph codeph">hours_sub(timestamp date, int hours)</code>, <code class="ph codeph">hours_sub(timestamp date, bigint + hours)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of hours. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__int_months_between"> + <code class="ph codeph">int_months_between(timestamp newer, timestamp older)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the number of months between the date portions of two <code class="ph codeph">TIMESTAMP</code> values, + as an <code class="ph codeph">INT</code> representing only the full months that passed. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + Typically used in business contexts, for example to determine whether + a specified number of months have passed or whether some end-of-month deadline was reached. + </p> + <p class="p"> + The method of determining the number of elapsed months includes some special handling of + months with different numbers of days that creates edge cases for dates between the + 28th and 31st days of certain months. See <code class="ph codeph">months_between()</code> for details. + The <code class="ph codeph">int_months_between()</code> result is essentially the <code class="ph codeph">floor()</code> + of the <code class="ph codeph">months_between()</code> result. + </p> + <p class="p"> + If either value is <code class="ph codeph">NULL</code>, which could happen for example when converting a + nonexistent date string such as <code class="ph codeph">'2015-02-29'</code> to a <code class="ph codeph">TIMESTAMP</code>, + the result is also <code class="ph codeph">NULL</code>. + </p> + <p class="p"> + If the first argument represents an earlier time than the second argument, the result is negative. + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + +<pre class="pre codeblock"><code>/* Less than a full month = 0. */ +select int_months_between('2015-02-28', '2015-01-29'); ++------------------------------------------------+ +| int_months_between('2015-02-28', '2015-01-29') | ++------------------------------------------------+ +| 0 | ++------------------------------------------------+ + +/* Last day of month to last day of next month = 1. */ +select int_months_between('2015-02-28', '2015-01-31'); ++------------------------------------------------+ +| int_months_between('2015-02-28', '2015-01-31') | ++------------------------------------------------+ +| 1 | ++------------------------------------------------+ + +/* Slightly less than 2 months = 1. */ +select int_months_between('2015-03-28', '2015-01-31'); ++------------------------------------------------+ +| int_months_between('2015-03-28', '2015-01-31') | ++------------------------------------------------+ +| 1 | ++------------------------------------------------+ + +/* 2 full months (identical days of the month) = 2. */ +select int_months_between('2015-03-31', '2015-01-31'); ++------------------------------------------------+ +| int_months_between('2015-03-31', '2015-01-31') | ++------------------------------------------------+ +| 2 | ++------------------------------------------------+ + +/* Last day of month to last day of month-after-next = 2. */ +select int_months_between('2015-03-31', '2015-01-30'); ++------------------------------------------------+ +| int_months_between('2015-03-31', '2015-01-30') | ++------------------------------------------------+ +| 2 | ++------------------------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__microseconds_add"> + <code class="ph codeph">microseconds_add(timestamp date, int microseconds)</code>, <code class="ph codeph">microseconds_add(timestamp + date, bigint microseconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of microseconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__microseconds_sub"> + <code class="ph codeph">microseconds_sub(timestamp date, int microseconds)</code>, <code class="ph codeph">microseconds_sub(timestamp + date, bigint microseconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of microseconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__millisecond"> + <code class="ph codeph">millisecond(timestamp)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the millisecond portion of a <code class="ph codeph">TIMESTAMP</code> value. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.5.0</span> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + The millisecond value is truncated, not rounded, if the <code class="ph codeph">TIMESTAMP</code> + value contains more than 3 significant digits to the right of the decimal point. + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +252.4 milliseconds truncated to 252. + +select now(), millisecond(now()); ++-------------------------------+--------------------+ +| now() | millisecond(now()) | ++-------------------------------+--------------------+ +| 2016-03-14 22:30:25.252400000 | 252 | ++-------------------------------+--------------------+ + +761.767 milliseconds truncated to 761. + +select now(), millisecond(now()); ++-------------------------------+--------------------+ +| now() | millisecond(now()) | ++-------------------------------+--------------------+ +| 2016-03-14 22:30:58.761767000 | 761 | ++-------------------------------+--------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__milliseconds_add"> + <code class="ph codeph">milliseconds_add(timestamp date, int milliseconds)</code>, <code class="ph codeph">milliseconds_add(timestamp + date, bigint milliseconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of milliseconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__milliseconds_sub"> + <code class="ph codeph">milliseconds_sub(timestamp date, int milliseconds)</code>, <code class="ph codeph">milliseconds_sub(timestamp + date, bigint milliseconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of milliseconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__minute"> + <code class="ph codeph">minute(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the minute field from a <code class="ph codeph">TIMESTAMP</code> value. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +select now() as right_now, minute(now()) as current_minute; ++-------------------------------+----------------+ +| right_now | current_minute | ++-------------------------------+----------------+ +| 2016-06-01 14:34:08.051702000 | 34 | ++-------------------------------+----------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__minutes_add"> + <code class="ph codeph">minutes_add(timestamp date, int minutes)</code>, <code class="ph codeph">minutes_add(timestamp date, bigint + minutes)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of minutes. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__minutes_sub"> + <code class="ph codeph">minutes_sub(timestamp date, int minutes)</code>, <code class="ph codeph">minutes_sub(timestamp date, bigint + minutes)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of minutes. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__month"> + + <code class="ph codeph">month(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the month field, represented as an integer, from the date portion of a <code class="ph codeph">TIMESTAMP</code>. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +select now() as right_now, month(now()) as current_month; ++-------------------------------+---------------+ +| right_now | current_month | ++-------------------------------+---------------+ +| 2016-06-01 14:43:37.141542000 | 6 | ++-------------------------------+---------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__months_add"> + <code class="ph codeph">months_add(timestamp date, int months)</code>, <code class="ph codeph">months_add(timestamp date, bigint + months)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of months. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following example shows the effects of adding some number of + months to a <code class="ph codeph">TIMESTAMP</code> value, using both the + <code class="ph codeph">months_add()</code> function and its <code class="ph codeph">add_months()</code> + alias. These examples use <code class="ph codeph">trunc()</code> to strip off the time portion + and leave just the date. + </p> +<pre class="pre codeblock"><code> +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 | ++---------------------+---------------------+ +</code></pre> + <p class="p"> + The following examples show how if <code class="ph codeph">months_add()</code> + would return a nonexistent date, due to different months having + different numbers of days, the function returns a <code class="ph codeph">TIMESTAMP</code> + 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). + </p> +<pre class="pre codeblock"><code> +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 | ++---------------------+---------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__months_between"> + <code class="ph codeph">months_between(timestamp newer, timestamp older)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the number of months between the date portions of two <code class="ph codeph">TIMESTAMP</code> values. + Can include a fractional part representing extra days in addition to the full months + between the dates. The fractional component is computed by dividing the difference in days by 31 (regardless of the month). + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">double</code> + </p> + <p class="p"> + <strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + Typically used in business contexts, for example to determine whether + a specified number of months have passed or whether some end-of-month deadline was reached. + </p> + <p class="p"> + If the only consideration is the number of full months and any fractional value is + not significant, use <code class="ph codeph">int_months_between()</code> instead. + </p> + <p class="p"> + The method of determining the number of elapsed months includes some special handling of + months with different numbers of days that creates edge cases for dates between the + 28th and 31st days of certain months. + </p> + <p class="p"> + If either value is <code class="ph codeph">NULL</code>, which could happen for example when converting a + nonexistent date string such as <code class="ph codeph">'2015-02-29'</code> to a <code class="ph codeph">TIMESTAMP</code>, + the result is also <code class="ph codeph">NULL</code>. + </p> + <p class="p"> + If the first argument represents an earlier time than the second argument, the result is negative. + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> + <p class="p"> + The following examples show how dates that are on the same day of the month + are considered to be exactly N months apart, even if the months have different + numbers of days. + </p> +<pre class="pre codeblock"><code>select months_between('2015-02-28', '2015-01-28'); ++--------------------------------------------+ +| months_between('2015-02-28', '2015-01-28') | ++--------------------------------------------+ +| 1 | ++--------------------------------------------+ + +select months_between(now(), now() + interval 1 month); ++-------------------------------------------------+ +| months_between(now(), now() + interval 1 month) | ++-------------------------------------------------+ +| -1 | ++-------------------------------------------------+ + +select months_between(now() + interval 1 year, now()); ++------------------------------------------------+ +| months_between(now() + interval 1 year, now()) | ++------------------------------------------------+ +| 12 | ++------------------------------------------------+ +</code></pre> + <p class="p"> + The following examples show how dates that are on the last day of the month + are considered to be exactly N months apart, even if the months have different + numbers of days. For example, from January 28th to February 28th is exactly one + month because the day of the month is identical; January 31st to February 28th + is exactly one month because in both cases it is the last day of the month; + but January 29th or 30th to February 28th is considered a fractional month. + </p> +<pre class="pre codeblock"><code>select months_between('2015-02-28', '2015-01-31'); ++--------------------------------------------+ +| months_between('2015-02-28', '2015-01-31') | ++--------------------------------------------+ +| 1 | ++--------------------------------------------+ + +select months_between('2015-02-28', '2015-01-29'); ++--------------------------------------------+ +| months_between('2015-02-28', '2015-01-29') | ++--------------------------------------------+ +| 0.967741935483871 | ++--------------------------------------------+ + +select months_between('2015-02-28', '2015-01-30');; ++--------------------------------------------+ +| months_between('2015-02-28', '2015-01-30') | ++--------------------------------------------+ +| 0.935483870967742 | ++--------------------------------------------+ +</code></pre> + <p class="p"> + The following examples show how dates that are not a precise number + of months apart result in a fractional return value. + </p> +<pre class="pre codeblock"><code>select months_between('2015-03-01', '2015-01-28'); ++--------------------------------------------+ +| months_between('2015-03-01', '2015-01-28') | ++--------------------------------------------+ +| 1.129032258064516 | ++--------------------------------------------+ + +select months_between('2015-03-01', '2015-02-28'); ++--------------------------------------------+ +| months_between('2015-03-01', '2015-02-28') | ++--------------------------------------------+ +| 0.1290322580645161 | ++--------------------------------------------+ + +select months_between('2015-06-02', '2015-05-29'); ++--------------------------------------------+ +| months_between('2015-06-02', '2015-05-29') | ++--------------------------------------------+ +| 0.1290322580645161 | ++--------------------------------------------+ + +select months_between('2015-03-01', '2015-01-25'); ++--------------------------------------------+ +| months_between('2015-03-01', '2015-01-25') | ++--------------------------------------------+ +| 1.225806451612903 | ++--------------------------------------------+ + +select months_between('2015-03-01', '2015-02-25'); ++--------------------------------------------+ +| months_between('2015-03-01', '2015-02-25') | ++--------------------------------------------+ +| 0.2258064516129032 | ++--------------------------------------------+ + +select months_between('2015-02-28', '2015-02-01'); ++--------------------------------------------+ +| months_between('2015-02-28', '2015-02-01') | ++--------------------------------------------+ +| 0.8709677419354839 | ++--------------------------------------------+ + +select months_between('2015-03-28', '2015-03-01'); ++--------------------------------------------+ +| months_between('2015-03-28', '2015-03-01') | ++--------------------------------------------+ +| 0.8709677419354839 | ++--------------------------------------------+ +</code></pre> + <p class="p"> + The following examples show how the time portion of the <code class="ph codeph">TIMESTAMP</code> + values are irrelevant for calculating the month interval. Even the fractional part + of the result only depends on the number of full days between the argument values, + regardless of the time portion. + </p> +<pre class="pre codeblock"><code>select months_between('2015-05-28 23:00:00', '2015-04-28 11:45:00'); ++--------------------------------------------------------------+ +| months_between('2015-05-28 23:00:00', '2015-04-28 11:45:00') | ++--------------------------------------------------------------+ +| 1 | ++--------------------------------------------------------------+ + +select months_between('2015-03-28', '2015-03-01'); ++--------------------------------------------+ +| months_between('2015-03-28', '2015-03-01') | ++--------------------------------------------+ +| 0.8709677419354839 | ++--------------------------------------------+ + +select months_between('2015-03-28 23:00:00', '2015-03-01 11:45:00'); ++--------------------------------------------------------------+ +| months_between('2015-03-28 23:00:00', '2015-03-01 11:45:00') | ++--------------------------------------------------------------+ +| 0.8709677419354839 | ++--------------------------------------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__months_sub"> + <code class="ph codeph">months_sub(timestamp date, int months)</code>, <code class="ph codeph">months_sub(timestamp date, bigint + months)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of months. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++---------------------+---------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__nanoseconds_add"> + <code class="ph codeph">nanoseconds_add(timestamp date, int nanoseconds)</code>, <code class="ph codeph">nanoseconds_add(timestamp + date, bigint nanoseconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of nanoseconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__nanoseconds_sub"> + <code class="ph codeph">nanoseconds_sub(timestamp date, int nanoseconds)</code>, <code class="ph codeph">nanoseconds_sub(timestamp + date, bigint nanoseconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of nanoseconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__now"> + <code class="ph codeph">now()</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the current date and time (in the local time zone) as a + <code class="ph codeph">TIMESTAMP</code> value. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Usage notes:</strong> + </p> + <p class="p"> + To find a date/time value in the future or the past relative to the current date + and time, add or subtract an <code class="ph codeph">INTERVAL</code> expression to the return value of + <code class="ph codeph">now()</code>. See <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for examples. + </p> + <p class="p"> + To produce a <code class="ph codeph">TIMESTAMP</code> representing the current date and time that can be + shared or stored without interoperability problems due to time zone differences, use the + <code class="ph codeph">to_utc_timestamp()</code> function and specify the time zone of the server. + When <code class="ph codeph">TIMESTAMP</code> 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, <code class="ph codeph">from_utc_timestamp()</code>. + </p> + <p class="p"> + To determine the time zone of the server you are connected to, in <span class="keyword">Impala 2.3</span> and + higher you can call the <code class="ph codeph">timeofday()</code> function, which includes the time zone + specifier in its return value. Remember that with cloud computing, the server you interact + with might be in a different time zone than you are, or different sessions might connect to + servers in different time zones, or a cluster might include servers in more than one time zone. + </p> + <p class="p"> + Any references to the <code class="ph codeph">now()</code> function are evaluated at the start of a query. + All calls to <code class="ph codeph">now()</code> within the same query return the same value, + and the value does not depend on how long the query takes. + </p> + + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__second"> + <code class="ph codeph">second(timestamp date)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the second field from a <code class="ph codeph">TIMESTAMP</code> value. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">int</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+---------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__seconds_add"> + <code class="ph codeph">seconds_add(timestamp date, int seconds)</code>, <code class="ph codeph">seconds_add(timestamp date, bigint + seconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of seconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__seconds_sub"> + <code class="ph codeph">seconds_sub(timestamp date, int seconds)</code>, <code class="ph codeph">seconds_sub(timestamp date, bigint + seconds)</code> + </dt> + + <dd class="dd"> + + <strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of seconds. + <p class="p"> + <strong class="ph b">Return type:</strong> <code class="ph codeph">timestamp</code> + </p> + <p class="p"> + <strong class="ph b">Examples:</strong> + </p> +<pre class="pre codeblock"><code> +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 | ++-------------------------------+-------------------------------+ +</code></pre> + </dd> + + + + + + <dt class="dt dlterm" id="datetime_functions__subdate"> + <code class="ph codeph">subdate(timestamp startdate, int days)</code>, <code class="ph codeph">subdate(timestamp startdate, bigint + days)</code>, + <
<TRUNCATED>
