You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by jr...@apache.org on 2016/07/26 23:05:06 UTC
[14/22] incubator-impala git commit: First try at porting over the
source files necessary for the Impala SQL Reference.
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/463ddf92/docs/topics/impala_datetime_functions.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_datetime_functions.xml b/docs/topics/impala_datetime_functions.xml
new file mode 100644
index 0000000..16ae088
--- /dev/null
+++ b/docs/topics/impala_datetime_functions.xml
@@ -0,0 +1,1505 @@
+<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
+<concept id="datetime_functions">
+
+ <title>Impala Date and Time Functions</title>
+ <titlealts><navtitle>Date and Time Functions</navtitle></titlealts>
+ <prolog>
+ <metadata>
+ <data name="Category" value="Impala"/>
+ <data name="Category" value="Impala Functions"/>
+ <data name="Category" value="SQL"/>
+ <data name="Category" value="Data Analysts"/>
+ <data name="Category" value="Developers"/>
+ <data name="Category" value="Dates and Times"/>
+ <data name="Category" value="Querying"/>
+ </metadata>
+ </prolog>
+
+ <conbody>
+
+ <p>
+ The underlying Impala data type for date and time data is
+ <codeph><xref href="impala_timestamp.xml#timestamp">TIMESTAMP</xref></codeph>, which has both a date and a
+ time portion. Functions that extract a single field, such as <codeph>hour()</codeph> or
+ <codeph>minute()</codeph>, typically return an integer value. Functions that format the date portion, such as
+ <codeph>date_add()</codeph> or <codeph>to_date()</codeph>, typically return a string value.
+ </p>
+
+ <p>
+ You can also adjust a <codeph>TIMESTAMP</codeph> value by adding or subtracting an <codeph>INTERVAL</codeph>
+ expression. See <xref href="impala_timestamp.xml#timestamp"/> for details. <codeph>INTERVAL</codeph>
+ expressions are also allowed as the second argument for the <codeph>date_add()</codeph> and
+ <codeph>date_sub()</codeph> functions, rather than integers.
+ </p>
+
+ <p rev="2.2.0">
+ Some of these functions are affected by the setting of the
+ <codeph>-use_local_tz_for_unix_timestamp_conversions</codeph> startup flag for the
+ <cmdname>impalad</cmdname> daemon. This setting is off by default, meaning that
+ functions such as <codeph>from_unixtime()</codeph> and <codeph>unix_timestamp()</codeph>
+ consider the input values to always represent the UTC time zone.
+ This setting also applies when you <codeph>CAST()</codeph> a <codeph>BIGINT</codeph>
+ value to <codeph>TIMESTAMP</codeph>, or a <codeph>TIMESTAMP</codeph>
+ value to <codeph>BIGINT</codeph>.
+ When this setting is enabled, these functions and operations convert to and from
+ values representing the local time zone.
+ See <xref href="impala_timestamp.xml#timestamp"/> for details about how
+ Impala handles time zone considerations for the <codeph>TIMESTAMP</codeph> data type.
+ </p>
+
+ <p>
+ <b>Function reference:</b>
+ </p>
+
+ <p>
+ Impala supports the following data and time functions:
+ </p>
+
+<!-- New for 2.3:
+int_months_between
+timeofday
+timestamp_cmp
+months_between
+-->
+
+ <dl>
+ <dlentry rev="1.4.0" id="add_months">
+
+ <dt>
+ <codeph>add_months(timestamp date, int months)</codeph>, <codeph>add_months(timestamp date, bigint
+ months)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">add_months() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of months.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>
+ Same as <codeph>months_add()</codeph>. Available in Impala 1.4 and higher. For
+ compatibility when porting code with vendor extensions.
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="adddate">
+
+ <dt>
+ <codeph>adddate(timestamp startdate, int days)</codeph>, <codeph>adddate(timestamp startdate, bigint
+ days)</codeph>,
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">adddate() function</indexterm>
+ <b>Purpose:</b> Adds a specified number of days to a <codeph>TIMESTAMP</codeph> value. Similar to
+ <codeph>date_add()</codeph>, but starts with an actual <codeph>TIMESTAMP</codeph> value instead of a
+ string that is converted to a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="current_timestamp">
+
+ <dt>
+ <codeph>current_timestamp()</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">current_timestamp() function</indexterm>
+ <b>Purpose:</b> Alias for the <codeph>now()</codeph> function.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="date_add">
+
+ <dt>
+ <codeph>date_add(timestamp startdate, int days)</codeph>, <codeph>date_add(timestamp startdate,
+ <varname>interval_expression</varname>)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">date_add() function</indexterm>
+ <b>Purpose:</b> Adds a specified number of days to a <codeph>TIMESTAMP</codeph> value. The first argument
+ can be a string, which is automatically cast to <codeph>TIMESTAMP</codeph> if it uses the recognized
+ format, as described in <xref href="impala_timestamp.xml#timestamp"/>. With an <codeph>INTERVAL</codeph>
+ expression as the second argument, you can calculate a delta value using other units such as weeks,
+ years, hours, seconds, and so on; see <xref href="impala_timestamp.xml#timestamp"/> for details.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="2.0.0" id="date_part">
+
+ <dt>
+ <codeph>date_part(string, timestamp)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">date_part() function</indexterm>
+ <b>Purpose:</b> Similar to <codeph>EXTRACT()</codeph>, with the argument order reversed. Supports the
+ same date and time units as <codeph>EXTRACT()</codeph>. For compatibility with SQL code containing vendor
+ extensions.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="date_sub">
+
+ <dt>
+ <codeph>date_sub(timestamp startdate, int days)</codeph>, <codeph>date_sub(timestamp startdate,
+ <varname>interval_expression</varname>)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">date_sub() function</indexterm>
+ <b>Purpose:</b> Subtracts a specified number of days from a <codeph>TIMESTAMP</codeph> value. The first
+ argument can be a string, which is automatically cast to <codeph>TIMESTAMP</codeph> if it uses the
+ recognized format, as described in <xref href="impala_timestamp.xml#timestamp"/>. With an
+ <codeph>INTERVAL</codeph> expression as the second argument, you can calculate a delta value using other
+ units such as weeks, years, hours, seconds, and so on; see <xref href="impala_timestamp.xml#timestamp"/>
+ for details.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="datediff">
+
+ <dt>
+ <codeph>datediff(string enddate, string startdate)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">datediff() function</indexterm>
+ <b>Purpose:</b> Returns the number of days between two dates represented as strings.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="day">
+
+ <dt>
+ <!-- <codeph>day(string date), <ph id="dayofmonth">dayofmonth(string date)</ph></codeph> -->
+ <codeph>day(timestamp date), <ph id="dayofmonth">dayofmonth(timestamp date)</ph></codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">day() function</indexterm>
+ <b>Purpose:</b> Returns the day field from the date portion of a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.2" id="dayname">
+
+ <dt>
+ <!-- <codeph>dayname(string date)</codeph> -->
+ <codeph>dayname(timestamp date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">dayname() function</indexterm>
+ <b>Purpose:</b> Returns the day field from a date represented as a string, converted to the string
+ corresponding to that day name. The range of return values is <codeph>'Sunday'</codeph> to
+ <codeph>'Saturday'</codeph>. Used in report-generating queries, as an alternative to calling
+ <codeph>dayofweek()</codeph> and turning that numeric return value into a string using a
+ <codeph>CASE</codeph> expression.
+ <p>
+ <b>Return type:</b> <codeph>string</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.1" id="dayofweek">
+
+ <dt>
+ <!-- <codeph>dayofweek(string date)</codeph> -->
+ <codeph>dayofweek(timestamp date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">dayofweek() function</indexterm>
+ <b>Purpose:</b> Returns the day field from the date portion of a <codeph>TIMESTAMP</codeph>, corresponding to the day of
+ the week. The range of return values is 1 (Sunday) to 7 (Saturday).
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="dayofyear">
+
+ <dt>
+ <codeph>dayofyear(timestamp date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">dayofyear() function</indexterm>
+ <b>Purpose:</b> Returns the day field from a <codeph>TIMESTAMP</codeph> 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>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="days_add">
+
+ <dt>
+ <codeph>days_add(timestamp startdate, int days)</codeph>, <codeph>days_add(timestamp startdate, bigint
+ days)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">days_add() function</indexterm>
+ <b>Purpose:</b> Adds a specified number of days to a <codeph>TIMESTAMP</codeph> value. Similar to
+ <codeph>date_add()</codeph>, but starts with an actual <codeph>TIMESTAMP</codeph> value instead of a
+ string that is converted to a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="days_sub">
+
+ <dt>
+ <codeph>days_sub(timestamp startdate, int days)</codeph>, <codeph>days_sub(timestamp startdate, bigint
+ days)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">days_sub() function</indexterm>
+ <b>Purpose:</b> Subtracts a specified number of days from a <codeph>TIMESTAMP</codeph> value. Similar to
+ <codeph>date_sub()</codeph>, but starts with an actual <codeph>TIMESTAMP</codeph> value instead of a
+ string that is converted to a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.4.0" id="extract">
+
+ <dt>
+ <codeph>extract(timestamp, string unit)</codeph><codeph rev="2.0.0">extract(unit FROM timestamp)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">extract() function</indexterm>
+ <b>Purpose:</b> Returns one of the numeric date or time fields from a <codeph>TIMESTAMP</codeph> value.
+ <p>
+ <b>Unit argument:</b> The <codeph>unit</codeph> string can be one of <codeph>year</codeph>,
+ <codeph>month</codeph>, <codeph>day</codeph>, <codeph>hour</codeph>, <codeph>minute</codeph>,
+ <codeph>second</codeph>, or <codeph>millisecond</codeph>. This argument value is case-insensitive.
+ </p>
+ <p rev="2.0.0">
+ 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 <codeph>FROM</codeph> keyword. With this
+ style, the unit names are identifiers rather than <codeph>STRING</codeph> literals. For example, the
+ following calls are both equivalent:
+<codeblock>extract(year from now());
+extract(now(), "year");
+</codeblock>
+ </p>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>
+ Typically used in <codeph>GROUP BY</codeph> queries to arrange results by hour,
+ day, month, and so on. You can also use this function in an <codeph>INSERT ... SELECT</codeph> into a
+ partitioned table to split up <codeph>TIMESTAMP</codeph> 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
+ <codeph>TRUNC()</codeph> function instead.
+ </p>
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="from_unixtime">
+
+ <dt>
+ <codeph>from_unixtime(bigint unixtime[, string format])</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">from_unixtime() function</indexterm>
+ <b>Purpose:</b> Converts the number of seconds from the Unix epoch to the specified time into a string in
+ the local time zone.
+ <p>
+ <b>Return type:</b> <codeph>string</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/y2k38"/>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>
+ The format string accepts the variations allowed for the <codeph>TIMESTAMP</codeph>
+ data type: date plus time, date by itself, time by itself, and optional fractional seconds for the
+ time. See <xref href="impala_timestamp.xml#timestamp"/> for details.
+ </p>
+ <p rev="1.3.0">
+ Currently, the format string is case-sensitive, especially to distinguish <codeph>m</codeph> for
+ minutes and <codeph>M</codeph> 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 <codeph>y</codeph>, <codeph>d</codeph>, <codeph>H</codeph>, and so on
+ produces output strings zero-padded to the requested number of characters. The exception is
+ <codeph>M</codeph> for months, where <codeph>M</codeph> produces a non-padded value such as
+ <codeph>3</codeph>, <codeph>MM</codeph> produces a zero-padded value such as <codeph>03</codeph>,
+ <codeph>MMM</codeph> produces an abbreviated month name such as <codeph>Mar</codeph>, and sequences of
+ 4 or more <codeph>M</codeph> are not allowed. A date string including all fields could be
+ <codeph>"yyyy-MM-dd HH:mm:ss.SSSSSS"</codeph>, <codeph>"dd/MM/yyyy HH:mm:ss.SSSSSS"</codeph>,
+ <codeph>"MMM dd, yyyy HH.mm.ss (SSSSSS)"</codeph> or other combinations of placeholders and separator
+ characters.
+ </p>
+ <p conref="../shared/impala_common.xml#common/timezone_conversion_caveat"/>
+ <note rev="1.3.0">
+ The more flexible format strings allowed with the built-in functions do not change the rules about
+ using <codeph>CAST()</codeph> to convert from a string to a <codeph>TIMESTAMP</codeph> value. Strings
+ being converted through <codeph>CAST()</codeph> must still have the elements in the specified order and use the specified delimiter
+ characters, as described in <xref href="impala_timestamp.xml#timestamp"/>.
+ </note>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+<codeblock>[localhost:21000] > 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 |
++-------------------------------------------------------+
+[localhost:21000] > select from_unixtime(1392394861,"yyyy-MM-dd");
++-----------------------------------------+
+| from_unixtime(1392394861, 'yyyy-mm-dd') |
++-----------------------------------------+
+| 2014-02-14 |
++-----------------------------------------+
+[localhost:21000] > select from_unixtime(1392394861,"HH:mm:ss.SSSS");
++--------------------------------------------+
+| from_unixtime(1392394861, 'hh:mm:ss.ssss') |
++--------------------------------------------+
+| 16:21:01.0000 |
++--------------------------------------------+
+[localhost:21000] > select from_unixtime(1392394861,"HH:mm:ss");
++---------------------------------------+
+| from_unixtime(1392394861, 'hh:mm:ss') |
++---------------------------------------+
+| 16:21:01 |
++---------------------------------------+</codeblock>
+ <p conref="../shared/impala_common.xml#common/datetime_function_chaining"/>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="from_utc_timestamp">
+
+ <dt>
+ <codeph>from_utc_timestamp(timestamp, string timezone)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">from_utc_timestamp() function</indexterm>
+ <b>Purpose:</b> Converts a specified UTC timestamp value into the appropriate value for a specified time
+ zone.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ <p>
+ <b>Usage notes:</b> 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 <codeph>to_utc_timestamp()</codeph> function.
+ </p>
+ <p>
+ <b>Examples:</b> See discussion of time zones in <xref href="impala_timestamp.xml#timestamp"/>
+ for information about using this function for conversions between the local time zone and UTC.
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="hour">
+
+ <dt>
+ <codeph>hour(string date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">hour() function</indexterm>
+ <b>Purpose:</b> Returns the hour field from a date represented as a string.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="hours_add">
+
+ <dt>
+ <codeph>hours_add(timestamp date, int hours)</codeph>, <codeph>hours_add(timestamp date, bigint
+ hours)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">hours_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of hours.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="hours_sub">
+
+ <dt>
+ <codeph>hours_sub(timestamp date, int hours)</codeph>, <codeph>hours_sub(timestamp date, bigint
+ hours)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">hours_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of hours.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="2.3.0" id="int_months_between">
+
+ <dt>
+ <codeph>int_months_between(timestamp newer, timestamp older)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">int_months_between() function</indexterm>
+ <b>Purpose:</b> Returns the number of months between the date portions of two <codeph>TIMESTAMP</codeph> values,
+ as an <codeph>INT</codeph> representing only the full months that passed.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/added_in_230"/>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <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>
+ 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 <codeph>months_between()</codeph> for details.
+ The <codeph>int_months_between()</codeph> result is essentially the <codeph>floor()</codeph>
+ of the <codeph>months_between()</codeph> result.
+ </p>
+ <p>
+ If either value is <codeph>NULL</codeph>, which could happen for example when converting a
+ nonexistent date string such as <codeph>'2015-02-29'</codeph> to a <codeph>TIMESTAMP</codeph>,
+ the result is also <codeph>NULL</codeph>.
+ </p>
+ <p>
+ If the first argument represents an earlier time than the second argument, the result is negative.
+ </p>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+
+<codeblock>/* 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 |
++------------------------------------------------+
+</codeblock>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="microseconds_add">
+
+ <dt>
+ <codeph>microseconds_add(timestamp date, int microseconds)</codeph>, <codeph>microseconds_add(timestamp
+ date, bigint microseconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">microseconds_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of microseconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="microseconds_sub">
+
+ <dt>
+ <codeph>microseconds_sub(timestamp date, int microseconds)</codeph>, <codeph>microseconds_sub(timestamp
+ date, bigint microseconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">microseconds_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of microseconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="milliseconds_add">
+
+ <dt>
+ <codeph>milliseconds_add(timestamp date, int milliseconds)</codeph>, <codeph>milliseconds_add(timestamp
+ date, bigint milliseconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">milliseconds_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of milliseconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="milliseconds_sub">
+
+ <dt>
+ <codeph>milliseconds_sub(timestamp date, int milliseconds)</codeph>, <codeph>milliseconds_sub(timestamp
+ date, bigint milliseconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">milliseconds_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of milliseconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="minute">
+
+ <dt>
+ <codeph>minute(string date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">minute() function</indexterm>
+ <b>Purpose:</b> Returns the minute field from a date represented as a string.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="minutes_add">
+
+ <dt>
+ <codeph>minutes_add(timestamp date, int minutes)</codeph>, <codeph>minutes_add(timestamp date, bigint
+ minutes)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">minutes_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of minutes.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="minutes_sub">
+
+ <dt>
+ <codeph>minutes_sub(timestamp date, int minutes)</codeph>, <codeph>minutes_sub(timestamp date, bigint
+ minutes)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">minutes_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of minutes.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="month">
+
+ <dt>
+ <!-- <codeph>month(string date)</codeph> -->
+ <codeph>month(timestamp date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">month() function</indexterm>
+ <b>Purpose:</b> Returns the month field from the date portion of a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="months_add">
+
+ <dt>
+ <codeph>months_add(timestamp date, int months)</codeph>, <codeph>months_add(timestamp date, bigint
+ months)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">months_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of months.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="2.3.0" id="months_between">
+
+ <dt>
+ <codeph>months_between(timestamp newer, timestamp older)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">months_between() function</indexterm>
+ <b>Purpose:</b> Returns the number of months between the date portions of two <codeph>TIMESTAMP</codeph> 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>
+ <b>Return type:</b> <codeph>double</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/added_in_230"/>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <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>
+ If the only consideration is the number of full months and any fractional value is
+ not significant, use <codeph>int_months_between()</codeph> instead.
+ </p>
+ <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>
+ If either value is <codeph>NULL</codeph>, which could happen for example when converting a
+ nonexistent date string such as <codeph>'2015-02-29'</codeph> to a <codeph>TIMESTAMP</codeph>,
+ the result is also <codeph>NULL</codeph>.
+ </p>
+ <p>
+ If the first argument represents an earlier time than the second argument, the result is negative.
+ </p>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <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>
+<codeblock>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 |
++------------------------------------------------+
+</codeblock>
+ <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>
+<codeblock>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 |
++--------------------------------------------+
+</codeblock>
+ <p>
+ The following examples show how dates that are not a precise number
+ of months apart result in a fractional return value.
+ </p>
+<codeblock>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 |
++--------------------------------------------+
+</codeblock>
+ <p>
+ The following examples show how the time portion of the <codeph>TIMESTAMP</codeph>
+ 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>
+<codeblock>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 |
++--------------------------------------------------------------+
+</codeblock>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="months_sub">
+
+ <dt>
+ <codeph>months_sub(timestamp date, int months)</codeph>, <codeph>months_sub(timestamp date, bigint
+ months)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">months_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of months.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="nanoseconds_add">
+
+ <dt>
+ <codeph>nanoseconds_add(timestamp date, int nanoseconds)</codeph>, <codeph>nanoseconds_add(timestamp
+ date, bigint nanoseconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">nanoseconds_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of nanoseconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="nanoseconds_sub">
+
+ <dt>
+ <codeph>nanoseconds_sub(timestamp date, int nanoseconds)</codeph>, <codeph>nanoseconds_sub(timestamp
+ date, bigint nanoseconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">nanoseconds_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of nanoseconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="now">
+
+ <dt>
+ <codeph>now()</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">now() function</indexterm>
+<!-- <b>Purpose:</b> Returns the current date and time (in the UTC time zone) as a <codeph>timestamp</codeph> value. -->
+ <b>Purpose:</b> Returns the current date and time (in the local time zone) as a
+ <codeph>timestamp</codeph> value.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>
+ To find a date/time value in the future or the past relative to the current date
+ and time, add or subtract an <codeph>INTERVAL</codeph> expression to the return value of
+ <codeph>now()</codeph>. See <xref href="impala_timestamp.xml#timestamp"/> for examples.
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="second">
+
+ <dt>
+ <codeph>second(string date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">second() function</indexterm>
+ <b>Purpose:</b> Returns the second field from a date represented as a string.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="seconds_add">
+
+ <dt>
+ <codeph>seconds_add(timestamp date, int seconds)</codeph>, <codeph>seconds_add(timestamp date, bigint
+ seconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">seconds_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of seconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="seconds_sub">
+
+ <dt>
+ <codeph>seconds_sub(timestamp date, int seconds)</codeph>, <codeph>seconds_sub(timestamp date, bigint
+ seconds)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">seconds_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of seconds.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="subdate">
+
+ <dt>
+ <codeph>subdate(timestamp startdate, int days)</codeph>, <codeph>subdate(timestamp startdate, bigint
+ days)</codeph>,
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">subdate() function</indexterm>
+ <b>Purpose:</b> Subtracts a specified number of days from a <codeph>TIMESTAMP</codeph> value. Similar to
+ <codeph>date_sub()</codeph>, but starts with an actual <codeph>TIMESTAMP</codeph> value instead of a
+ string that is converted to a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="2.3.0" id="timeofday">
+
+ <dt>
+ <codeph>timeofday()</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">timeofday() function</indexterm>
+ <b>Purpose:</b> Returns a string representation of the current date and time, according to the time of the local system,
+ including any time zone designation.
+ <p>
+ <b>Return type:</b> <codeph>string</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/added_in_230"/>
+ <p>
+ <b>Usage notes:</b> The result value represents similar information as the
+ <codeph>now()</codeph> function, only as a <codeph>STRING</codeph> type
+ and with somewhat different formatting. For example, the day of the week
+ and the time zone identifier are included. This function is intended
+ primarily for compatibility with SQL code from other systems that
+ also have a <codeph>timeofday()</codeph> function. Prefer to use
+ <codeph>now()</codeph> if practical for any new Impala code.
+ </p>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <p>
+ The following examples show the format of the <codeph>timeofday()</codeph>
+ return value, illustrate how that value is represented as a <codeph>STRING</codeph>
+ that you can manipulate with string processing functions, and how the format
+ compares with the return value from the <codeph>now()</codeph> function.
+ </p>
+<codeblock>/* Date and time fields in a STRING return value. */
+select timeofday();
++------------------------------+
+| timeofday() |
++------------------------------+
+| Tue Sep 01 15:13:18 2015 PDT |
++------------------------------+
+
+/* The return value can be processed by other string functions. */
+select upper(timeofday());
++------------------------------+
+| upper(timeofday()) |
++------------------------------+
+| TUE SEP 01 15:13:38 2015 PDT |
++------------------------------+
+
+/* The TIMEOFDAY() result is formatted differently than NOW(). NOW() returns a TIMESTAMP. */
+select now(), timeofday();
++-------------------------------+------------------------------+
+| now() | timeofday() |
++-------------------------------+------------------------------+
+| 2015-09-01 15:15:25.930021000 | Tue Sep 01 15:15:25 2015 PDT |
++-------------------------------+------------------------------+
+</codeblock>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="2.3.0" id="timestamp_cmp">
+
+ <dt>
+ <codeph>timestamp_cmp(timestamp t1, timestamp t2)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">timestamp_cmp() function</indexterm>
+ <b>Purpose:</b> Tests if one <codeph>TIMESTAMP</codeph> value is
+ newer than, older than, or identical to another <codeph>TIMESTAMP</codeph>
+ <p>
+ <b>Return type:</b> <codeph>int</codeph> (either -1, 0, 1, or <codeph>NULL</codeph>)
+ </p>
+ <p conref="../shared/impala_common.xml#common/added_in_230"/>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>
+ <b>Usage notes:</b> A comparison function for <codeph>TIMESTAMP</codeph>
+ values that only tests whether the date and time increases, decreases,
+ or stays the same. Similar to the <codeph>sign()</codeph> function
+ for numeric values.
+ </p>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <p>
+ The following examples show all the possible return values for <codeph>timestamp_cmp()</codeph>.
+ If the first argument represents a later point in time than the second argument, the result is 1.
+ The amount of the difference is irrelevant, only the fact that one argument is greater than or less than the other.
+ If the first argument represents an earlier point in time than the second argument, the result is -1.
+ If the first and second arguments represent identical points in time, the result is 0.
+ If either argument is <codeph>NULL</codeph>, the result is <codeph>NULL</codeph>.
+ </p>
+<codeblock>/* First argument 'later' than second argument. */
+
+select timestamp_cmp(now() + interval 70 minutes, now());
++---------------------------------------------------+
+| timestamp_cmp(now() + interval 70 minutes, now()) |
++---------------------------------------------------+
+| 1 |
++---------------------------------------------------+
+
+select timestamp_cmp(now() + interval 3 days + interval 5 hours, now());
++------------------------------------------------------------------+
+| timestamp_cmp(now() + interval 3 days + interval 5 hours, now()) |
++------------------------------------------------------------------+
+| 1 |
++------------------------------------------------------------------+
+
+/* First argument 'earlier' than second argument. */
+select timestamp_cmp(now(), now() + interval 2 hours);
++------------------------------------------------+
+| timestamp_cmp(now(), now() + interval 2 hours) |
++------------------------------------------------+
+| -1 |
++------------------------------------------------+
+
+/* Both arguments represent the same point in time. */
+
+select timestamp_cmp(now(), now());
++-----------------------------+
+| timestamp_cmp(now(), now()) |
++-----------------------------+
+| 0 |
++-----------------------------+
+
+select timestamp_cmp(now() + interval 1 hour, now() + interval 60 minutes);
++---------------------------------------------------------------------+
+| timestamp_cmp(now() + interval 1 hour, now() + interval 60 minutes) |
++---------------------------------------------------------------------+
+| 0 |
++---------------------------------------------------------------------+
+
+/* Either argument NULL. */
+
+select timestamp_cmp(now(), null);
++----------------------------+
+| timestamp_cmp(now(), null) |
++----------------------------+
+| NULL |
++----------------------------+
+</codeblock>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="to_date">
+
+ <dt>
+ <codeph>to_date(timestamp)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">to_date() function</indexterm>
+ <b>Purpose:</b> Returns a string representation of the date field from a timestamp value.
+ <p>
+ <b>Return type:</b> <codeph>string</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="to_utc_timestamp">
+
+ <dt>
+ <codeph>to_utc_timestamp(timestamp, string timezone)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">to_utc_timestamp() function</indexterm>
+ <b>Purpose:</b> Converts a specified timestamp value in a specified time zone into the corresponding
+ value for the UTC time zone.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ <p>
+ <b>Usage notes:</b> Often used in combination with the <codeph>now()</codeph> function,
+ to translate local date and time values to the UTC time zone for consistent representation
+ on disk. The opposite of the <codeph>from_utc_timestamp()</codeph> function.
+ </p>
+ <p>
+ <b>Examples:</b> See discussion of time zones in <xref href="impala_timestamp.xml#timestamp"/>
+ for information about using this function for conversions between the local time zone and UTC.
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.4.0" id="trunc">
+
+ <dt>
+ <codeph>trunc(timestamp, string unit)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">trunc() function</indexterm>
+ <b>Purpose:</b> Strips off fields from a <codeph>TIMESTAMP</codeph> value.
+ <p>
+ <b>Unit argument:</b> The <codeph>unit</codeph> argument value is case-sensitive. This argument string
+ can be one of:
+<!-- Some but not all of the arguments from http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084 are supported here.
+ Impala doesn't support 2-digit years or ISO-related years or values derived from ISO years.
+-->
+ <ul>
+ <li>
+ <codeph>SYYYY</codeph>, <codeph>YYYY</codeph>, <codeph>YEAR</codeph>, <codeph>SYEAR</codeph>,
+ <codeph>YYY</codeph>, <codeph>YY</codeph>, <codeph>Y</codeph>: Year.
+ </li>
+
+ <li>
+ <codeph>Q</codeph>: Quarter.
+ </li>
+
+ <li>
+ <codeph>MONTH</codeph>, <codeph>MON</codeph>, <codeph>MM</codeph>, <codeph>RM</codeph>: Month.
+ </li>
+
+ <li>
+ <codeph>WW</codeph>, <codeph>W</codeph>: Same day of the week as the first day of the month.
+ </li>
+
+ <li>
+ <codeph>DDD</codeph>, <codeph>DD</codeph>, <codeph>J</codeph>: Day.
+ </li>
+
+ <li>
+ <codeph>DAY</codeph>, <codeph>DY</codeph>, <codeph>D</codeph>: Starting day of the week.
+ (Not necessarily the current day.)
+ </li>
+
+ <li>
+ <codeph>HH</codeph>, <codeph>HH12</codeph>, <codeph>HH24</codeph>: Hour. A
+ <codeph>TIMESTAMP</codeph> value truncated to the hour is always represented in 24-hour
+ notation, even for the <codeph>HH12</codeph> argument string.
+ </li>
+
+ <li>
+ <codeph>MI</codeph>: Minute.
+ </li>
+ </ul>
+ </p>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p>
+ Typically used in <codeph>GROUP BY</codeph> queries to aggregate results from the
+ same hour, day, week, month, quarter, and so on. You can also use this function in an <codeph>INSERT
+ ... SELECT</codeph> into a partitioned table to divide <codeph>TIMESTAMP</codeph> values into the
+ correct partition.
+ </p>
+ <p>
+ Because the return value is a <codeph>TIMESTAMP</codeph>, if you cast the result of
+ <codeph>TRUNC()</codeph> to <codeph>STRING</codeph>, you will often see zeroed-out portions such as
+ <codeph>00:00:00</codeph> in the time field. If you only need the individual units such as hour, day,
+ month, or year, use the <codeph>EXTRACT()</codeph> function instead. If you need the individual units
+ from a truncated <codeph>TIMESTAMP</codeph> value, run the <codeph>TRUNCATE()</codeph> function on the
+ original value, then run <codeph>EXTRACT()</codeph> on the result.
+ </p>
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="unix_timestamp">
+
+ <dt>
+ <codeph>unix_timestamp(), unix_timestamp(string datetime), unix_timestamp(string datetime, string
+ format), unix_timestamp(timestamp datetime)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">unix_timestamp() function</indexterm>
+ <b>Purpose:</b> Returns an integer value representing the current date and time as a delta from the Unix
+ epoch, or converts from a specified date and time value represented as a <codeph>TIMESTAMP</codeph> or
+ <codeph>STRING</codeph>.
+ <p>
+ <b>Return type:</b> <codeph rev="2.2.0">bigint</codeph>
+ </p>
+ <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/>
+ <p rev="1.3.0">
+ See <codeph>from_unixtime()</codeph> for details about the patterns you can use in
+ the <codeph>format</codeph> string to represent the position of year, month, day, and so on in the
+ <codeph>date</codeph> string. In Impala 1.3 and higher, you have more flexibility to switch the
+ positions of elements and use different separator characters.
+ </p>
+ <p rev="2.2.3">
+ In CDH 5.4.3 and higher, you can include a trailing uppercase <codeph>Z</codeph> qualifier
+ to indicate <q>Zulu</q> time, a synonym for UTC.
+ </p>
+ <p rev="2.3.0">
+ In CDH 5.5.0 and higher, you can include a timezone offset specified as minutes and hours,
+ provided you also specify the details in the format string argument. The offset is specified in the format
+ string as a plus or minus sign followed by <codeph>hh:mm</codeph>, <codeph>hhmm</codeph>, or <codeph>hh</codeph>.
+ The <codeph>hh</codeph> must be lowercase, to distinguish it from the <codeph>HH</codeph> represent
+ hours in the actual time value. Currently, only numeric timezone offsets are allowed, not symbolic names.
+ </p>
+ <p conref="../shared/impala_common.xml#common/y2k38"/>
+ <p conref="../shared/impala_common.xml#common/datetime_function_chaining"/>
+ <p conref="../shared/impala_common.xml#common/timezone_conversion_caveat"/>
+ <p conref="../shared/impala_common.xml#common/example_blurb"/>
+ <p>
+ 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 <codeph>Z</codeph> 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.
+ </p>
+ <p>
+ The final two examples show how to specify a timezone offset of Pacific Daylight Saving Time, which is 7 hours earlier than UTC.
+ You can use the numeric offset <codeph>-07:00</codeph> and the equivalent suffix of <codeph>-hh:mm</codeph>
+ in the format string, or specify the mnemonic name for the time zone in a call to <codeph>to_utc_timestamp()</codeph>.
+ This particular date and time expressed in PDT translates to a different number than the same date and time expressed in UTC.
+ </p>
+<codeblock rev="2.3.0">
+-- 3 ways of expressing the same date/time in UTC and converting to an integer.
+
+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');
++------------------------------------------------------------------+
+| unix_timestamp('may 15, 2015 12:00:00', 'mmm dd, yyyy hh:mm:ss') |
++------------------------------------------------------------------+
+| 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');
++--------------------------------------------------------------------------+
+| unix_timestamp('2015-05-15 12:00:00-07:00', 'yyyy-mm-dd hh:mm:ss-hh:mm') |
++--------------------------------------------------------------------------+
+| 1431716400 |
++--------------------------------------------------------------------------+
+
+select unix_timestamp(to_utc_timestamp('2015-05-15 12:00:00', 'PDT'))
++----------------------------------------------------------------+
+| unix_timestamp(to_utc_timestamp('2015-05-15 12:00:00', 'pdt')) |
++----------------------------------------------------------------+
+| 1431716400 |
++----------------------------------------------------------------+
+</codeblock>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="weekofyear">
+
+ <dt>
+ <!-- <codeph>weekofyear(string date)</codeph> -->
+ <codeph>weekofyear(timestamp date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">weekofyear() function</indexterm>
+ <b>Purpose:</b> Returns the corresponding week (1-53) from the date portion of a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="weeks_add">
+
+ <dt>
+ <codeph>weeks_add(timestamp date, int weeks)</codeph>, <codeph>weeks_add(timestamp date, bigint
+ weeks)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">weeks_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of weeks.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="weeks_sub">
+
+ <dt>
+ <codeph>weeks_sub(timestamp date, int weeks)</codeph>, <codeph>weeks_sub(timestamp date, bigint
+ weeks)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">weeks_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of weeks.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry id="year">
+
+ <dt>
+ <!-- <codeph>year(string date)</codeph> -->
+ <codeph>year(timestamp date)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">year() function</indexterm>
+ <b>Purpose:</b> Returns the year field from the date portion of a <codeph>TIMESTAMP</codeph>.
+ <p>
+ <b>Return type:</b> <codeph>int</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="years_add">
+
+ <dt>
+ <codeph>years_add(timestamp date, int years)</codeph>, <codeph>years_add(timestamp date, bigint
+ years)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">years_add() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time plus some number of years.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+
+ <dlentry rev="1.3.0" id="years_sub">
+
+ <dt>
+ <codeph>years_sub(timestamp date, int years)</codeph>, <codeph>years_sub(timestamp date, bigint
+ years)</codeph>
+ </dt>
+
+ <dd>
+ <indexterm audience="Cloudera">years_sub() function</indexterm>
+ <b>Purpose:</b> Returns the specified date and time minus some number of years.
+ <p>
+ <b>Return type:</b> <codeph>timestamp</codeph>
+ </p>
+ </dd>
+
+ </dlentry>
+ </dl>
+ </conbody>
+</concept>
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/463ddf92/docs/topics/impala_ddl.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_ddl.xml b/docs/topics/impala_ddl.xml
new file mode 100644
index 0000000..8e6a3bd
--- /dev/null
+++ b/docs/topics/impala_ddl.xml
@@ -0,0 +1,150 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
+<concept id="ddl">
+
+ <title>DDL Statements</title>
+ <prolog>
+ <metadata>
+ <data name="Category" value="Impala"/>
+ <data name="Category" value="SQL"/>
+ <data name="Category" value="DDL"/>
+ <data name="Category" value="Data Analysts"/>
+ <data name="Category" value="Developers"/>
+ <data name="Category" value="Schemas"/>
+ <data name="Category" value="Tables"/>
+ <data name="Category" value="Databases"/>
+ </metadata>
+ </prolog>
+
+ <conbody>
+
+ <p>
+ DDL refers to <q>Data Definition Language</q>, a subset of SQL statements that change the structure of the
+ database schema in some way, typically by creating, deleting, or modifying schema objects such as databases,
+ tables, and views. Most Impala DDL statements start with the keywords <codeph>CREATE</codeph>,
+ <codeph>DROP</codeph>, or <codeph>ALTER</codeph>.
+ </p>
+
+ <p>
+ The Impala DDL statements are:
+ </p>
+
+ <ul>
+ <li>
+ <xref href="impala_alter_table.xml#alter_table"/>
+ </li>
+
+ <li>
+ <xref href="impala_alter_view.xml#alter_view"/>
+ </li>
+
+ <li>
+ <xref href="impala_compute_stats.xml#compute_stats"/>
+ </li>
+
+ <li>
+ <xref href="impala_create_database.xml#create_database"/>
+ </li>
+
+ <li>
+ <xref href="impala_create_function.xml#create_function"/>
+ </li>
+
+ <li rev="2.0.0">
+ <xref href="impala_create_role.xml#create_role"/>
+ </li>
+
+ <li>
+ <xref href="impala_create_table.xml#create_table"/>
+ </li>
+
+ <li>
+ <xref href="impala_create_view.xml#create_view"/>
+ </li>
+
+ <li>
+ <xref href="impala_drop_database.xml#drop_database"/>
+ </li>
+
+ <li>
+ <xref href="impala_drop_function.xml#drop_function"/>
+ </li>
+
+ <li rev="2.0.0">
+ <xref href="impala_drop_role.xml#drop_role"/>
+ </li>
+
+ <li>
+ <xref href="impala_drop_table.xml#drop_table"/>
+ </li>
+
+ <li>
+ <xref href="impala_drop_view.xml#drop_view"/>
+ </li>
+
+ <li rev="2.0.0">
+ <xref href="impala_grant.xml#grant"/>
+ </li>
+
+ <li rev="2.0.0">
+ <xref href="impala_revoke.xml#revoke"/>
+ </li>
+ </ul>
+
+ <p>
+ After Impala executes a DDL command, information about available tables, columns, views, partitions, and so
+ on is automatically synchronized between all the Impala nodes in a cluster. (Prior to Impala 1.2, you had to
+ issue a <codeph>REFRESH</codeph> or <codeph>INVALIDATE METADATA</codeph> statement manually on the other
+ nodes to make them aware of the changes.)
+ </p>
+
+ <p>
+ If the timing of metadata updates is significant, for example if you use round-robin scheduling where each
+ query could be issued through a different Impala node, you can enable the
+ <xref href="impala_sync_ddl.xml#sync_ddl">SYNC_DDL</xref> query option to make the DDL statement wait until
+ all nodes have been notified about the metadata changes.
+ </p>
+
+ <p rev="2.2.0">
+ See <xref href="impala_s3.xml#s3"/> for details about how Impala DDL statements interact with
+ tables and partitions stored in the Amazon S3 filesystem.
+ </p>
+
+ <p>
+ Although the <codeph>INSERT</codeph> statement is officially classified as a DML (data manipulation language)
+ statement, it also involves metadata changes that must be broadcast to all Impala nodes, and so is also
+ affected by the <codeph>SYNC_DDL</codeph> query option.
+ </p>
+
+ <p>
+ Because the <codeph>SYNC_DDL</codeph> query option makes each DDL operation take longer than normal, you
+ might only enable it before the last DDL operation in a sequence. For example, if you are running a script
+ that issues multiple of DDL operations to set up an entire new schema, add several new partitions, and so on,
+ you might minimize the performance overhead by enabling the query option only before the last
+ <codeph>CREATE</codeph>, <codeph>DROP</codeph>, <codeph>ALTER</codeph>, or <codeph>INSERT</codeph> statement.
+ The script only finishes when all the relevant metadata changes are recognized by all the Impala nodes, so
+ you could connect to any node and issue queries through it.
+ </p>
+
+ <p>
+ The classification of DDL, DML, and other statements is not necessarily the same between Impala and Hive.
+ Impala organizes these statements in a way intended to be familiar to people familiar with relational
+ databases or data warehouse products. Statements that modify the metastore database, such as <codeph>COMPUTE
+ STATS</codeph>, are classified as DDL. Statements that only query the metastore database, such as
+ <codeph>SHOW</codeph> or <codeph>DESCRIBE</codeph>, are put into a separate category of utility statements.
+ </p>
+
+ <note>
+ The query types shown in the Impala debug web user interface might not match exactly the categories listed
+ here. For example, currently the <codeph>USE</codeph> statement is shown as DDL in the debug web UI. The
+ query types shown in the debug web UI are subject to change, for improved consistency.
+ </note>
+
+ <p conref="../shared/impala_common.xml#common/related_info"/>
+
+ <p>
+ The other major classifications of SQL statements are data manipulation language (see
+ <xref href="impala_dml.xml#dml"/>) and queries (see <xref href="impala_select.xml#select"/>).
+ </p>
+ </conbody>
+</concept>
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/463ddf92/docs/topics/impala_debug_action.xml
----------------------------------------------------------------------
diff --git a/docs/topics/impala_debug_action.xml b/docs/topics/impala_debug_action.xml
new file mode 100644
index 0000000..b931979
--- /dev/null
+++ b/docs/topics/impala_debug_action.xml
@@ -0,0 +1,28 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE concept PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
+<concept id="debug_action">
+
+ <title>DEBUG_ACTION Query Option</title>
+ <prolog>
+ <metadata>
+ <data name="Category" value="Impala"/>
+ <data name="Category" value="Impala Query Options"/>
+ </metadata>
+ </prolog>
+
+ <conbody>
+
+ <p>
+ <indexterm audience="Cloudera">DEBUG_ACTION query option</indexterm>
+ Introduces artificial problem conditions within queries. For internal Cloudera debugging and troubleshooting.
+ </p>
+
+ <p>
+ <b>Type:</b> <codeph>STRING</codeph>
+ </p>
+
+ <p>
+ <b>Default:</b> empty string
+ </p>
+ </conbody>
+</concept>