http://git-wip-us.apache.org/repos/asf/impala/blob/e8ee827a/docs/topics/impala_datetime_functions.xml ---------------------------------------------------------------------- diff --git a/docs/topics/impala_datetime_functions.xml b/docs/topics/impala_datetime_functions.xml index da52cc8..02d2771 100644 --- a/docs/topics/impala_datetime_functions.xml +++ b/docs/topics/impala_datetime_functions.xml @@ -1,4 +1,5 @@ -<?xml version="1.0" encoding="UTF-8"?><!-- +<?xml version="1.0" encoding="UTF-8"?> +<!-- Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information @@ -20,7 +21,13 @@ under the License. <concept id="datetime_functions"> <title>Impala Date and Time Functions</title> - <titlealts audience="PDF"><navtitle>Date and Time Functions</navtitle></titlealts> + + <titlealts audience="PDF"> + + <navtitle>Date and Time Functions</navtitle> + + </titlealts> + <prolog> <metadata> <data name="Category" value="Impala"/> @@ -37,32 +44,32 @@ under the License. <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. + <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. + 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. + <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> @@ -73,39 +80,278 @@ under the License. Impala supports the following data and time functions: </p> -<!-- New for 2.3: -int_months_between -timeofday -timestamp_cmp -months_between ---> + <ul> + <li> + <xref href="#datetime_functions/add_months">ADD_MONTHS</xref> + </li> + + <li> + <xref href="#datetime_functions/adddate">ADDDATE</xref> + </li> + + <li> + <xref href="#datetime_functions/current_timestamp" + >CURRENT_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/date_add">DATE_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/date_part">DATE_PART</xref> + </li> + + <li> + <xref href="#datetime_functions/date_sub">DATE_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/date_trunc">DATE_TRUNC</xref> + </li> + + <li> + <xref href="#datetime_functions/datediff">DATEDIFF</xref> + </li> + + <li> + <xref href="#datetime_functions/day">DAY</xref> + </li> + + <li> + <xref href="#datetime_functions/dayname">DAYNAME</xref> + </li> + + <li> + <xref href="#datetime_functions/dayofweek">DAYOFWEEK</xref> + </li> + + <li> + <xref href="#datetime_functions/dayofyear">DAYOFYEAR</xref> + </li> + + <li> + <xref href="#datetime_functions/days_add">DAYS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/days_sub">DAYS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/extract">EXTRACT</xref> + </li> + + <li> + <xref href="#datetime_functions/from_timestamp">FROM_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/from_unixtime">FROM_UNIXTIME</xref> + </li> + + <li> + <xref href="#datetime_functions/from_utc_timestamp" + >FROM_UTC_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/hour">HOUR</xref> + </li> + + <li> + <xref href="#datetime_functions/hours_add">HOURS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/hours_sub">HOURS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/int_months_between" + >INT_MONTHS_BETWEEN</xref> + </li> + + <li> + <xref href="#datetime_functions/microseconds_add" + >MICROSECONDS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/microseconds_sub" + >MICROSECONDS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/millisecond">MILLISECOND</xref> + </li> + + <li> + <xref href="#datetime_functions/milliseconds_add" + >MILLISECONDS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/milliseconds_sub" + >MILLISECONDS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/minute">MINUTE</xref> + </li> + + <li> + <xref href="#datetime_functions/minutes_add">MINUTES_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/minutes_sub">MINUTES_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/month">MONTH</xref> + </li> + + <li> + <xref href="#datetime_functions/month">MONTHNAME</xref> + </li> + + <li> + <xref href="#datetime_functions/monthname">MONTHS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/months_between">MONTHS_BETWEEN</xref> + </li> + + <li> + <xref href="#datetime_functions/months_sub">MONTHS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/nanoseconds_add">NANOSECONDS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/nanoseconds_sub">NANOSECONDS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/next_day">NEXT_DAY</xref> + </li> + + <li> + <xref href="#datetime_functions/now">NOW</xref> + </li> + + <li> + <xref href="#datetime_functions/quarter">QUARTER</xref> + </li> + + <li> + <xref href="#datetime_functions/second">SECOND</xref> + </li> + + <li> + <xref href="#datetime_functions/seconds_add">SECONDS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/seconds_sub">SECONDS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/subdate">SUBDATE</xref> + </li> + + <li> + <xref href="#datetime_functions/timeofday">TIMEOFDAY</xref> + </li> + + <li> + <xref href="#datetime_functions/timestamp_cmp">TIMESTAMP_CMP</xref> + </li> + + <li> + <xref href="#datetime_functions/to_date">TO_DATE</xref> + </li> + + <li> + <xref href="#datetime_functions/to_timestamp">TO_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/to_utc_timestamp" + >TO_UTC_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/trunc">TRUNC</xref> + </li> + + <li> + <xref href="#datetime_functions/unix_timestamp">UNIX_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/utc_timestamp">UTC_TIMESTAMP</xref> + </li> + + <li> + <xref href="#datetime_functions/weekofyear">WEEKOFYEAR</xref> + </li> + + <li> + <xref href="#datetime_functions/weeks_add">WEEKS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/weeks_sub">WEEKS_SUB</xref> + </li> + + <li> + <xref href="#datetime_functions/year">YEAR</xref> + </li> + + <li> + <xref href="#datetime_functions/years_add">YEARS_ADD</xref> + </li> + + <li> + <xref href="#datetime_functions/years_sub">YEARS_SUB</xref> + </li> + </ul> <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> + ADD_MONTHS(TIMESTAMP date, INT months), ADD_MONTHS(TIMESTAMP date, BIGINT months) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> - Same as <codeph><xref href="#datetime_functions/months_add">months_add()</xref></codeph>. - Available in Impala 1.4 and higher. For - compatibility when porting code with vendor extensions. + Same as + <codeph><xref href="#datetime_functions/months_add" + >MONTHS_ADD()</xref></codeph>. + Available in Impala 1.4 and higher. For compatibility when porting code with vendor + extensions. </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following examples demonstrate adding months to construct the same - day of the month in a different month; how if the current day of the month - does not exist in the target month, the last day of that month is substituted; - and how a negative argument produces a return value from a previous month. + The following examples demonstrate adding months to construct the same day of the + month in a different month; how if the current day of the month does not exist in + the target month, the last day of that month is substituted; and how a negative + argument produces a return value from a previous month. </p> <codeblock> select now(), add_months(now(), 2); @@ -136,22 +382,24 @@ select now(), add_months(now(), -1); <dlentry rev="1.3.0" id="adddate"> <dt> - <codeph>adddate(timestamp startdate, int days)</codeph>, <codeph>adddate(timestamp startdate, bigint - days)</codeph>, + ADDDATE(TIMESTAMP startdate, INT days), ADDDATE(TIMESTAMP startdate, BIGINT days) </dt> <dd> - <indexterm audience="hidden">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>. + <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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following examples show how to add a number of days to a <codeph>TIMESTAMP</codeph>. - The number of days can also be negative, which gives the same effect as the <codeph>subdate()</codeph> function. + The following examples show how to add a number of days to a + <codeph>TIMESTAMP</codeph>. The number of days can also be negative, which gives the + same effect as the <codeph>subdate()</codeph> function. </p> <codeblock> select now() as right_now, adddate(now(), 30) as now_plus_30; @@ -175,15 +423,15 @@ select now() as right_now, adddate(now(), -15) as now_minus_15; <dlentry id="current_timestamp"> <dt> - <codeph>current_timestamp()</codeph> + CURRENT_TIMESTAMP() </dt> <dd> - <indexterm audience="hidden">current_timestamp() function</indexterm> - <b>Purpose:</b> Alias for the <codeph>now()</codeph> function. + <b>Purpose:</b> Alias for the <codeph>NOW()</codeph> function. <p> - <b>Return type:</b> <codeph>timestamp</codeph> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now(), current_timestamp(); @@ -208,25 +456,27 @@ select current_timestamp() as right_now, <dlentry id="date_add"> <dt> - <codeph>date_add(timestamp startdate, int days)</codeph>, <codeph>date_add(timestamp startdate, - <varname>interval_expression</varname>)</codeph> + DATE_ADD(TIMESTAMP startdate, INT days), DATE_ADD(TIMESTAMP startdate, + interval_expression) </dt> <dd> - <indexterm audience="hidden">date_add() function</indexterm> <b>Purpose:</b> Adds a specified number of days to a <codeph>TIMESTAMP</codeph> value. - <!-- Found this not to be true in latest release. I think the signature changed way back. +<!-- Found this not to be true in latest release. I think the signature changed way back. 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. + 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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> The following example shows the simplest usage, of adding a specified number of days to a <codeph>TIMESTAMP</codeph> value: @@ -239,12 +489,11 @@ select now() as right_now, date_add(now(), 7) as next_week; | 2016-05-20 11:03:48.687055000 | 2016-05-27 11:03:48.687055000 | +-------------------------------+-------------------------------+ </codeblock> - <p> The following examples show the shorthand notation of an <codeph>INTERVAL</codeph> - expression, instead of specifying the precise number of days. - The <codeph>INTERVAL</codeph> notation also lets you work with units smaller than - a single day. + expression, instead of specifying the precise number of days. The + <codeph>INTERVAL</codeph> notation also lets you work with units smaller than a + single day. </p> <codeblock> select now() as right_now, date_add(now(), interval 3 weeks) as in_3_weeks; @@ -261,12 +510,11 @@ select now() as right_now, date_add(now(), interval 6 hours) as in_6_hours; | 2016-05-20 11:13:51.492536000 | 2016-05-20 17:13:51.492536000 | +-------------------------------+-------------------------------+ </codeblock> - <p> Like all date/time functions that deal with months, <codeph>date_add()</codeph> - handles nonexistent dates past the end of a month by setting the date to the - last day of the month. The following example shows how the nonexistent date - April 31st is normalized to April 30th: + handles nonexistent dates past the end of a month by setting the date to the last + day of the month. The following example shows how the nonexistent date April 31st is + normalized to April 30th: </p> <codeblock> select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31st'; @@ -283,18 +531,21 @@ select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31 <dlentry rev="2.0.0" id="date_part"> <dt> - <codeph>date_part(string, timestamp)</codeph> + DATE_PART(STRING part, TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">date_part() function</indexterm> <b>Purpose:</b> Similar to - <xref href="impala_datetime_functions.xml#datetime_functions/extract"><codeph>EXTRACT()</codeph></xref>, - with the argument order reversed. Supports the same date and time units as <codeph>EXTRACT()</codeph>. - For compatibility with SQL code containing vendor extensions. + <xref + href="impala_datetime_functions.xml#datetime_functions/extract" + ><codeph>EXTRACT()</codeph></xref>, + 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>bigint</codeph> + <b>Return type:</b> <codeph>BIGINT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select date_part('year',now()) as current_year; @@ -318,28 +569,30 @@ select date_part('hour',now()) as hour_of_day; <dlentry id="date_sub"> <dt> - <codeph>date_sub(timestamp startdate, int days)</codeph>, <codeph>date_sub(timestamp startdate, - <varname>interval_expression</varname>)</codeph> + DATE_SUB(TIMESTAMP startdate, INT days), DATE_SUB(TIMESTAMP startdate, + interval_expression) </dt> <dd> - <indexterm audience="hidden">date_sub() function</indexterm> - <b>Purpose:</b> Subtracts a specified number of days from a <codeph>TIMESTAMP</codeph> value. - <!-- Found this not to be true in latest release. I think the signature changed way back. + <b>Purpose:</b> Subtracts a specified number of days from a <codeph>TIMESTAMP</codeph> + value. +<!-- Found this not to be true in latest release. I think the signature changed way back. 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. + 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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following example shows the simplest usage, of subtracting a specified number of days - from a <codeph>TIMESTAMP</codeph> value: + The following example shows the simplest usage, of subtracting a specified number of + days from a <codeph>TIMESTAMP</codeph> value: </p> <codeblock> select now() as right_now, date_sub(now(), 7) as last_week; @@ -351,9 +604,9 @@ select now() as right_now, date_sub(now(), 7) as last_week; </codeblock> <p> The following examples show the shorthand notation of an <codeph>INTERVAL</codeph> - expression, instead of specifying the precise number of days. - The <codeph>INTERVAL</codeph> notation also lets you work with units smaller than - a single day. + expression, instead of specifying the precise number of days. The + <codeph>INTERVAL</codeph> notation also lets you work with units smaller than a + single day. </p> <codeblock> select now() as right_now, date_sub(now(), interval 3 weeks) as 3_weeks_ago; @@ -370,12 +623,11 @@ select now() as right_now, date_sub(now(), interval 6 hours) as 6_hours_ago; | 2016-05-20 11:23:35.439631000 | 2016-05-20 05:23:35.439631000 | +-------------------------------+-------------------------------+ </codeblock> - <p> Like all date/time functions that deal with months, <codeph>date_add()</codeph> - handles nonexistent dates past the end of a month by setting the date to the - last day of the month. The following example shows how the nonexistent date - April 31st is normalized to April 30th: + handles nonexistent dates past the end of a month by setting the date to the last + day of the month. The following example shows how the nonexistent date April 31st is + normalized to April 30th: </p> <codeblock> select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st'; @@ -392,72 +644,117 @@ select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31 <dlentry rev="2.11.0 IMPALA-5317" id="date_trunc"> <dt> - <codeph>date_trunc(string unit, timestamp)</codeph> + DATE_TRUNC(STRING unit, TIMESTAMP ts) </dt> <dd> - <indexterm audience="hidden">date_trunc() function</indexterm> - <b>Purpose:</b> Truncates a <codeph>TIMESTAMP</codeph> value to the specified precision. + <b>Purpose:</b> Truncates a <codeph>TIMESTAMP</codeph> value to the specified + precision. <p> <b>Unit argument:</b> The <codeph>unit</codeph> argument value for truncating - <codeph>TIMESTAMP</codeph> values is not case-sensitive. This argument string - can be one of: + <codeph>TIMESTAMP</codeph> values is not case-sensitive. This argument string can be + one of: </p> <ul> - <li>microseconds</li> - <li>milliseconds</li> - <li>second</li> - <li>minute</li> - <li>hour</li> - <li>day</li> - <li>week</li> - <li>month</li> - <li>year</li> - <li>decade</li> - <li>century</li> - <li>millennium</li> + <li> + microseconds + </li> + + <li> + milliseconds + </li> + + <li> + second + </li> + + <li> + minute + </li> + + <li> + hour + </li> + + <li> + day + </li> + + <li> + week + </li> + + <li> + month + </li> + + <li> + year + </li> + + <li> + decade + </li> + + <li> + century + </li> + + <li> + millennium + </li> </ul> <p> - For example, calling <codeph>date_trunc('hour',ts)</codeph> truncates - <codeph>ts</codeph> to the beginning of the corresponding hour, with - all minutes, seconds, milliseconds, and so on set to zero. Calling - <codeph>date_trunc('milliseconds',ts)</codeph> truncates - <codeph>ts</codeph> to the beginning of the corresponding millisecond, - with all microseconds and nanoseconds set to zero. + For example, calling <codeph>DATE_TRUNC('hour',ts)</codeph> truncates + <codeph>ts</codeph> to the beginning of the corresponding hour, with all minutes, + seconds, milliseconds, and so on set to zero. Calling + <codeph>DATE_TRUNC('milliseconds',ts)</codeph> truncates <codeph>ts</codeph> to the + beginning of the corresponding millisecond, with all microseconds and nanoseconds + set to zero. </p> + <note> - The sub-second units are specified in plural form. All units representing - one second or more are specified in singular form. + The sub-second units are specified in plural form. All units representing one second + or more are specified in singular form. </note> + <p conref="../shared/impala_common.xml#common/added_in_2110"/> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> - Although this function is similar to calling <codeph>TRUNC()</codeph> - with a <codeph>TIMESTAMP</codeph> argument, the order of arguments - and the recognized units are different between <codeph>TRUNC()</codeph> - and <codeph>DATE_TRUNC()</codeph>. Therefore, these functions are not - interchangeable. + Although this function is similar to calling <codeph>TRUNC()</codeph> with a + <codeph>TIMESTAMP</codeph> argument, the order of arguments and the recognized units + are different between <codeph>TRUNC()</codeph> and <codeph>DATE_TRUNC()</codeph>. + Therefore, these functions are not interchangeable. </p> + <p> - This function is 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. + This function is 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>DATE_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. + <codeph>DATE_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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following examples show how to call <codeph>DATE_TRUNC()</codeph> with different unit values: + The following examples show how to call <codeph>DATE_TRUNC()</codeph> with different + unit values: </p> <codeblock> select now(), date_trunc('second', now()); @@ -488,30 +785,33 @@ select now(), date_trunc('millennium', now()); <dlentry id="datediff"> <dt> - <codeph>datediff(timestamp enddate, timestamp startdate)</codeph> + DATEDIFF(TIMESTAMP enddate, TIMESTAMP startdate) </dt> <dd> - <indexterm audience="hidden">datediff() function</indexterm> - <b>Purpose:</b> Returns the number of days between two <codeph>TIMESTAMP</codeph> values. + <b>Purpose:</b> Returns the number of days between two <codeph>TIMESTAMP</codeph> + values. <p> - <b>Return type:</b> <codeph>int</codeph> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> - If the first argument represents a later date than the second argument, - the return value is positive. If both arguments represent the same date, - the return value is zero. The time portions of the <codeph>TIMESTAMP</codeph> - values are irrelevant. For example, 11:59 PM on one day and 12:01 on the next - day represent a <codeph>datediff()</codeph> of -1 because the date/time values - represent different days, even though the <codeph>TIMESTAMP</codeph> values differ by only 2 minutes. + If the first argument represents a later date than the second argument, the return + value is positive. If both arguments represent the same date, the return value is + zero. The time portions of the <codeph>TIMESTAMP</codeph> values are irrelevant. For + example, 11:59 PM on one day and 12:01 on the next day represent a + <codeph>datediff()</codeph> of -1 because the date/time values represent different + days, even though the <codeph>TIMESTAMP</codeph> values differ by only 2 minutes. </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following example shows how comparing a <q>late</q> value with - an <q>earlier</q> value produces a positive number. In this case, - the result is (365 * 5) + 1, because one of the intervening years is - a leap year. + The following example shows how comparing a <q>late</q> value with an <q>earlier</q> + value produces a positive number. In this case, the result is (365 * 5) + 1, because + one of the intervening years is a leap year. </p> <codeblock> select now() as right_now, datediff(now() + interval 5 years, now()) as in_5_years; @@ -523,11 +823,11 @@ select now() as right_now, datediff(now() + interval 5 years, now()) as in_5_yea </codeblock> <p> The following examples show how the return value represent the number of days - between the associated dates, regardless of the time portion of each <codeph>TIMESTAMP</codeph>. - For example, different times on the same day produce a <codeph>date_diff()</codeph> of 0, - regardless of which one is earlier or later. But if the arguments represent different dates, - <codeph>date_diff()</codeph> returns a non-zero integer value, regardless of the time portions - of the dates. + between the associated dates, regardless of the time portion of each + <codeph>TIMESTAMP</codeph>. For example, different times on the same day produce a + <codeph>date_diff()</codeph> of 0, regardless of which one is earlier or later. But + if the arguments represent different dates, <codeph>date_diff()</codeph> returns a + non-zero integer value, regardless of the time portions of the dates. </p> <codeblock> select now() as right_now, datediff(now(), now() + interval 4 hours) as in_4_hours; @@ -565,22 +865,22 @@ select now() as right_now, datediff(now(), now() - interval 18 hours) as 18_hour <dlentry id="day"> <dt> - <codeph>day(timestamp date), <ph id="dayofmonth" - >dayofmonth(timestamp date)</ph></codeph> + DAY(TIMESTAMP date), DAYOFMONTH(TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">day() function</indexterm> - <b>Purpose:</b> Returns the day field from the date portion of a <codeph>TIMESTAMP</codeph>. - The value represents the day of the month, therefore is in the range 1-31, or less for - months without 31 days. + <b>Purpose:</b> Returns the day field from the date portion of a + <codeph>TIMESTAMP</codeph>. The value represents the day of the month, therefore is in + the range 1-31, or less for months without 31 days. <p> - <b>Return type:</b> <codeph>int</codeph> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following examples show how the day value corresponds to the day - of the month, resetting back to 1 at the start of each month. + The following examples show how the day value corresponds to the day of the month, + resetting back to 1 at the start of each month. </p> <codeblock> select now(), day(now()); @@ -605,8 +905,8 @@ select now() + interval 12 days, day(now() + interval 12 days); +-------------------------------+-------------------------------+ </codeblock> <p> - The following examples show how the day value is <codeph>NULL</codeph> - for nonexistent dates or misformatted date strings. + The following examples show how the day value is <codeph>NULL</codeph> for + nonexistent dates or misformatted date strings. </p> <codeblock> -- 2016 is a leap year, so it has a Feb. 29. @@ -641,23 +941,24 @@ select day('2016-02-028'); <dlentry rev="1.2" id="dayname"> <dt> - <codeph>dayname(timestamp date)</codeph> + DAYNAME(TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">dayname() function</indexterm> - <b>Purpose:</b> Returns the day field from a <codeph>TIMESTAMP</codeph> value, 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. + <b>Purpose:</b> Returns the day field from a <codeph>TIMESTAMP</codeph> value, + 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> + <b>Return type:</b> <codeph>STRING</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following examples show the day name associated with - <codeph>TIMESTAMP</codeph> values representing different days. + The following examples show the day name associated with <codeph>TIMESTAMP</codeph> + values representing different days. </p> <codeblock> select now() as right_now, @@ -684,16 +985,17 @@ select now() + interval 1 day as tomorrow, <dlentry rev="1.1" id="dayofweek"> <dt> - <codeph>dayofweek(timestamp date)</codeph> + DAYOFWEEK(TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">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). + <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> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -712,23 +1014,23 @@ select now() as right_now, <dlentry rev="1.3.0" id="dayofyear"> <dt> - <codeph>dayofyear(timestamp date)</codeph> + DAYOFYEAR(TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">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). + <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> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following examples show return values from the - <codeph>dayofyear()</codeph> function. The same date - in different years returns a different day number - for all dates after February 28, - because 2016 is a leap year while 2015 is not a leap year. + The following examples show return values from the <codeph>DAYOFYEAR()</codeph> + function. The same date in different years returns a different day number for all + dates after February 28, because 2016 is a leap year while 2015 is not a leap year. </p> <codeblock> select now() as right_now, @@ -754,18 +1056,18 @@ select now() - interval 1 year as last_year, <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> + DAYS_ADD(TIMESTAMP startdate, INT days), DAYS_ADD(TIMESTAMP startdate, BIGINT days) </dt> <dd> - <indexterm audience="hidden">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>. + <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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, days_add(now(), 31) as 31_days_later; @@ -782,18 +1084,18 @@ select now() as right_now, days_add(now(), 31) as 31_days_later; <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> + DAYS_SUB(TIMESTAMP startdate, INT days), DAYS_SUB(TIMESTAMP startdate, BIGINT days) </dt> <dd> - <indexterm audience="hidden">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>. + <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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, days_sub(now(), 31) as 31_days_ago; @@ -810,61 +1112,59 @@ select now() as right_now, days_sub(now(), 31) as 31_days_ago; <dlentry rev="1.4.0" id="extract"> <dt> - <codeph>extract(timestamp, string unit)</codeph>, <codeph rev="2.0.0">extract(unit FROM timestamp)</codeph> + EXTRACT(TIMESTAMP ts, STRING unit), EXTRACT(unit FROM TIMESTAMP ts) </dt> <dd> - <indexterm audience="hidden">extract() function</indexterm> <b>Purpose:</b> Returns one of the numeric date or time fields from a - <codeph>TIMESTAMP</codeph> value. <p> + <codeph>TIMESTAMP</codeph> value. + <p> <b>Unit argument:</b> The <codeph>unit</codeph> string can be one of - <codeph>epoch</codeph>, <codeph>year</codeph>, - <codeph>quarter</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>If you specify <codeph>millisecond</codeph> - for the <codeph>unit</codeph> argument, the function returns the - seconds component and the milliseconds component. For example, - <codeph>extract(cast('2006-05-12 18:27:28.123456789' as - timestamp), 'MILLISECOND')</codeph> will return - <codeph>28123</codeph>. </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"); + <codeph>epoch</codeph>, <codeph>year</codeph>, <codeph>quarter</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> + If you specify <codeph>millisecond</codeph> for the <codeph>unit</codeph> argument, + the function returns the seconds component and the milliseconds component. For + example, <codeph>EXTRACT(CAST('2006-05-12 18:27:28.123456789' AS TIMESTAMP), + 'MILLISECOND')</codeph> will return <codeph>28123</codeph>. + </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 + 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>bigint</codeph> + + <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> statement to insert 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 conref="../shared/impala_common.xml#common/example_blurb"/> - <codeblock> -select now() as right_now, - extract(year from now()) as this_year, - extract(month from now()) as this_month; -+-------------------------------+-----------+------------+ -| right_now | this_year | this_month | -+-------------------------------+-----------+------------+ -| 2016-05-31 11:18:43.310328000 | 2016 | 5 | -+-------------------------------+-----------+------------+ -select now() as right_now, - extract(day from now()) as this_day, - extract(hour from now()) as this_hour; + <p> + <b>Return type:</b> <codeph>BIGINT</codeph> + </p> + + <p conref="../shared/impala_common.xml#common/example_blurb"/> +<codeblock> +SELECT NOW() AS right_now, + EXTRACT(day FROM NOW()) AS this_day, + EXTRACT(hour FROM NOW()) AS this_hour; +-------------------------------+----------+-----------+ | right_now | this_day | this_hour | +-------------------------------+----------+-----------+ @@ -878,55 +1178,57 @@ select now() as right_now, <dlentry id="from_timestamp" rev="2.3.0 IMPALA-2190"> <dt> - <codeph>from_timestamp(datetime timestamp, pattern string)</codeph> + FROM_TIMESTAMP(TIMESTAMP datetime, STRING pattern), FROM_TIMESTAMP(STRING datetime, + STRING pattern) </dt> <dd> - <indexterm audience="hidden">from_timestamp() function</indexterm> - <b>Purpose:</b> Converts a <codeph>TIMESTAMP</codeph> value into a - string representing the same value. + <b>Purpose:</b> Converts a <codeph>TIMESTAMP</codeph> value into a string representing + the same value. <p> - <b>Return type:</b> <codeph>string</codeph> + <b>Return type:</b> <codeph>STRING</codeph> </p> + <p conref="../shared/impala_common.xml#common/added_in_230"/> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> - The <codeph>from_timestamp()</codeph> function provides a flexible way to convert <codeph>TIMESTAMP</codeph> - values into arbitrary string formats for reporting purposes. + The <codeph>FROM_TIMESTAMP()</codeph> function provides a flexible way to convert + <codeph>TIMESTAMP</codeph> values into arbitrary string formats for reporting + purposes. </p> + <p> - Because Impala implicitly converts string values into <codeph>TIMESTAMP</codeph>, you can - pass date/time values represented as strings (in the standard <codeph>yyyy-MM-dd HH:mm:ss.SSS</codeph> format) - to this function. The result is a string using different separator characters, order of fields, spelled-out month + Because Impala implicitly converts string values into <codeph>TIMESTAMP</codeph>, + you can pass date/time values represented as strings (in the standard + <codeph>yyyy-MM-dd HH:mm:ss.SSS</codeph> format) to this function. The result is a + string using different separator characters, order of fields, spelled-out month names, or other variation of the date/time string representation. </p> + <p> - The allowed tokens for the pattern string are the same as for the <codeph>from_unixtime()</codeph> function. + The allowed tokens for the pattern string are the same as for the + <codeph>FROM_UNIXTIME()</codeph> function. </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> The following examples show different ways to format a <codeph>TIMESTAMP</codeph> value as a string: </p> <codeblock> --- Reformat arbitrary TIMESTAMP value. -select from_timestamp(now(), 'yyyy/MM/dd'); +-- Reformat a TIMESTAMP value. +SELECT FROM_TIMESTAMP(NOW(), 'yyyy/MM/dd'); +-------------------------------------+ | from_timestamp(now(), 'yyyy/mm/dd') | +-------------------------------------+ -| 2017/10/01 | +| 2018/10/09 | +-------------------------------------+ --- Reformat string literal representing date/time. -select from_timestamp('1984-09-25', 'yyyy/MM/dd'); -+--------------------------------------------+ -| from_timestamp('1984-09-25', 'yyyy/mm/dd') | -+--------------------------------------------+ -| 1984/09/25 | -+--------------------------------------------+ - -- Alternative format for reporting purposes. -select from_timestamp('1984-09-25 16:45:30.125', 'MMM dd, yyyy HH:mm:ss.SSS'); +SELECT FROM_TIMESTAMP('1984-09-25 16:45:30.125', 'MMM dd, yyyy HH:mm:ss.SSS'); +------------------------------------------------------------------------+ | from_timestamp('1984-09-25 16:45:30.125', 'mmm dd, yyyy hh:mm:ss.sss') | +------------------------------------------------------------------------+ @@ -940,75 +1242,75 @@ select from_timestamp('1984-09-25 16:45:30.125', 'MMM dd, yyyy HH:mm:ss.SSS'); <dlentry id="from_unixtime"> <dt> - <codeph>from_unixtime(bigint unixtime[, string format])</codeph> + FROM_UNIXTIME(BIGINT unixtime[, STRING format]) </dt> <dd> - <indexterm audience="hidden">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. + <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> + <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. + 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"/> + 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"> <p 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"/>. + 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"/>. </p> </note> + <p conref="../shared/impala_common.xml#common/example_blurb"/> -<codeblock>select from_unixtime(1392394861,"yyyy-MM-dd HH:mm:ss.SSSS"); +<codeblock>SELECT FROM_UNIXTIME(1392394861,'yyyy-MM-dd HH:mm:ss.SSSS'); +-------------------------------------------------------+ | from_unixtime(1392394861, 'yyyy-mm-dd hh:mm:ss.ssss') | +-------------------------------------------------------+ | 2014-02-14 16:21:01.0000 | +-------------------------------------------------------+ -select from_unixtime(1392394861,"yyyy-MM-dd"); -+-----------------------------------------+ -| from_unixtime(1392394861, 'yyyy-mm-dd') | -+-----------------------------------------+ -| 2014-02-14 | -+-----------------------------------------+ - -select from_unixtime(1392394861,"HH:mm:ss.SSSS"); +SELECT FROM_UNIXTIME(1392394861,'HH:mm:ss.SSSS'); +--------------------------------------------+ | from_unixtime(1392394861, 'hh:mm:ss.ssss') | +--------------------------------------------+ | 16:21:01.0000 | +--------------------------------------------+ - -select from_unixtime(1392394861,"HH:mm:ss"); -+---------------------------------------+ -| from_unixtime(1392394861, 'hh:mm:ss') | -+---------------------------------------+ -| 16:21:01 | -+---------------------------------------+</codeblock> - <p conref="../shared/impala_common.xml#common/datetime_function_chaining"/> +</codeblock> + <p + conref="../shared/impala_common.xml#common/datetime_function_chaining" + /> </dd> </dlentry> @@ -1016,29 +1318,37 @@ select from_unixtime(1392394861,"HH:mm:ss"); <dlentry id="from_utc_timestamp"> <dt> - <codeph>from_utc_timestamp(timestamp, string timezone)</codeph> + FROM_UTC_TIMESTAMP(TIMESTAMP ts, STRING timezone) </dt> <dd> - <indexterm audience="hidden">from_utc_timestamp() function</indexterm> - <b>Purpose:</b> Converts a specified UTC timestamp value into the appropriate value for a specified time - zone. + <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> + <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. + <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 conref="../shared/impala_common.xml#common/current_timezone_tip"/> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - 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. + 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> + <p> - The following example shows how when <codeph>TIMESTAMP</codeph> values representing the UTC time zone - are stored in a table, a query can display the equivalent local date and time for a different time zone. + The following example shows how when <codeph>TIMESTAMP</codeph> values representing + the UTC time zone are stored in a table, a query can display the equivalent local + date and time for a different time zone. </p> <codeblock> with t1 as (select cast('2016-06-02 16:25:36.116143000' as timestamp) as utc_datetime) @@ -1053,11 +1363,10 @@ with t1 as (select cast('2016-06-02 16:25:36.116143000' as timestamp) as utc_dat +-------------------------------+-------------------------------+ </codeblock> <p> - The following example shows that for a date and time when daylight savings - is in effect (<codeph>PDT</codeph>), the UTC time - is 7 hours ahead of the local California time; while when daylight savings - is not in effect (<codeph>PST</codeph>), the UTC time is 8 hours ahead of - the local California time. + The following example shows that for a date and time when daylight savings is in + effect (<codeph>PDT</codeph>), the UTC time is 7 hours ahead of the local California + time; while when daylight savings is not in effect (<codeph>PST</codeph>), the UTC + time is 8 hours ahead of the local California time. </p> <codeblock> select now() as local_datetime, @@ -1083,15 +1392,15 @@ select '2016-01-05' as local_datetime, <dlentry id="hour"> <dt> - <codeph>hour(timestamp date)</codeph> + HOUR(TIMESTAMP ts) </dt> <dd> - <indexterm audience="hidden">hour() function</indexterm> <b>Purpose:</b> Returns the hour field from a <codeph>TIMESTAMP</codeph> field. <p> - <b>Return type:</b> <codeph>int</codeph> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, hour(now()) as current_hour; @@ -1116,16 +1425,15 @@ select now() + interval 12 hours as 12_hours_from_now, <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> + HOURS_ADD(TIMESTAMP date, INT hours), HOURS_ADD(TIMESTAMP date, BIGINT hours) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -1143,16 +1451,15 @@ select now() as right_now, <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> + HOURS_SUB(TIMESTAMP date, INT hours), HOURS_SUB(TIMESTAMP date, BIGINT hours) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -1170,39 +1477,47 @@ select now() as right_now, <dlentry rev="2.3.0" id="int_months_between"> <dt> - <codeph>int_months_between(timestamp newer, timestamp older)</codeph> + INT_MONTHS_BETWEEN(TIMESTAMP newer, TIMESTAMP older) </dt> <dd> - <indexterm audience="hidden">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. + <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> + <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. + 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. + 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>. + 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. + 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 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'); +------------------------------------------------+ @@ -1250,19 +1565,20 @@ select int_months_between('2015-03-31', '2015-01-30'); <dlentry id="last_day" rev="2.9.0 IMPALA-5316"> <dt> - <codeph>last_day(timestamp t)</codeph> + LAST_DAY(TIMESTAMP ts) </dt> <dd> - <indexterm audience="hidden">last_day() function</indexterm> - <b>Purpose:</b> Returns a <codeph>TIMESTAMP</codeph> corresponding to - the beginning of the last calendar day in the same month as the - <codeph>TIMESTAMP</codeph> argument. + <b>Purpose:</b> Returns a <codeph>TIMESTAMP</codeph> corresponding to the beginning of + the last calendar day in the same month as the <codeph>TIMESTAMP</codeph> argument. <p> - <b>Return type:</b> <codeph>timestamp</codeph> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/added_in_290"/> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> If the input argument does not represent a valid Impala <codeph>TIMESTAMP</codeph> including both date and time portions, the function returns <codeph>NULL</codeph>. @@ -1271,10 +1587,13 @@ select int_months_between('2015-03-31', '2015-01-30'); allowed range for Impala <codeph>TIMESTAMP</codeph> values, the function returns <codeph>NULL</codeph>. </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> The following example shows how to examine the current date, and dates around the - end of the month, as <codeph>TIMESTAMP</codeph> values with any time portion removed: + end of the month, as <codeph>TIMESTAMP</codeph> values with any time portion + removed: </p> <codeblock> select @@ -1289,8 +1608,8 @@ select +-------------------------------+---------------------+---------------------+---------------------+ </codeblock> <p> - The following example shows how to examine the current date and dates around the - end of the month as integers representing the day of the month: + The following example shows how to examine the current date and dates around the end + of the month as integers representing the day of the month: </p> <codeblock> select @@ -1312,16 +1631,16 @@ select <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> + MICROSECONDS_ADD(TIMESTAMP date, INT microseconds), MICROSECONDS_ADD(TIMESTAMP date, + BIGINT microseconds) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -1339,16 +1658,16 @@ select now() as right_now, <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> + MICROSECONDS_SUB(TIMESTAMP date, INT microseconds), MICROSECONDS_SUB(TIMESTAMP date, + BIGINT microseconds) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -1366,21 +1685,24 @@ select now() as right_now, <dlentry rev="IMPALA-1772 2.6.0" id="millisecond"> <dt> - <codeph>millisecond(timestamp)</codeph> + MILLISECOND(TIMESTAMP ts) </dt> <dd> - <indexterm audience="hidden">millisecond() function</indexterm> <b>Purpose:</b> Returns the millisecond portion of a <codeph>TIMESTAMP</codeph> value. <p> - <b>Return type:</b> <codeph>int</codeph> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/added_in_250"/> + <p conref="../shared/impala_common.xml#common/usage_notes_blurb"/> + <p> The millisecond value is truncated, not rounded, if the <codeph>TIMESTAMP</codeph> value contains more than 3 significant digits to the right of the decimal point. </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> 252.4 milliseconds truncated to 252. @@ -1408,16 +1730,16 @@ select now(), millisecond(now()); <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> + MILLISECONDS_ADD(TIMESTAMP date, INT milliseconds), MILLISECONDS_ADD(TIMESTAMP date, + BIGINT milliseconds) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -1435,16 +1757,16 @@ select now() as right_now, <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> + MILLISECONDS_SUB(TIMESTAMP date, INT milliseconds), MILLISECONDS_SUB(TIMESTAMP date, + BIGINT milliseconds) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, @@ -1462,15 +1784,15 @@ select now() as right_now, <dlentry id="minute"> <dt> - <codeph>minute(timestamp date)</codeph> + MINUTE(TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">minute() function</indexterm> <b>Purpose:</b> Returns the minute field from a <codeph>TIMESTAMP</codeph> value. <p> - <b>Return type:</b> <codeph>int</codeph> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, minute(now()) as current_minute; @@ -1487,16 +1809,15 @@ select now() as right_now, minute(now()) as current_minute; <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> + MINUTES_ADD(TIMESTAMP date, INT minutes), MINUTES_ADD(TIMESTAMP date, BIGINT minutes) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, minutes_add(now(), 90) as 90_minutes_from_now; @@ -1513,16 +1834,15 @@ select now() as right_now, minutes_add(now(), 90) as 90_minutes_from_now; <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> + MINUTES_SUB(TIMESTAMP date, INT minutes), MINUTES_SUB(TIMESTAMP date, BIGINT minutes) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, minutes_sub(now(), 90) as 90_minutes_ago; @@ -1539,15 +1859,16 @@ select now() as right_now, minutes_sub(now(), 90) as 90_minutes_ago; <dlentry id="month"> <dt> - <codeph>month(timestamp date)</codeph> + MONTH(TIMESTAMP date) </dt> <dd> - <indexterm audience="hidden">month() function</indexterm> - <b>Purpose:</b> Returns the month field, represented as an integer, from the date portion of a <codeph>TIMESTAMP</codeph>. + <b>Purpose:</b> Returns the month field, represented as an integer, from the date + portion of a <codeph>TIMESTAMP</codeph>. <p> - <b>Return type:</b> <codeph>int</codeph> + <b>Return type:</b> <codeph>INT</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> <codeblock> select now() as right_now, month(now()) as current_month; @@ -1560,40 +1881,42 @@ select now() as right_now, month(now()) as current_month; </dd> </dlentry> + <dlentry id="monthname"> + <dt> - <codeph>monthname(timestamp date)</codeph> + MONTHNAME(TIMESTAMP date) </dt> + <dd> - <b>Purpose:</b> Returns the month field from a - <codeph>TIMESTAMP</codeph> value, converted to the string - corresponding to that month name. + <b>Purpose:</b> Returns the month field from a <codeph>TIMESTAMP</codeph> value, + converted to the string corresponding to that month name. <p> - <b>Return type:</b> <codeph>string</codeph> + <b>Return type:</b> <codeph>STRING</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> + MONTHS_ADD(TIMESTAMP date, INT months), MONTHS_ADD(TIMESTAMP date, BIGINT months) </dt> <dd> - <indexterm audience="hidden">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> + <b>Return type:</b> <codeph>TIMESTAMP</codeph> </p> + <p conref="../shared/impala_common.xml#common/example_blurb"/> + <p> - The following example shows the effects of adding some number of - months to a <codeph>TIMESTAMP</codeph> value, using both the - <codeph>months_add()</codeph> function and its <codeph>add_months()</codeph> - alias. These examples use <codeph>trunc()</codeph> to strip off the time portion - and leave just the date. + The following example shows the effects of adding some number of months to a + <codeph>TIMESTAMP</codeph> value, using both the <codeph>MONTHS_ADD()</codeph> + function and its <codeph>ADD_MONTHS()</codeph> alias. These examples use + <codeph>TRUNC()</codeph> to strip off the time portion and leave just the date. </p> <codeblock> with t1 as (select trunc(now(), 'dd') as today) @@ -1613,12 +1936,12 @@ with t1 as (select trunc(now(), 'dd') as today) +---------------------+---------------------+ </codeblock> <p> - The following examples show how if <codeph>months_add()</codeph> - would return a nonexistent date, due to different months having - different numbers of days, the function returns a <codeph>TIMESTAMP</codeph> - from the last day of the relevant month. For example, adding one month - to January 31 produces a date of February 29th in the year 2016 (a leap year), - and February 28th in the year 2015 (a non-leap year). + The following examples show how if <codeph>MONTHS_ADD()</codeph> would return a + nonexistent date, due to different months having different numbers of days, the + function returns a <codeph>TIMESTAMP</codeph> from the last day of the relevant + month. For example, adding one month to January 31 produces a date of February 29th + in the year 2016 (a leap year), and February 28th in the year 2015 (a non-leap + year). </p> <codeblock> with t1 as (select cast('2016-01-31' as timestamp) as jan_31) @@ -1644,45 +1967,55 @@ with t1 as (select cast('2015-01-31' as timestamp) as jan_31) <dlentry rev="2.3.0" id="months_between"> <dt> - <codeph>months_between(timestamp newer, timestamp older)</codeph> + MONTHS_BETWEEN(TIMESTAMP newer, TIMESTAMP older) </dt> <dd> - <indexterm audience="hidden">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). + <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> + <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. + 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. + 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. + 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>. + 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. + 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. + 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'); +--------------------------------------------+ @@ -1706,12 +2039,12 @@ select months_between(now() + interval 1 year, now()); +------------------------------------------------+ </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. + 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
<TRUNCATED>
