Google Git
Sign in
apache / impala / 0b571b5cf4987b1cbd209cf9e4606caced4886bc / . / docs / build / plain-html / topics / impala_datetime_functions.html
blob: afc8750c3efd2a7563d04d1819ba4422e46565b3 [file] [log] [blame]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="copyright" content="(C) Copyright 2025" />
<meta name="DC.rights.owner" content="(C) Copyright 2025" />
<meta name="DC.Type" content="concept" />
<meta name="DC.Title" content="Impala Date and Time Functions" />
<meta name="DC.Relation" scheme="URI" content="../topics/impala_functions.html" />
<meta name="prodname" content="Impala" />
<meta name="prodname" content="Impala" />
<meta name="version" content="Impala 3.4.x" />
<meta name="version" content="Impala 3.4.x" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="datetime_functions" />
<link rel="stylesheet" type="text/css" href="../commonltr.css" />
<title>Impala Date and Time Functions</title>
</head>
<body id="datetime_functions">
<h1 class="title topictitle1" id="ariaid-title1">Impala Date and Time Functions</h1>
<div class="body conbody">
<p class="p">
The underlying Impala data types for date and time data are <code class="ph codeph">TIMESTAMP</code> and
<code class="ph codeph">DATE</code>.
</p>
<div class="p">
Some of the date/time functions are affected by the setting of the
<code class="ph codeph">‑‑use_local_tz_for_unix_timestamp_conversions</code> startup flag
for the <span class="keyword cmdname">impalad</span> daemon:
<ul class="ul">
<li class="li">
The setting is off by default, meaning that functions such as
<code class="ph codeph">FROM_UNIXTIME()</code> and <code class="ph codeph">UNIX_TIMESTAMP()</code> consider the
input values to always represent the UTC time zone.
</li>
<li class="li">
The setting also applies when you <code class="ph codeph">CAST()</code> a <code class="ph codeph">BIGINT</code>
value to <code class="ph codeph">TIMESTAMP</code>, or a <code class="ph codeph">TIMESTAMP</code> value to
<code class="ph codeph">BIGINT</code>. When this setting is enabled, these functions and operations
convert to and from values representing the local time zone. See
<a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details about how Impala
handles time zone considerations for the <code class="ph codeph">TIMESTAMP</code> data type.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Function reference:</strong>
</p>
<p class="p">
Impala supports the following date and time functions:
</p>
<ul class="ul">
<li class="li">
<a class="xref" href="#datetime_functions__add_months">ADD_MONTHS</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__adddate">ADDDATE</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__current_date">CURRENT_DATE</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__current_timestamp">CURRENT_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__date_add">DATE_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__date_part">DATE_PART</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__date_sub">DATE_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__date_trunc">DATE_TRUNC</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__datediff">DATEDIFF</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__day">DAY</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__dayname">DAYNAME</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__dayofweek">DAYOFWEEK</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__dayofyear">DAYOFYEAR</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__days_add">DAYS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__days_sub">DAYS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__extract">EXTRACT</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__from_timestamp">FROM_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__from_unixtime">FROM_UNIXTIME</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__from_utc_timestamp">FROM_UTC_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__hour">HOUR</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__hours_add">HOURS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__hours_sub">HOURS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__int_months_between">INT_MONTHS_BETWEEN</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__microseconds_add">MICROSECONDS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__microseconds_sub">MICROSECONDS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__millisecond">MILLISECOND</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__milliseconds_add">MILLISECONDS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__milliseconds_sub">MILLISECONDS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__minute">MINUTE</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__minutes_add">MINUTES_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__minutes_sub">MINUTES_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__month">MONTH</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__month">MONTHNAME</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__monthname">MONTHS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__months_between">MONTHS_BETWEEN</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__months_sub">MONTHS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__nanoseconds_add">NANOSECONDS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__nanoseconds_sub">NANOSECONDS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__next_day">NEXT_DAY</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__now">NOW</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__quarter">QUARTER</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__second">SECOND</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__seconds_add">SECONDS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__seconds_sub">SECONDS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__subdate">SUBDATE</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__timeofday">TIMEOFDAY</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__timestamp_cmp">TIMESTAMP_CMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__to_date">TO_DATE</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__to_timestamp">TO_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__to_utc_timestamp">TO_UTC_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__trunc">TRUNC</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__unix_timestamp">UNIX_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__utc_timestamp">UTC_TIMESTAMP</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__weekofyear">WEEKOFYEAR</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__weeks_add">WEEKS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__weeks_sub">WEEKS_SUB</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__year">YEAR</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__years_add">YEARS_ADD</a>
</li>
<li class="li">
<a class="xref" href="#datetime_functions__years_sub">YEARS_SUB</a>
</li>
</ul>
<dl class="dl">
<dt class="dt dlterm" id="datetime_functions__add_months">
ADD_MONTHS(TIMESTAMP / DATE date, INT months), ADD_MONTHS(TIMESTAMP / DATE date,
BIGINT months)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Adds <var class="keyword varname">months</var> to <var class="keyword varname">date</var> and returns
the new date value.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Same as
<code class="ph codeph"><a class="xref" href="#datetime_functions__months_add">MONTHS_ADD()</a></code>.
Available in Impala 1.4 and higher. For compatibility when porting code with vendor
extensions.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__adddate">
ADDDATE(TIMESTAMP / DATE date, INT / BIGINT days)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Adds <var class="keyword varname">days</var> to <var class="keyword varname">date</var> and returns
the new date value.
<p class="p">
The <var class="keyword varname">days</var> value can be negative, which gives the same result as
the <code class="ph codeph">SUBDATE()</code> function.
</p>
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
</dd>
<dt class="dt dlterm" id="datetime_functions__current_date">
CURRENT_DATE()
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the current date.
<p class="p">
Any references to the <code class="ph codeph">CURRENT_DATE()</code> function are evaluated at the
start of a query. All calls to <code class="ph codeph">CURRENT_DATE()</code> within the same query
return the same value, and the value does not depend on how long the query takes.
</p>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">DATE</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__current_timestamp">
CURRENT_TIMESTAMP()
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Alias for the <code class="ph codeph">NOW()</code> function.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__date_add">
DATE_ADD(TIMESTAMP / DATE date, INT / BIGINT days), DATE_ADD(TIMESTAMP / DATE date,
interval_expression)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Adds a specified number of days to the <var class="keyword varname">date</var>
argument.
</dd>
<dd class="dd ddexpand">
With an <code class="ph codeph">INTERVAL</code> expression as the second argument, you can calculate
a delta value using other units such as weeks, years, hours, seconds, and so on; see
<a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
The following examples show the shorthand notation of an <code class="ph codeph">INTERVAL</code>
expression, instead of specifying the precise number of days. The
<code class="ph codeph">INTERVAL</code> notation also lets you work with units smaller than a
single day.
</p>
<pre class="pre codeblock"><code>
select now() as right_now, date_add(now(), interval 3 weeks) as in_3_weeks;
+-------------------------------+-------------------------------+
| right_now | in_3_weeks |
+-------------------------------+-------------------------------+
| 2016-05-20 11:05:39.173331000 | 2016-06-10 11:05:39.173331000 |
+-------------------------------+-------------------------------+
select now() as right_now, date_add(now(), interval 6 hours) as in_6_hours;
+-------------------------------+-------------------------------+
| right_now | in_6_hours |
+-------------------------------+-------------------------------+
| 2016-05-20 11:13:51.492536000 | 2016-05-20 17:13:51.492536000 |
+-------------------------------+-------------------------------+
</code></pre>
<p class="p">
Like all date/time functions that deal with months, <code class="ph codeph">date_add()</code>
handles nonexistent dates past the end of a month by setting the date to the last
day of the month. The following example shows how the nonexistent date April 31st is
normalized to April 30th:
</p>
<pre class="pre codeblock"><code>
select date_add(cast('2016-01-31' as timestamp), interval 3 months) as 'april_31st';
+---------------------+
| april_31st |
+---------------------+
| 2016-04-30 00:00:00 |
+---------------------+
</code></pre>
</dd>
<dt class="dt dlterm">
DATE_CMP(DATE date1, DATE date2)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Compares <var class="keyword varname">date1</var> and <var class="keyword varname">date2</var> and
returns:
<ul class="ul">
<li class="li">
<code class="ph codeph">0</code> if the dates are identical.
</li>
<li class="li">
1 if <var class="keyword varname">date1</var> &gt; <var class="keyword varname">date2</var>.
</li>
<li class="li">
-1 if <var class="keyword varname">date1</var> &lt; <var class="keyword varname">date2</var>.
</li>
<li class="li">
<code class="ph codeph">NULL</code> if <var class="keyword varname">date1</var> or <var class="keyword varname">date2</var> is
<code class="ph codeph">NULL</code>.
</li>
</ul>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__date_part">
DATE_PART(STRING part, TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Similar to
<a class="xref" href="impala_datetime_functions.html#datetime_functions__extract"><code class="ph codeph">EXTRACT()</code></a>,
with the argument order reversed. Supports the same date and time units as
<code class="ph codeph">EXTRACT()</code>. For compatibility with SQL code containing vendor
extensions.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">BIGINT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__date_sub">
DATE_SUB(TIMESTAMP startdate, INT days), DATE_SUB(TIMESTAMP startdate,
interval_expression)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Subtracts a specified number of days from a <code class="ph codeph">TIMESTAMP</code>
value. With an <code class="ph codeph">INTERVAL</code> expression as the second argument, you can
calculate a delta value using other units such as weeks, years, hours, seconds, and so
on; see <a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
The following example shows the simplest usage, of subtracting a specified number of
days from a <code class="ph codeph">TIMESTAMP</code> value:
</p>
<pre class="pre codeblock"><code>
select now() as right_now, date_sub(now(), 7) as last_week;
+-------------------------------+-------------------------------+
| right_now | last_week |
+-------------------------------+-------------------------------+
| 2016-05-20 11:21:30.491011000 | 2016-05-13 11:21:30.491011000 |
+-------------------------------+-------------------------------+
</code></pre>
<p class="p">
The following examples show the shorthand notation of an <code class="ph codeph">INTERVAL</code>
expression, instead of specifying the precise number of days. The
<code class="ph codeph">INTERVAL</code> notation also lets you work with units smaller than a
single day.
</p>
<pre class="pre codeblock"><code>
select now() as right_now, date_sub(now(), interval 3 weeks) as 3_weeks_ago;
+-------------------------------+-------------------------------+
| right_now | 3_weeks_ago |
+-------------------------------+-------------------------------+
| 2016-05-20 11:23:05.176953000 | 2016-04-29 11:23:05.176953000 |
+-------------------------------+-------------------------------+
select now() as right_now, date_sub(now(), interval 6 hours) as 6_hours_ago;
+-------------------------------+-------------------------------+
| right_now | 6_hours_ago |
+-------------------------------+-------------------------------+
| 2016-05-20 11:23:35.439631000 | 2016-05-20 05:23:35.439631000 |
+-------------------------------+-------------------------------+
</code></pre>
<p class="p">
Like all date/time functions that deal with months, <code class="ph codeph">date_add()</code>
handles nonexistent dates past the end of a month by setting the date to the last
day of the month. The following example shows how the nonexistent date April 31st is
normalized to April 30th:
</p>
<pre class="pre codeblock"><code>
select date_sub(cast('2016-05-31' as timestamp), interval 1 months) as 'april_31st';
+---------------------+
| april_31st |
+---------------------+
| 2016-04-30 00:00:00 |
+---------------------+
</code></pre>
</dd>
<dt class="dt dlterm" id="datetime_functions__date_trunc">
DATE_TRUNC(STRING unit, TIMESTAMP / DATE ts)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the <var class="keyword varname">ts</var> value truncated to the specified
<var class="keyword varname">unit</var>.
<p class="p">
<strong class="ph b">Argument:</strong> The <code class="ph codeph">unit</code> argument is not case-sensitive. This
argument string can be one of:
</p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="datetime_functions__table_ucy_trp_p3b" class="table" frame="border" border="1" rules="all"><colgroup><col style="width:25.380710659898476%" /><col style="width:32.48730964467005%" /><col style="width:42.13197969543147%" /></colgroup><thead class="thead" style="text-align:left;">
<tr class="row">
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e1045">
Unit
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e1048">
Supported for TIMESTAMP
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e1051">
Supported for DATE
</th>
</tr>
</thead>
<tbody class="tbody">
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'MICROSECONDS'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'MILLISECONDS'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'SECOND'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'MINUTE'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'HOUR'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'DAY'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'WEEK'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'MONTH'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'YEAR'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'DECADE'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'CENTURY'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1045 ">
<code class="ph codeph">'MILLENNIUM'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1048 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1051 ">
Yes
</td>
</tr>
</tbody>
</table>
</div>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.11.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Although this function is similar to calling <code class="ph codeph">TRUNC()</code> with a
<code class="ph codeph">TIMESTAMP</code> or <code class="ph codeph">DATE</code> argument, the order of arguments
and the recognized units are different between <code class="ph codeph">TRUNC()</code> and
<code class="ph codeph">DATE_TRUNC()</code>. Therefore, these functions are not interchangeable.
</p>
<p class="p">
This function is typically used in <code class="ph codeph">GROUP BY</code> queries to aggregate
results from the same hour, day, week, month, quarter, and so on. You can also use
this function in an <code class="ph codeph">INSERT ... SELECT</code> into a partitioned table to
divide <code class="ph codeph">TIMESTAMP</code> or <code class="ph codeph">DATE</code> values into the correct
partition.
</p>
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
<code class="ph codeph">TIMESTAMP</code> if the second argument, <var class="keyword varname">ts</var>, is
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
<code class="ph codeph">DATE</code> if the second argument, <var class="keyword varname">ts</var>, is
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
<code class="ph codeph">DATE_TRUNC('HOUR', NOW())</code> returns <code class="ph codeph">2017-12-05
13:00:00</code>.
</p>
<p class="p">
<code class="ph codeph">DATE_TRUNC('MILLENNIUM', DATE'2019-08-02')</code> returns
<code class="ph codeph">2001-01-01</code>.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__datediff">
DATEDIFF(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the number of days from <var class="keyword varname">startdate</var> to
<var class="keyword varname">enddate</var>.
<p class="p">
If <var class="keyword varname">enddate</var> &gt; <var class="keyword varname">startdate</var>, the return value is
positive.
</p>
<p class="p">
If <var class="keyword varname">enddate</var> &lt; <var class="keyword varname">startdate</var>, the return value is
negative.
</p>
<p class="p">
If <var class="keyword varname">enddate</var> = <var class="keyword varname">startdate</var>, the return value is
zero.
</p>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
The time portions of the <var class="keyword varname">enddate</var> and <var class="keyword varname">startdate</var>
values are ignored. For example, 11:59 PM on one day and 12:01 AM on the next day
represent a <code class="ph codeph">DATEDIFF()</code> of -1 because the date/time values represent
different days, even though the <code class="ph codeph">TIMESTAMP</code> values differ by only 2
minutes.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__day">
DAY(TIMESTAMP / DATE date), DAYOFMONTH(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the day value from the <var class="keyword varname">date</var> argument. The
value represents the day of the month, therefore is in the range 1-31, or less for
months without 31 days.
<p class="p">
Returns <code class="ph codeph">NULL</code> for nonexistent dates, e.g. <code class="ph codeph">Feb 30</code>,
or misformatted date strings, e.g. <code class="ph codeph">'1999-02-013'</code>.
</p>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__dayname">
DAYNAME(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the day name of the <var class="keyword varname">date</var> argument. The
range of return values is <code class="ph codeph">'Sunday'</code> to <code class="ph codeph">'Saturday'</code>.
Used in report-generating queries, as an alternative to calling
<code class="ph codeph">DAYOFWEEK()</code> and turning that numeric return value into a string using
a <code class="ph codeph">CASE</code> expression.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">STRING</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__dayofweek">
DAYOFWEEK(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the day field of the <var class="keyword varname">date</var> arguement,
corresponding to the day of the week. The range of return values is 1 (Sunday) to 7
(Saturday).
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__dayofyear">
DAYOFYEAR(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the day field from the <var class="keyword varname">date</var> argument,
corresponding to the day of the year. The range of return values is 1 (January 1) to
366 (December 31 of a leap year).
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__days_add">
DAYS_ADD(TIMESTAMP / DATE date, INT / BIGINT days)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">days</var> added to
<var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
</dd>
<dt class="dt dlterm" id="datetime_functions__days_sub">
DAYS_SUB(TIMESTAMP / DATE date, INT / BIGINT days)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">days</var>
subtracted from <var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
</dd>
<dt class="dt dlterm" id="datetime_functions__extract">
EXTRACT(TIMESTAMP / DATE ts, STRING unit), EXTRACT(unit FROM TIMESTAMP / DATE ts)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns one of the numeric date or time fields, specified by
<var class="keyword varname">unit</var>, from <var class="keyword varname">ts</var>.
<p class="p">
<strong class="ph b">Argument:</strong> The <code class="ph codeph">unit</code> argument value is not case-sensitive. The
<code class="ph codeph">unit</code> string can be one of:
</p>
<div class="p">
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="datetime_functions__table_itt_ktp_p3b" class="table" frame="border" border="1" rules="all"><colgroup><col style="width:25.380710659898476%" /><col style="width:32.48730964467005%" /><col style="width:42.13197969543147%" /></colgroup><thead class="thead" style="text-align:left;">
<tr class="row">
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e1696">
Unit
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e1699">
Supported for TIMESTAMP ts
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e1702">
Supported for DATE ts
</th>
</tr>
</thead>
<tbody class="tbody">
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'EPOCH'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'MILLISECOND'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'SECOND'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'MINUTE'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'HOUR'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
No
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'DAY'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'MONTH'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'QUARTER'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
Yes
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1696 ">
<code class="ph codeph">'YEAR'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1699 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e1702 ">
Yes
</td>
</tr>
</tbody>
</table>
</div>
</div>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Typically used in <code class="ph codeph">GROUP BY</code> queries to arrange results by hour, day,
month, and so on. You can also use this function in an <code class="ph codeph">INSERT ...
SELECT</code> statement to insert into a partitioned table to split up
<code class="ph codeph">TIMESTAMP</code> values into individual parts, if the partitioned table
has separate partition key columns representing year, month, day, and so on. If you
need to divide by more complex units of time, such as by week or by quarter, use the
<code class="ph codeph">TRUNC()</code> function instead.
</p>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">BIGINT</code>
</p>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<code class="ph codeph">EXTRACT(DAY FROM DATE'2019-08-17')</code> returns <code class="ph codeph">17</code>.
<p class="p">
If you specify <code class="ph codeph">'MILLISECOND'</code> for the <var class="keyword varname">unit</var>
argument, the function returns the seconds component and the milliseconds component.
</p>
<p class="p">
<code class="ph codeph">EXTRACT(CAST('2006-05-12 18:27:28.123456789' AS TIMESTAMP),
'MILLISECOND')</code> returns <code class="ph codeph">28123</code>.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__from_timestamp">
FROM_TIMESTAMP(TIMESTAMP datetime, STRING pattern), FROM_TIMESTAMP(STRING datetime,
STRING pattern)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Converts a <code class="ph codeph">TIMESTAMP</code> value into a string representing
the same value.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">STRING</code>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
The <code class="ph codeph">FROM_TIMESTAMP()</code> function provides a flexible way to convert
<code class="ph codeph">TIMESTAMP</code> values into arbitrary string formats for reporting
purposes.
</p>
<p class="p">
Because Impala implicitly converts string values into <code class="ph codeph">TIMESTAMP</code>,
you can pass date/time values represented as strings (in the standard
<code class="ph codeph">yyyy-MM-dd HH:mm:ss.SSS</code> 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 class="p">
The allowed tokens for the pattern string are the same as for the
<code class="ph codeph">FROM_UNIXTIME()</code> function.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__from_unixtime">
FROM_UNIXTIME(BIGINT unixtime [, STRING pattern])
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Converts the number of seconds from the Unix epoch to the specified time
into a string in the local time zone. <p class="p">
<strong class="ph b">Return type:</strong>
<code class="ph codeph">STRING</code>
</p>
<p class="p"> The <var class="keyword varname">pattern</var> string supports the following subset of
Java SimpleDateFormat. </p>
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="datetime_functions__table_dzg_zpm_1jb" class="table" frame="border" border="1" rules="all"><colgroup><col /><col /></colgroup><thead class="thead" style="text-align:left;">
<tr class="row">
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e2004"> Pattern </th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e2007"> Description </th>
</tr>
</thead>
<tbody class="tbody">
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">y</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Year </td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">M</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Month </td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">d</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Day </td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">H</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Hour </td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">m</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Minute </td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">s</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Second </td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2004 ">
<code class="ph codeph">S</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e2007 "> Fractional second </td>
</tr>
</tbody>
</table>
</div>
<p class="p"> The following rules apply to the <var class="keyword varname">pattern</var> string: </p>
<ul class="ul">
<li class="li"> The <var class="keyword varname">pattern</var> string is case-sensitive. </li>
<li class="li"> All fields are variable length, and thus must use separators to specify the
boundaries of the fields, with the exception of the time zone values.</li>
<li class="li">Time zone offsets will be disregarded. The FROM_UNIXTIME() function accepts a bigint
number as input (seconds from Unix epoch) but will not hold the timezone related
information and will be excluded in the output.</li>
<li class="li"> Formatting character groups can appear in any order along with any separators. For
example: <ul class="ul">
<li class="li">
<code class="ph codeph">yyyy/MM/dd</code>
</li>
<li class="li">
<code class="ph codeph">dd-MMM-yy</code>
</li>
<li class="li">
<code class="ph codeph">(dd)(MM)(yyyy) HH:mm:ss</code>
</li>
<li class="li">
<code class="ph codeph">yyyy-MM-dd HH:mm:ss</code>
</li>
</ul>
</li>
</ul>
<p class="p"> In Impala 1.3 and later, you can switch the order of elements, use
alternative separator characters, and use a different number of placeholders for each
unit. Adding more instances of <code class="ph codeph">y</code>, <code class="ph codeph">d</code>,
<code class="ph codeph">H</code>, and so on produces output strings zero-padded to the requested
number of characters. The exception is <code class="ph codeph">M</code> for months, where
<code class="ph codeph">M</code> produces a non-padded value such as <code class="ph codeph">3</code>,
<code class="ph codeph">MM</code> produces a zero-padded value such as <code class="ph codeph">03</code>,
<code class="ph codeph">MMM</code> produces an abbreviated month name such as <code class="ph codeph">Mar</code>,
and sequences of 4 or more <code class="ph codeph">M</code> are not allowed. </p>
<p class="p"> A date string including all fields could be <code class="ph codeph">'yyyy-MM-dd
HH:mm:ss.SSSSSS'</code>, <code class="ph codeph">'dd/MM/yyyy HH:mm:ss.SSSSSS'</code>, <code class="ph codeph">'MMM
dd, yyyy HH.mm.ss (SSSSSS)'</code> or other combinations of placeholders and
separator characters. </p>
<p class="p">
In Impala 2.2.0 and higher, built-in functions that accept or return integers
representing <code class="ph codeph">TIMESTAMP</code> values use the <code class="ph codeph">BIGINT</code> type for
parameters and return values, rather than <code class="ph codeph">INT</code>. This change lets the
date and time functions avoid an overflow error that would otherwise occur on January
19th, 2038 (known as the
<a class="xref" href="http://en.wikipedia.org/wiki/Year_2038_problem" target="_blank"><span class="q">"Year
2038 problem"</span> or <span class="q">"Y2K38 problem"</span></a>). This change affects the
<code class="ph codeph">FROM_UNIXTIME()</code> and <code class="ph codeph">UNIX_TIMESTAMP()</code> functions. You
might need to change application code that interacts with these functions, change the
types of columns that store the return values, or add <code class="ph codeph">CAST()</code> calls to
SQL statements that call these functions.
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
The way this function deals with time zones when converting to or from
<code class="ph codeph">TIMESTAMP</code> values is affected by the
<code class="ph codeph">‑‑use_local_tz_for_unix_timestamp_conversions</code> startup flag
for the <span class="keyword cmdname">impalad</span> daemon. See
<a class="xref" href="../shared/../topics/impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details about how
Impala handles time zone considerations for the <code class="ph codeph">TIMESTAMP</code> data type.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__from_utc_timestamp">
FROM_UTC_TIMESTAMP(TIMESTAMP ts, STRING timezone)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Converts a specified UTC timestamp value into the appropriate value
for a specified time zone.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong> Often used to translate UTC time zone data stored in a table
back to the local date and time for reporting. The opposite of the
<code class="ph codeph">TO_UTC_TIMESTAMP()</code> function.
</p>
<p class="p">
To determine the time zone of the server you are connected to, in
<span class="keyword">Impala 2.3</span> and higher you can call the
<code class="ph codeph">timeofday()</code> function, which includes the time zone specifier in its
return value. Remember that with cloud computing, the server you interact with might be
in a different time zone than you are, or different sessions might connect to servers in
different time zones, or a cluster might include servers in more than one time zone.
</p>
<p class="p">
See discussion of time zones in
<a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for information about
using this function for conversions between the local time zone and UTC.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__hour">
HOUR(TIMESTAMP ts)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the hour field from a <code class="ph codeph">TIMESTAMP</code> field.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__hours_add">
HOURS_ADD(TIMESTAMP date, INT hours), HOURS_ADD(TIMESTAMP date, BIGINT hours)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of hours.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__hours_sub">
HOURS_SUB(TIMESTAMP date, INT hours), HOURS_SUB(TIMESTAMP date, BIGINT hours)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of hours.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__int_months_between">
INT_MONTHS_BETWEEN(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the number of months from <var class="keyword varname">startdate</var> to
<var class="keyword varname">enddate</var>, representing only the full months that passed.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Typically used in business contexts, for example to determine whether a specified
number of months have passed or whether some end-of-month deadline was reached.
</p>
<p class="p">
The method of determining the number of elapsed months includes some special
handling of months with different numbers of days that creates edge cases for dates
between the 28th and 31st days of certain months. See
<code class="ph codeph">MONTHS_BETWEEN()</code> for details. The
<code class="ph codeph">INT_MONTHS_BETWEEN()</code> result is essentially the
<code class="ph codeph">FLOOR()</code> of the <code class="ph codeph">MONTHS_BETWEEN()</code> result.
</p>
<p class="p">
If either value is <code class="ph codeph">NULL</code>, which could happen for example when
converting a nonexistent date string such as <code class="ph codeph">'2015-02-29'</code> to a
<code class="ph codeph">TIMESTAMP</code>, the result is also <code class="ph codeph">NULL</code>.
</p>
<p class="p">
If the first argument represents an earlier time than the second argument, the
result is negative.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__last_day">
LAST_DAY(TIMESTAMP / DATE ts)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the beginning of the last calendar day in the same month of
<var class="keyword varname">ts</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
Returns <code class="ph codeph">TIMESTAMP</code> if <var class="keyword varname">ts</var> is of the
<code class="ph codeph">TIMESTAMP</code> type.
</li>
<li class="li">
Returns <code class="ph codeph">DATE</code> if <var class="keyword varname">ts</var> is of the
<code class="ph codeph">DATE</code> type.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.9.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
If the input argument does not represent a valid Impala <code class="ph codeph">TIMESTAMP</code>
including both date and time portions, the function returns <code class="ph codeph">NULL</code>.
For example, if the input argument is a string that cannot be implicitly cast to
<code class="ph codeph">TIMESTAMP</code>, does not include a date portion, or is out of the
allowed range for Impala <code class="ph codeph">TIMESTAMP</code> values, the function returns
<code class="ph codeph">NULL</code>.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__microseconds_add">
MICROSECONDS_ADD(TIMESTAMP date, INT microseconds), MICROSECONDS_ADD(TIMESTAMP date,
BIGINT microseconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of microseconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__microseconds_sub">
MICROSECONDS_SUB(TIMESTAMP date, INT microseconds), MICROSECONDS_SUB(TIMESTAMP date,
BIGINT microseconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of microseconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__millisecond">
MILLISECOND(TIMESTAMP ts)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the millisecond portion of a <code class="ph codeph">TIMESTAMP</code> value.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.5.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
The millisecond value is truncated, not rounded, if the <code class="ph codeph">TIMESTAMP</code>
value contains more than 3 significant digits to the right of the decimal point.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__milliseconds_add">
MILLISECONDS_ADD(TIMESTAMP date, INT milliseconds), MILLISECONDS_ADD(TIMESTAMP date,
BIGINT milliseconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of milliseconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__milliseconds_sub">
MILLISECONDS_SUB(TIMESTAMP date, INT milliseconds), MILLISECONDS_SUB(TIMESTAMP date,
BIGINT milliseconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of milliseconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__minute">
MINUTE(TIMESTAMP date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the minute field from a <code class="ph codeph">TIMESTAMP</code> value.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__minutes_add">
MINUTES_ADD(TIMESTAMP date, INT minutes), MINUTES_ADD(TIMESTAMP date, BIGINT minutes)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of minutes.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__minutes_sub">
MINUTES_SUB(TIMESTAMP date, INT minutes), MINUTES_SUB(TIMESTAMP date, BIGINT minutes)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of minutes.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__month">
MONTH(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the month field, represented as an integer, from the
<var class="keyword varname">date</var> argument.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__monthname">
MONTHNAME(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the month name of the <var class="keyword varname">date</var> argument.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">STRING</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__months_add">
MONTHS_ADD(TIMESTAMP / DATE date, INT / BIGINT months)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">months</var> added
to <var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
If <var class="keyword varname">date</var> is the last day of a month, the return date will fall on
the last day of the target month, e.g. <code class="ph codeph">MONTHS_ADD(DATE'2019-01-31',
1)</code> returns <code class="ph codeph">DATE'2019-02-28'</code>.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__months_between">
MONTHS_BETWEEN(TIMESTAMP / DATE enddate, TIMESTAMP / DATE startdate)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the number of months from <var class="keyword varname">startdate</var> to
<var class="keyword varname">enddate</var>.
</dd>
<dd class="dd ddexpand">
This result can include a fractional part representing extra days in addition to the
full months between the dates. The fractional component is computed by dividing the
difference in days by 31 (regardless of the month).
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">DOUBLE</code>
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Typically used in business contexts, for example to determine whether a specified
number of months have passed or whether some end-of-month deadline was reached.
</p>
<p class="p">
If the only consideration is the number of full months and any fractional value is
not significant, use <code class="ph codeph">INT_MONTHS_BETWEEN()</code> instead.
</p>
<p class="p">
The method of determining the number of elapsed months includes some special
handling of months with different numbers of days that creates edge cases for dates
between the 28th and 31st days of certain months.
</p>
<p class="p">
If either value is <code class="ph codeph">NULL</code>, which could happen for example when
converting a nonexistent date string such as <code class="ph codeph">'2015-02-29'</code> to a
<code class="ph codeph">TIMESTAMP</code>, the result is also <code class="ph codeph">NULL</code>.
</p>
<p class="p">
If the first argument represents an earlier time than the second argument, the
result is negative.
</p>
<p class="p">
The time portion of the input arguements are ignored.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__months_sub">
MONTHS_SUB(TIMESTAMP / DATE date, INT / BIGINT months)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">months</var>
subtracted from <var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
If <var class="keyword varname">date</var> is the last day of a month, the return date will fall on
the last day of the target month, e.g. <code class="ph codeph">MONTHS_SUB(DATE'2019-02-28',
1)</code> returns <code class="ph codeph">DATE'2019-01-31'</code>.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__nanoseconds_add">
NANOSECONDS_ADD(TIMESTAMP date, INT nanoseconds), NANOSECONDS_ADD(TIMESTAMP date,
BIGINT nanoseconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of nanoseconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Kudu considerations:</strong>
</p>
<p class="p">
The nanosecond portion of an Impala <code class="ph codeph">TIMESTAMP</code> value is rounded to the
nearest microsecond when that value is stored in a Kudu table.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__nanoseconds_sub">
NANOSECONDS_SUB(TIMESTAMP date, INT nanoseconds), NANOSECONDS_SUB(TIMESTAMP date,
BIGINT nanoseconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of nanoseconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Kudu considerations:</strong>
</p>
<p class="p">
The nanosecond portion of an Impala <code class="ph codeph">TIMESTAMP</code> value is rounded to the
nearest microsecond when that value is stored in a Kudu table.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__next_day">
NEXT_DAY(TIMESTAMP / DATE date, STRING weekday)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the date of the <var class="keyword varname">weekday</var> that follows the
specified <var class="keyword varname">date</var>.
<p class="p">
<strong class="ph b">Argument:</strong> The <var class="keyword varname">weekday</var> is not case-sensitive.
</p>
<p class="p">
The following values are accepted for <var class="keyword varname">weekday</var>:
<code class="ph codeph">"Sunday"</code>/<code class="ph codeph">"Sun"</code>,
<code class="ph codeph">"Monday"</code>/<code class="ph codeph">"Mon"</code>,
<code class="ph codeph">"Tuesday"</code>/<code class="ph codeph">"Tue"</code>,
<code class="ph codeph">"Wednesday"</code>/<code class="ph codeph">"Wed"</code>,
<code class="ph codeph">"Thursday"</code>/<code class="ph codeph">"Thu"</code>,
<code class="ph codeph">"Friday"</code>/<code class="ph codeph">"Fri"</code>,
<code class="ph codeph">"Saturday"</code>/<code class="ph codeph">"Sat"</code>
</p>
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
Returns <code class="ph codeph">TIMESTAMP</code> if <var class="keyword varname">date</var> is of the
<code class="ph codeph">TIMESTAMP</code> type.
</li>
<li class="li">
Returns <code class="ph codeph">DATE</code> if <var class="keyword varname">date</var> is of the
<code class="ph codeph">DATE</code> type.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
<code class="ph codeph">NEXT_DAY('2013-12-25','Saturday')</code> returns <code class="ph codeph">'2013-12-28
00:00:00'</code> which is the first Saturday after December 25, 2013.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__now">
NOW()
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the current date and time (in the local time zone) as a
<code class="ph codeph">TIMESTAMP</code> value.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
To find a date/time value in the future or the past relative to the current date and
time, add or subtract an <code class="ph codeph">INTERVAL</code> expression to the return value of
<code class="ph codeph">NOW()</code>. See
<a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for examples.
</p>
<p class="p">
To produce a <code class="ph codeph">TIMESTAMP</code> representing the current date and time that
can be shared or stored without interoperability problems due to time zone
differences, use the <code class="ph codeph">TO_UTC_TIMESTAMP()</code> function and specify the
time zone of the server. When <code class="ph codeph">TIMESTAMP</code> data is stored in UTC form,
any application that queries those values can convert them to the appropriate local
time zone by calling the inverse function, <code class="ph codeph">FROM_UTC_TIMESTAMP()</code>.
</p>
<p class="p">
To determine the time zone of the server you are connected to, in
<span class="keyword">Impala 2.3</span> and higher you can call the
<code class="ph codeph">timeofday()</code> function, which includes the time zone specifier in its
return value. Remember that with cloud computing, the server you interact with might be
in a different time zone than you are, or different sessions might connect to servers in
different time zones, or a cluster might include servers in more than one time zone.
</p>
<p class="p">
Any references to the <code class="ph codeph">NOW()</code> function are evaluated at the start of
a query. All calls to <code class="ph codeph">NOW()</code> within the same query return the same
value, and the value does not depend on how long the query takes.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__quarter">
QUARTER(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the quarter in the input <var class="keyword varname">date</var> argument as
an integer value, 1, 2, 3, or 4, where 1 represents January 1 through March 31.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__second">
SECOND(TIMESTAMP date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the second field from a <code class="ph codeph">TIMESTAMP</code> value.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__seconds_add">
SECONDS_ADD(TIMESTAMP date, INT seconds), SECONDS_ADD(TIMESTAMP date, BIGINT seconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time plus some number of seconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__seconds_sub">
SECONDS_SUB(TIMESTAMP date, INT seconds), SECONDS_SUB(TIMESTAMP date, BIGINT seconds)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the specified date and time minus some number of seconds.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__subdate">
SUBDATE(TIMESTAMP / DATE date, INT / BIGINT days)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Subtracts <var class="keyword varname">days</var> from <var class="keyword varname">date</var> and
returns the new date value.
<p class="p">
The <var class="keyword varname">days</var> value can be negative, which gives the same result as
the <code class="ph codeph">ADDDATE()</code> function.
</p>
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
</dd>
<dt class="dt dlterm" id="datetime_functions__timeofday">
TIMEOFDAY()
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns a string representation of the current date and time,
according to the time of the local system, including any time zone designation.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">STRING</code>
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong> The result value represents similar information as the
<code class="ph codeph">now()</code> function, only as a <code class="ph codeph">STRING</code> 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 <code class="ph codeph">timeofday()</code> function.
Prefer to use <code class="ph codeph">now()</code> if practical for any new Impala code.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__timestamp_cmp">
TIMESTAMP_CMP(TIMESTAMP t1, TIMESTAMP t2)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Tests if one <code class="ph codeph">TIMESTAMP</code> value is newer than, older
than, or identical to another <code class="ph codeph">TIMESTAMP</code>
<ul class="ul">
<li class="li">
<p class="p">
If the first argument represents a later point in time than the second argument,
the result is 1.
</p>
</li>
<li class="li">
<p class="p">
If the first argument represents an earlier point in time than the second
argument, the result is -1.
</p>
</li>
<li class="li">
<p class="p">
If the first and second arguments represent identical points in time, the result
is 0.
</p>
</li>
<li class="li">
<p class="p">
If either argument is <code class="ph codeph">NULL</code>, the result is
<code class="ph codeph">NULL</code>.
</p>
</li>
</ul>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code> (either -1, 0, 1, or <code class="ph codeph">NULL</code>)
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong> A comparison function for <code class="ph codeph">TIMESTAMP</code> values that
only tests whether the date and time increases, decreases, or stays the same.
Similar to the <code class="ph codeph">SIGN()</code> function for numeric values.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__to_date">
TO_DATE(TIMESTAMP ts)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns a string representation of the date field from the
<var class="keyword varname">ts</var> argument.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">STRING</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__to_timestamp">
TO_TIMESTAMP(BIGINT unixtime), TO_TIMESTAMP(STRING date, STRING pattern)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Converts an integer or string representing a date/time value into the
corresponding <code class="ph codeph">TIMESTAMP</code> value.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.3.0</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
An integer argument represents the number of seconds past the epoch (midnight on
January 1, 1970). It is the converse of the <code class="ph codeph">UNIX_TIMESTAMP()</code>
function, which produces a <code class="ph codeph">BIGINT</code> representing the number of
seconds past the epoch.
</p>
<p class="p">
A string argument, plus another string argument representing the pattern, turns an
arbitrary string representation of a date and time into a true
<code class="ph codeph">TIMESTAMP</code> value. The ability to parse many kinds of date and time
formats allows you to deal with temporal data from diverse sources, and if desired
to convert to efficient <code class="ph codeph">TIMESTAMP</code> values during your ETL process.
Using <code class="ph codeph">TIMESTAMP</code> directly in queries and expressions lets you
perform date and time calculations without the overhead of extra function calls and
conversions each time you reference the applicable columns.
</p>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
The following examples demonstrate how to convert an arbitrary string representation
to <code class="ph codeph">TIMESTAMP</code> based on a pattern string:
</p>
<pre class="pre codeblock"><code>
select to_timestamp('Sep 25, 1984', 'MMM dd, yyyy');
+----------------------------------------------+
| to_timestamp('sep 25, 1984', 'mmm dd, yyyy') |
+----------------------------------------------+
| 1984-09-25 00:00:00 |
+----------------------------------------------+
select to_timestamp('1984/09/25', 'yyyy/MM/dd');
+------------------------------------------+
| to_timestamp('1984/09/25', 'yyyy/mm/dd') |
+------------------------------------------+
| 1984-09-25 00:00:00 |
+------------------------------------------+
</code></pre>
<p class="p">
The following examples show how to convert a <code class="ph codeph">BIGINT</code> representing
seconds past epoch into a <code class="ph codeph">TIMESTAMP</code> value:
</p>
<pre class="pre codeblock"><code>
-- One day past the epoch.
select to_timestamp(24 * 60 * 60);
+----------------------------+
| to_timestamp(24 * 60 * 60) |
+----------------------------+
| 1970-01-02 00:00:00 |
+----------------------------+
-- 60 seconds in the past.
select now() as 'current date/time',
unix_timestamp(now()) 'now in seconds',
to_timestamp(unix_timestamp(now()) - 60) as '60 seconds ago';
+-------------------------------+----------------+---------------------+
| current date/time | now in seconds | 60 seconds ago |
+-------------------------------+----------------+---------------------+
| 2017-10-01 22:03:46.885624000 | 1506895426 | 2017-10-01 22:02:46 |
+-------------------------------+----------------+---------------------+
</code></pre>
</dd>
<dt class="dt dlterm" id="datetime_functions__to_utc_timestamp">
TO_UTC_TIMESTAMP(TIMESTAMP ts, STRING timezone)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Converts a specified timestamp value in a specified time zone into the
corresponding value for the UTC time zone.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Often used in combination with the <code class="ph codeph">now()</code> function, to translate
local date and time values to the UTC time zone for consistent representation on
disk. The opposite of the <code class="ph codeph">FROM_UTC_TIMESTAMP()</code> function.
</p>
<p class="p">
See discussion of time zones in
<a class="xref" href="impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for information about
using this function for conversions between the local time zone and UTC.
</p>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
The simplest use of this function is to turn a local date/time value to one with the
standardized UTC time zone. Because the time zone specifier is not saved as part of
the Impala <code class="ph codeph">TIMESTAMP</code> value, all applications that refer to such
data must agree in advance which time zone the values represent. If different parts
of the ETL cycle, or different instances of the application, occur in different time
zones, the ideal reference point is to convert all <code class="ph codeph">TIMESTAMP</code> values
to UTC for storage.
</p>
<pre class="pre codeblock"><code>
select now() as 'Current time in California USA',
to_utc_timestamp(now(), 'PDT') as 'Current time in Greenwich UK';
+--------------------------------+-------------------------------+
| current time in california usa | current time in greenwich uk |
+--------------------------------+-------------------------------+
| 2016-06-01 15:52:08.980072000 | 2016-06-01 22:52:08.980072000 |
+--------------------------------+-------------------------------+
</code></pre>
<p class="p">
Once a value is converted to the UTC time zone by
<code class="ph codeph">TO_UTC_TIMESTAMP()</code>, it can be converted back to the local time zone
with <code class="ph codeph">FROM_UTC_TIMESTAMP()</code>. You can combine these functions using
different time zone identifiers to convert a <code class="ph codeph">TIMESTAMP</code> between any
two time zones. This example starts with a <code class="ph codeph">TIMESTAMP</code> value
representing Pacific Daylight Time, converts it to UTC, and converts it to the
equivalent value in Eastern Daylight Time.
</p>
<pre class="pre codeblock"><code>
select now() as 'Current time in California USA',
from_utc_timestamp
(
to_utc_timestamp(now(), 'PDT'),
'EDT'
) as 'Current time in New York, USA';
+--------------------------------+-------------------------------+
| current time in california usa | current time in new york, usa |
+--------------------------------+-------------------------------+
| 2016-06-01 18:14:12.743658000 | 2016-06-01 21:14:12.743658000 |
+--------------------------------+-------------------------------+
</code></pre>
</dd>
<dt class="dt dlterm" id="datetime_functions__trunc_timestamp"><a id="datetime_functions__trunc"></a>
TRUNC(TIMESTAMP / DATE ts, STRING unit)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the <var class="keyword varname">ts</var> truncated to the
<var class="keyword varname">unit</var> specified.
<p class="p">
<strong class="ph b">Argument:</strong> The <var class="keyword varname">unit</var> argument is not case-sensitive. This
argument string can be one of:
</p>
<div class="p">
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" id="datetime_functions__table_evg_bhp_p3b" class="table" frame="border" border="1" rules="all"><colgroup><col style="width:25%" /><col style="width:25%" /><col style="width:25%" /><col style="width:25%" /></colgroup><thead class="thead" style="text-align:left;">
<tr class="row">
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e3795">
Unit
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e3798">
Supported for TIMESTAMP ts
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e3801">
Supported for DATE ts
</th>
<th class="entry cellrowborder" style="text-align:left;vertical-align:top;" id="d52606e3804">
Description
</th>
</tr>
</thead>
<tbody class="tbody">
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'SYYYY'</code>
</p>
<p class="p">
<code class="ph codeph">'YYYY'</code>
</p>
<p class="p">
<code class="ph codeph">'YEAR'</code>
</p>
<p class="p">
<code class="ph codeph">'SYEAR'</code>
</p>
<p class="p">
<code class="ph codeph">'YYY'</code>
</p>
<p class="p">
<code class="ph codeph">'YY'</code>
</p>
<p class="p">
<code class="ph codeph">'Y'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Year
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'Q'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Quarter
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'MONTH'</code>
</p>
<p class="p">
<code class="ph codeph">'MON'</code>
</p>
<p class="p">
<code class="ph codeph">'MM'</code>
</p>
<p class="p">
<code class="ph codeph">'RM'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Month
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'WW'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
The most recent date that is the same day of the week as the first day of
the year
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'W'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
The most recent date that is the same day of the week as the first day of
the month
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'DDD'</code>
</p>
<p class="p">
<code class="ph codeph">'DD'</code>
</p>
<p class="p">
<code class="ph codeph">'J'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Day
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'DAY'</code>
</p>
<p class="p">
<code class="ph codeph">'DY'</code>
</p>
<p class="p">
<code class="ph codeph">'D'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Starting day of the week (Monday)
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<p class="p">
<code class="ph codeph">'HH'</code>
</p>
<p class="p">
<code class="ph codeph">'HH12'</code>
</p>
<p class="p">
<code class="ph codeph">'HH24'</code>
</p>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
No
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Hour. A <code class="ph codeph">TIMESTAMP</code> value truncated to the hour is always
represented in 24-hour notation, even for the <code class="ph codeph">HH12</code>
argument string.
</td>
</tr>
<tr class="row">
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3795 ">
<code class="ph codeph">'MI'</code>
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3798 ">
Yes
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3801 ">
No
</td>
<td class="entry cellrowborder" style="text-align:left;vertical-align:top;" headers="d52606e3804 ">
Minute
</td>
</tr>
</tbody>
</table>
</div>
</div>
<p class="p">
<strong class="ph b">Added in:</strong> The ability to truncate numeric values is new starting in
<span class="keyword">Impala 2.10</span>.
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
The <code class="ph codeph">TIMESTAMP</code> form is typically used in <code class="ph codeph">GROUP BY</code>
queries to aggregate results from the same hour, day, week, month, quarter, and so
on. You can also use this function in an <code class="ph codeph">INSERT ... SELECT</code> into a
partitioned table to divide <code class="ph codeph">TIMESTAMP</code> values into the correct
partition.
</p>
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
<code class="ph codeph">TIMESTAMP</code> if the first argument, <var class="keyword varname">ts</var>, is
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
<code class="ph codeph">DATE</code> if the first argument, <var class="keyword varname">ts</var>, is
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Example:</strong>
</p>
<p class="p">
<code class="ph codeph">TRUNC(DATE'2019-05-08','YEAR')</code> returns <code class="ph codeph">2019-01-01</code>.
</p>
<p class="p">
<code class="ph codeph">TRUNC(DATE'2019-05-08', 'QUARTER')</code> returns
<code class="ph codeph">2019-04-01</code>.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__unix_timestamp">
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(STRING datetime), UNIX_TIMESTAMP(STRING datetime,
STRING pattern), UNIX_TIMESTAMP(TIMESTAMP datetime)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns a Unix time, which is a number of seconds elapsed since
'1970-01-01 00:00:00' UTC. If called with no argument, the current date and time is
converted to its Unix time. If called with arguments, the first argument represented
as the <code class="ph codeph">TIMESTAMP</code> or <code class="ph codeph">STRING</code> is converted to its Unix
time.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">BIGINT</code>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
See <code class="ph codeph">FROM_UNIXTIME()</code> for details about the patterns you can use in
the <var class="keyword varname">pattern</var> string to represent the position of year, month, day,
and so on in the <code class="ph codeph">date</code> string. In Impala 1.3 and higher, you have
more flexibility to switch the positions of elements and use different separator
characters.
</p>
<p class="p">
In <span class="keyword">Impala 2.2.3</span> and higher, you can include a trailing uppercase
<code class="ph codeph">Z</code> qualifier to indicate <span class="q">"Zulu"</span> time, a synonym for UTC.
</p>
<p class="p">
In <span class="keyword">Impala 2.3</span> and higher, you can include a timezone offset
specified as minutes and hours, provided you also specify the details in the
<var class="keyword varname">pattern</var> string argument. The offset is specified in the
<var class="keyword varname">pattern</var> string as a plus or minus sign followed by
<code class="ph codeph">hh:mm</code>, <code class="ph codeph">hhmm</code>, or <code class="ph codeph">hh</code>. The
<code class="ph codeph">hh</code> must be lowercase, to distinguish it from the
<code class="ph codeph">HH</code> represent hours in the actual time value. Currently, only
numeric timezone offsets are allowed, not symbolic names.
</p>
<p class="p">
In Impala 2.2.0 and higher, built-in functions that accept or return integers
representing <code class="ph codeph">TIMESTAMP</code> values use the <code class="ph codeph">BIGINT</code> type for
parameters and return values, rather than <code class="ph codeph">INT</code>. This change lets the
date and time functions avoid an overflow error that would otherwise occur on January
19th, 2038 (known as the
<a class="xref" href="http://en.wikipedia.org/wiki/Year_2038_problem" target="_blank"><span class="q">"Year
2038 problem"</span> or <span class="q">"Y2K38 problem"</span></a>). This change affects the
<code class="ph codeph">FROM_UNIXTIME()</code> and <code class="ph codeph">UNIX_TIMESTAMP()</code> functions. You
might need to change application code that interacts with these functions, change the
types of columns that store the return values, or add <code class="ph codeph">CAST()</code> calls to
SQL statements that call these functions.
</p>
<div class="p">
<code class="ph codeph">UNIX_TIMESTAMP()</code> and <code class="ph codeph">FROM_UNIXTIME()</code> are often used in
combination to convert a <code class="ph codeph">TIMESTAMP</code> value into a particular string
format. For example:
<pre class="pre codeblock"><code>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW() + interval 3 days),
'yyyy/MM/dd HH:mm') AS yyyy_mm_dd_hh_mm;
+------------------+
| yyyy_mm_dd_hh_mm |
+------------------+
| 2016/06/03 11:38 |
+------------------+
</code></pre>
</div>
<p class="p">
The way this function deals with time zones when converting to or from
<code class="ph codeph">TIMESTAMP</code> values is affected by the
<code class="ph codeph">‑‑use_local_tz_for_unix_timestamp_conversions</code> startup flag
for the <span class="keyword cmdname">impalad</span> daemon. See
<a class="xref" href="../shared/../topics/impala_timestamp.html#timestamp">TIMESTAMP Data Type</a> for details about how
Impala handles time zone considerations for the <code class="ph codeph">TIMESTAMP</code> data type.
</p>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
The following examples show different ways of turning the same date and time into an
integer value. A <var class="keyword varname">pattern</var> string that Impala recognizes by default
is interpreted as a UTC date and time. The trailing <code class="ph codeph">Z</code> 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 class="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
<code class="ph codeph">-07:00</code> and the equivalent suffix of <code class="ph codeph">-hh:mm</code> in the
pattern string, or specify the mnemonic name for the time zone in a call to
<code class="ph codeph">TO_UTC_TIMESTAMP()</code>. This particular date and time expressed in PDT
translates to a different number than the same date and time expressed in UTC.
</p>
<pre class="pre codeblock"><code>
-- 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'
) as may_15_month_day_year;
+-----------------------+
| may_15_month_day_year |
+-----------------------+
| 1431691200 |
+-----------------------+
-- 2 ways of expressing the same date and time but in a different timezone.
-- The resulting integer is different from the previous examples.
select unix_timestamp
(
'2015-05-15 12:00:00-07:00',
'yyyy-MM-dd HH:mm:ss-hh:mm'
) as may_15_year_month_day;
+-----------------------+
| may_15_year_month_day |
+-----------------------+
| 1431716400 |
+-----------------------+
select unix_timestamp
(to_utc_timestamp(
'2015-05-15 12:00:00',
'PDT')
) as may_15_pdt;
+------------+
| may_15_pdt |
+------------+
| 1431716400 |
+------------+
</code></pre>
</dd>
<dt class="dt dlterm" id="datetime_functions__utc_timestamp">
UTC_TIMESTAMP()
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns a <code class="ph codeph">TIMESTAMP</code> corresponding to the current date
and time in the UTC time zone.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">TIMESTAMP</code>
</p>
<p class="p">
<strong class="ph b">Added in:</strong> <span class="keyword">Impala 2.10</span>
</p>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
Similar to the <code class="ph codeph">NOW()</code> or <code class="ph codeph">CURRENT_TIMESTAMP()</code>
functions, but does not use the local time zone as those functions do. Use
<code class="ph codeph">UTC_TIMESTAMP()</code> to record <code class="ph codeph">TIMESTAMP</code> values that
are interoperable with servers around the world, in arbitrary time zones, without
the need for additional conversion functions to standardize the time zone of each
value representing a date/time.
</p>
<p class="p">
For working with date/time values represented as integer values, you can convert
back and forth between <code class="ph codeph">TIMESTAMP</code> and <code class="ph codeph">BIGINT</code> with
the <code class="ph codeph">UNIX_MICROS_TO_UTC_TIMESTAMP()</code> and
<code class="ph codeph">UTC_TO_UNIX_MICROS()</code> functions. The integer values represent the
number of microseconds since the Unix epoch (midnight on January 1, 1970).
</p>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
The following example shows how <code class="ph codeph">NOW()</code> and
<code class="ph codeph">CURRENT_TIMESTAMP()</code> represent the current date/time in the local
time zone (in this case, UTC-7), while <code class="ph codeph">utc_timestamp()</code> represents
the same date/time in the standardized UTC time zone:
</p>
<pre class="pre codeblock"><code>
select now(), utc_timestamp();
+-------------------------------+-------------------------------+
| now() | utc_timestamp() |
+-------------------------------+-------------------------------+
| 2017-10-01 23:33:58.919688000 | 2017-10-02 06:33:58.919688000 |
+-------------------------------+-------------------------------+
select current_timestamp(), utc_timestamp();
+-------------------------------+-------------------------------+
| current_timestamp() | utc_timestamp() |
+-------------------------------+-------------------------------+
| 2017-10-01 23:34:07.400642000 | 2017-10-02 06:34:07.400642000 |
+-------------------------------+-------------------------------+
</code></pre>
</dd>
<dt class="dt dlterm" id="datetime_functions__weekofyear">
WEEK(TIMESTAMP / DATE date), WEEKOFYEAR(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the corresponding week (1-53) from the <var class="keyword varname">date</var>
argument.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__weeks_add">
WEEKS_ADD(TIMESTAMP / DATE date, INT / BIGINT weeks)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">weeks</var> added to
<var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
</dd>
<dt class="dt dlterm" id="datetime_functions__weeks_sub">
WEEKS_SUB(TIMESTAMP / DATE date, INT / BIGINT weeks)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">weeks</var>
subtracted from <var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
</dd>
<dt class="dt dlterm" id="datetime_functions__year">
YEAR(TIMESTAMP / DATE date)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the year field from the <var class="keyword varname">date</var> argument.
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">INT</code>
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__years_add">
YEARS_ADD(TIMESTAMP / DATE date, INT / BIGINT years)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">years</var> added to
<var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
If the equivalent date does not exist in the year of the result due to a leap year,
the date is changed to the last day of the appropriate month.
</p>
</dd>
<dt class="dt dlterm" id="datetime_functions__years_sub">
YEARS_SUB(TIMESTAMP / DATE date, INT / BIGINT years)
</dt>
<dd class="dd">
<strong class="ph b">Purpose:</strong> Returns the value with the number of <var class="keyword varname">years</var>
subtracted from <var class="keyword varname">date</var>.
<div class="p">
<strong class="ph b">Return type:</strong>
<ul class="ul">
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">TIMESTAMP</code>, returns
<code class="ph codeph">TIMESTAMP</code>.
</li>
<li class="li">
If <var class="keyword varname">date</var> is <code class="ph codeph">DATE</code>, returns
<code class="ph codeph">DATE</code>.
</li>
</ul>
</div>
<p class="p">
<strong class="ph b">Usage notes:</strong>
</p>
<p class="p">
If the equivalent date does not exist in the year of the result due to a leap year,
the date is changed to the last day of the appropriate month.
</p>
<p class="p"></p>
</dd>
</dl>
</div>
<div class="related-links">
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_functions.html">Impala Built-In Functions</a></div>
</div>
</div></body>
</html>