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>
