Google Git
Sign in
apache / spark-website / asf-site / . / site / docs / 3.5.3 / sql-ref-literals.html
blob: 4a16708c884624d74ae42456bf9a2b763f0af001 [file] [log] [blame]
<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Literals - Spark 3.5.3 Documentation</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=DM+Sans:ital,wght@0,400;0,500;0,700;1,400;1,500;1,700&Courier+Prime:wght@400;700&display=swap" rel="stylesheet">
<link href="css/custom.css" rel="stylesheet">
<script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script>
<link rel="stylesheet" href="css/pygments-default.css">
<link rel="stylesheet" href="css/docsearch.min.css" />
<link rel="stylesheet" href="css/docsearch.css">
<!-- Matomo -->
<script>
var _paq = window._paq = window._paq || [];
/* tracker methods like "setCustomDimension" should be called before "trackPageView" */
_paq.push(["disableCookies"]);
_paq.push(['trackPageView']);
_paq.push(['enableLinkTracking']);
(function() {
var u="https://analytics.apache.org/";
_paq.push(['setTrackerUrl', u+'matomo.php']);
_paq.push(['setSiteId', '40']);
var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
g.async=true; g.src=u+'matomo.js'; s.parentNode.insertBefore(g,s);
})();
</script>
<!-- End Matomo Code -->
</head>
<body class="global">
<!--[if lt IE 7]>
<p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p>
<![endif]-->
<!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->
<nav class="navbar navbar-expand-lg navbar-dark p-0 px-4 fixed-top" style="background: #1d6890;" id="topbar">
<div class="navbar-brand"><a href="index.html">
<img src="img/spark-logo-rev.svg" width="141" height="72"/></a><span class="version">3.5.3</span>
</div>
<button class="navbar-toggler" type="button" data-toggle="collapse"
data-target="#navbarCollapse" aria-controls="navbarCollapse"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarCollapse">
<ul class="navbar-nav me-auto">
<li class="nav-item"><a href="index.html" class="nav-link">Overview</a></li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarQuickStart" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Programming Guides</a>
<div class="dropdown-menu" aria-labelledby="navbarQuickStart">
<a class="dropdown-item" href="quick-start.html">Quick Start</a>
<a class="dropdown-item" href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a>
<a class="dropdown-item" href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a>
<a class="dropdown-item" href="structured-streaming-programming-guide.html">Structured Streaming</a>
<a class="dropdown-item" href="streaming-programming-guide.html">Spark Streaming (DStreams)</a>
<a class="dropdown-item" href="ml-guide.html">MLlib (Machine Learning)</a>
<a class="dropdown-item" href="graphx-programming-guide.html">GraphX (Graph Processing)</a>
<a class="dropdown-item" href="sparkr.html">SparkR (R on Spark)</a>
<a class="dropdown-item" href="api/python/getting_started/index.html">PySpark (Python on Spark)</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarAPIDocs" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">API Docs</a>
<div class="dropdown-menu" aria-labelledby="navbarAPIDocs">
<a class="dropdown-item" href="api/scala/org/apache/spark/index.html">Scala</a>
<a class="dropdown-item" href="api/java/index.html">Java</a>
<a class="dropdown-item" href="api/python/index.html">Python</a>
<a class="dropdown-item" href="api/R/index.html">R</a>
<a class="dropdown-item" href="api/sql/index.html">SQL, Built-in Functions</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarDeploying" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Deploying</a>
<div class="dropdown-menu" aria-labelledby="navbarDeploying">
<a class="dropdown-item" href="cluster-overview.html">Overview</a>
<a class="dropdown-item" href="submitting-applications.html">Submitting Applications</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="spark-standalone.html">Spark Standalone</a>
<a class="dropdown-item" href="running-on-mesos.html">Mesos</a>
<a class="dropdown-item" href="running-on-yarn.html">YARN</a>
<a class="dropdown-item" href="running-on-kubernetes.html">Kubernetes</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarMore" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">More</a>
<div class="dropdown-menu" aria-labelledby="navbarMore">
<a class="dropdown-item" href="configuration.html">Configuration</a>
<a class="dropdown-item" href="monitoring.html">Monitoring</a>
<a class="dropdown-item" href="tuning.html">Tuning Guide</a>
<a class="dropdown-item" href="job-scheduling.html">Job Scheduling</a>
<a class="dropdown-item" href="security.html">Security</a>
<a class="dropdown-item" href="hardware-provisioning.html">Hardware Provisioning</a>
<a class="dropdown-item" href="migration-guide.html">Migration Guide</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="building-spark.html">Building Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/contributing.html">Contributing to Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a>
</div>
</li>
<li class="nav-item">
<input type="text" id="docsearch-input" placeholder="Search the docs…">
</li>
</ul>
<!--<span class="navbar-text navbar-right"><span class="version-text">v3.5.3</span></span>-->
</div>
</nav>
<div class="container">
<div class="left-menu-wrapper">
<div class="left-menu">
<h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3>
<ul>
<li>
<a href="sql-getting-started.html">
Getting Started
</a>
</li>
<li>
<a href="sql-data-sources.html">
Data Sources
</a>
</li>
<li>
<a href="sql-performance-tuning.html">
Performance Tuning
</a>
</li>
<li>
<a href="sql-distributed-sql-engine.html">
Distributed SQL Engine
</a>
</li>
<li>
<a href="sql-pyspark-pandas-with-arrow.html">
PySpark Usage Guide for Pandas with Apache Arrow
</a>
</li>
<li>
<a href="sql-migration-guide.html">
Migration Guide
</a>
</li>
<li>
<a href="sql-ref.html">
SQL Reference
</a>
</li>
<ul>
<li>
<a href="sql-ref-ansi-compliance.html">
ANSI Compliance
</a>
</li>
<li>
<a href="sql-ref-datatypes.html">
Data Types
</a>
</li>
<li>
<a href="sql-ref-datetime-pattern.html">
Datetime Pattern
</a>
</li>
<li>
<a href="sql-ref-number-pattern.html">
Number Pattern
</a>
</li>
<li>
<a href="sql-ref-functions.html">
Functions
</a>
</li>
<li>
<a href="sql-ref-identifier.html">
Identifiers
</a>
</li>
<li>
<a href="sql-ref-literals.html">
Literals
</a>
</li>
<li>
<a href="sql-ref-null-semantics.html">
Null Semantics
</a>
</li>
<li>
<a href="sql-ref-syntax.html">
SQL Syntax
</a>
</li>
</ul>
<li>
<a href="sql-error-conditions.html">
Error Conditions
</a>
</li>
</ul>
</div>
</div>
<input id="nav-trigger" class="nav-trigger" checked type="checkbox">
<label for="nav-trigger"></label>
<div class="content-with-sidebar mr-3" id="content">
<h1 class="title">Literals</h1>
<p>A literal (also known as a constant) represents a fixed data value. Spark SQL supports the following literals:</p>
<ul>
<li><a href="#string-literal">String Literal</a></li>
<li><a href="#binary-literal">Binary Literal</a></li>
<li><a href="#null-literal">Null Literal</a></li>
<li><a href="#boolean-literal">Boolean Literal</a></li>
<li><a href="#numeric-literal">Numeric Literal</a></li>
<li><a href="#datetime-literal">Datetime Literal</a></li>
<li><a href="#interval-literal">Interval Literal</a></li>
</ul>
<h3 id="string-literal">String Literal</h3>
<p>A string literal is used to specify a character string value.</p>
<h4 id="syntax">Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="p">[</span> <span class="n">r</span> <span class="p">]</span> <span class="err">{</span> <span class="s1">'char [ ... ]'</span> <span class="o">|</span> <span class="nv">"char [ ... ]"</span> <span class="err">}</span>
</code></pre></div></div>
<h4 id="parameters">Parameters</h4>
<ul>
<li>
<p><strong>char</strong></p>
<p>One character from the character set. Use <code class="language-plaintext highlighter-rouge">\</code> to escape special characters (e.g., <code class="language-plaintext highlighter-rouge">'</code> or <code class="language-plaintext highlighter-rouge">\</code>).
To represent unicode characters, use 16-bit or 32-bit unicode escape of the form <code class="language-plaintext highlighter-rouge">\uxxxx</code> or <code class="language-plaintext highlighter-rouge">\Uxxxxxxxx</code>,
where xxxx and xxxxxxxx are 16-bit and 32-bit code points in hexadecimal respectively (e.g., <code class="language-plaintext highlighter-rouge">\u3042</code> for <code class="language-plaintext highlighter-rouge">あ</code> and <code class="language-plaintext highlighter-rouge">\U0001F44D</code> for <code class="language-plaintext highlighter-rouge">👍</code>).</p>
</li>
<li>
<p><strong>r</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">RAW</code>. If a string literal starts with <code class="language-plaintext highlighter-rouge">r</code> prefix, neither special characters nor unicode characters are escaped by <code class="language-plaintext highlighter-rouge">\</code>.</p>
</li>
</ul>
<h4 id="examples">Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="s1">'Hello, World!'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------+</span>
<span class="o">|</span><span class="n">Hello</span><span class="p">,</span> <span class="n">World</span><span class="o">!|</span>
<span class="o">+</span><span class="c1">-------------+</span>
<span class="k">SELECT</span> <span class="nv">"SPARK SQL"</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="o">|</span><span class="n">Spark</span> <span class="k">SQL</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="k">SELECT</span> <span class="s1">'it</span><span class="se">\'</span><span class="s1">s $10.'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="o">|</span><span class="n">It</span><span class="s1">'s $10.|
+---------+
SELECT r"'</span><span class="err">\</span><span class="n">n</span><span class="s1">' represents newline character." AS col;
+----------------------------------+
| col|
+----------------------------------+
|'</span><span class="err">\</span><span class="n">n</span><span class="s1">' represents newline character.|
+----------------------------------+
</span></code></pre></div></div>
<h3 id="binary-literal">Binary Literal</h3>
<p>A binary literal is used to specify a byte sequence value.</p>
<h4 id="syntax-1">Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">X</span> <span class="err">{</span> <span class="s1">'num [ ... ]'</span> <span class="o">|</span> <span class="nv">"num [ ... ]"</span> <span class="err">}</span>
</code></pre></div></div>
<h4 id="parameters-1">Parameters</h4>
<ul>
<li>
<p><strong>num</strong></p>
<p>Any hexadecimal number from 0 to F.</p>
</li>
</ul>
<h4 id="examples-1">Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="n">X</span><span class="s1">'123456'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span><span class="p">[</span><span class="mi">12</span> <span class="mi">34</span> <span class="mi">56</span><span class="p">]</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
</code></pre></div></div>
<h3 id="null-literal">Null Literal</h3>
<p>A null literal is used to specify a null value.</p>
<h4 id="syntax-2">Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">NULL</span>
</code></pre></div></div>
<h4 id="examples-2">Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="k">NULL</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----+</span>
<span class="o">|</span><span class="k">NULL</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----+</span>
</code></pre></div></div>
<h3 id="boolean-literal">Boolean Literal</h3>
<p>A boolean literal is used to specify a boolean value.</p>
<h4 id="syntax-3">Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">TRUE</span> <span class="o">|</span> <span class="k">FALSE</span>
</code></pre></div></div>
<h4 id="examples-3">Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="k">TRUE</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----+</span>
<span class="o">|</span><span class="k">true</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----+</span>
</code></pre></div></div>
<h3 id="numeric-literal">Numeric Literal</h3>
<p>A numeric literal is used to specify a fixed or floating-point number.
There are two kinds of numeric literals: integral literal and fractional literal.</p>
<h4 id="integral-literal-syntax">Integral Literal Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="p">[</span> <span class="o">+</span> <span class="o">|</span> <span class="o">-</span> <span class="p">]</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="p">[</span> <span class="n">L</span> <span class="o">|</span> <span class="n">S</span> <span class="o">|</span> <span class="n">Y</span> <span class="p">]</span>
</code></pre></div></div>
<h4 id="integral-literal-parameters">Integral Literal Parameters</h4>
<ul>
<li>
<p><strong>digit</strong></p>
<p>Any numeral from 0 to 9.</p>
</li>
<li>
<p><strong>L</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">BIGINT</code>, which is an 8-byte signed integer number.</p>
</li>
<li>
<p><strong>S</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">SMALLINT</code>, which is a 2-byte signed integer number.</p>
</li>
<li>
<p><strong>Y</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">TINYINT</code>, which is a 1-byte signed integer number.</p>
</li>
<li>
<p><strong>default (no postfix)</strong></p>
<p>Indicates a 4-byte signed integer number.</p>
</li>
</ul>
<h4 id="integral-literal-examples">Integral Literal Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="o">-</span><span class="mi">2147483648</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+</span>
<span class="o">|-</span><span class="mi">2147483648</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------+</span>
<span class="k">SELECT</span> <span class="mi">9223372036854775807</span><span class="n">l</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="mi">9223372036854775807</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="k">SELECT</span> <span class="o">-</span><span class="mi">32</span><span class="n">Y</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|-</span><span class="mi">32</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="k">SELECT</span> <span class="mi">482</span><span class="n">S</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="mi">482</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
</code></pre></div></div>
<h4 id="fractional-literals-syntax">Fractional Literals Syntax</h4>
<p>decimal literals:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">decimal_digits</span> <span class="err">{</span> <span class="p">[</span> <span class="n">BD</span> <span class="p">]</span> <span class="o">|</span> <span class="p">[</span> <span class="n">exponent</span> <span class="n">BD</span> <span class="p">]</span> <span class="err">}</span> <span class="o">|</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="p">[</span> <span class="n">exponent</span> <span class="p">]</span> <span class="n">BD</span>
</code></pre></div></div>
<p>double literals:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">decimal_digits</span> <span class="err">{</span> <span class="n">D</span> <span class="o">|</span> <span class="n">exponent</span> <span class="p">[</span> <span class="n">D</span> <span class="p">]</span> <span class="err">}</span> <span class="o">|</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="err">{</span> <span class="n">exponent</span> <span class="p">[</span> <span class="n">D</span> <span class="p">]</span> <span class="o">|</span> <span class="p">[</span> <span class="n">exponent</span> <span class="p">]</span> <span class="n">D</span> <span class="err">}</span>
</code></pre></div></div>
<p>float literals:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">decimal_digits</span> <span class="err">{</span> <span class="n">F</span> <span class="o">|</span> <span class="n">exponent</span> <span class="p">[</span> <span class="n">F</span> <span class="p">]</span> <span class="err">}</span> <span class="o">|</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="err">{</span> <span class="n">exponent</span> <span class="p">[</span> <span class="n">F</span> <span class="p">]</span> <span class="o">|</span> <span class="p">[</span> <span class="n">exponent</span> <span class="p">]</span> <span class="n">F</span> <span class="err">}</span>
</code></pre></div></div>
<p>While decimal_digits is defined as</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="p">[</span> <span class="o">+</span> <span class="o">|</span> <span class="o">-</span> <span class="p">]</span> <span class="err">{</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="p">.</span> <span class="p">[</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="p">]</span> <span class="o">|</span> <span class="p">.</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span> <span class="err">}</span>
</code></pre></div></div>
<p>and exponent is defined as</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">E</span> <span class="p">[</span> <span class="o">+</span> <span class="o">|</span> <span class="o">-</span> <span class="p">]</span> <span class="n">digit</span> <span class="p">[</span> <span class="p">...</span> <span class="p">]</span>
</code></pre></div></div>
<h4 id="fractional-literals-parameters">Fractional Literals Parameters</h4>
<ul>
<li>
<p><strong>digit</strong></p>
<p>Any numeral from 0 to 9.</p>
</li>
<li>
<p><strong>D</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">DOUBLE</code>, which is an 8-byte double-precision floating point number.</p>
</li>
<li>
<p><strong>F</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">FLOAT</code>, which is a 4-byte single-precision floating point number.</p>
</li>
<li>
<p><strong>BD</strong></p>
<p>Case insensitive, indicates <code class="language-plaintext highlighter-rouge">DECIMAL</code>, with the total number of digits as precision and the number of digits to right of decimal point as scale.</p>
</li>
</ul>
<h4 id="fractional-literals-examples">Fractional Literals Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="mi">12</span><span class="p">.</span><span class="mi">578</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="o">|</span><span class="mi">12</span><span class="p">.</span><span class="mi">578</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="k">SELECT</span> <span class="o">-</span><span class="mi">0</span><span class="p">.</span><span class="mi">1234567</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|-</span><span class="mi">0</span><span class="p">.</span><span class="mi">1234567</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="k">SELECT</span> <span class="o">-</span><span class="p">.</span><span class="mi">1234567</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|-</span><span class="mi">0</span><span class="p">.</span><span class="mi">1234567</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="k">SELECT</span> <span class="mi">123</span><span class="p">.</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="mi">123</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="k">SELECT</span> <span class="mi">123</span><span class="p">.</span><span class="n">BD</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="mi">123</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="k">SELECT</span> <span class="mi">5</span><span class="n">E2</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----+</span>
<span class="o">|</span><span class="mi">500</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----+</span>
<span class="k">SELECT</span> <span class="mi">5</span><span class="n">D</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="mi">5</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="k">SELECT</span> <span class="o">-</span><span class="mi">5</span><span class="n">BD</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span><span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="o">|</span> <span class="o">-</span><span class="mi">5</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+</span>
<span class="k">SELECT</span> <span class="mi">12</span><span class="p">.</span><span class="mi">578</span><span class="n">e</span><span class="o">-</span><span class="mi">2</span><span class="n">d</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------+</span>
<span class="o">|</span><span class="mi">0</span><span class="p">.</span><span class="mi">12578</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------+</span>
<span class="k">SELECT</span> <span class="o">-</span><span class="p">.</span><span class="mi">1234567</span><span class="n">E</span><span class="o">+</span><span class="mi">2</span><span class="n">BD</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="o">|-</span><span class="mi">12</span><span class="p">.</span><span class="mi">34567</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---------+</span>
<span class="k">SELECT</span> <span class="o">+</span><span class="mi">3</span><span class="p">.</span><span class="n">e</span><span class="o">+</span><span class="mi">3</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="o">|</span><span class="mi">3000</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="k">SELECT</span> <span class="o">-</span><span class="mi">3</span><span class="p">.</span><span class="n">E</span><span class="o">-</span><span class="mi">3</span><span class="n">D</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+</span>
<span class="o">|-</span><span class="mi">0</span><span class="p">.</span><span class="mi">003</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+</span>
</code></pre></div></div>
<h3 id="datetime-literal">Datetime Literal</h3>
<p>A datetime literal is used to specify a date or timestamp value.</p>
<h4 id="date-syntax">Date Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="nb">DATE</span> <span class="err">{</span> <span class="s1">'yyyy'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d[T]'</span> <span class="err">}</span>
</code></pre></div></div>
<p><strong>Note:</strong> defaults to <code class="language-plaintext highlighter-rouge">01</code> if month or day is not specified.</p>
<h4 id="date-examples">Date Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="nb">DATE</span> <span class="s1">'1997'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span><span class="mi">1997</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="k">SELECT</span> <span class="nb">DATE</span> <span class="s1">'1997-01'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span><span class="mi">1997</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="k">SELECT</span> <span class="nb">DATE</span> <span class="s1">'2011-11-11'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
<span class="o">|</span><span class="mi">2011</span><span class="o">-</span><span class="mi">11</span><span class="o">-</span><span class="mi">11</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+</span>
</code></pre></div></div>
<h4 id="timestamp-syntax">Timestamp Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="nb">TIMESTAMP</span> <span class="err">{</span> <span class="s1">'yyyy'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d '</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d[T][h]h[:]'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d[T][h]h:[m]m[:]'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s[.]'</span> <span class="o">|</span>
<span class="s1">'yyyy-[m]m-[d]d[T][h]h:[m]m:[s]s.[ms][ms][ms][us][us][us][zone_id]'</span><span class="err">}</span>
</code></pre></div></div>
<p><strong>Note:</strong> defaults to <code class="language-plaintext highlighter-rouge">00</code> if hour, minute or second is not specified.
<code class="language-plaintext highlighter-rouge">zone_id</code> should have one of the forms:</p>
<ul>
<li>Z - Zulu time zone UTC+0</li>
<li><code class="language-plaintext highlighter-rouge">+|-[h]h:[m]m</code></li>
<li>An id with one of the prefixes UTC+, UTC-, GMT+, GMT-, UT+ or UT-, and a suffix in the formats:
<ul>
<li><code class="language-plaintext highlighter-rouge">+|-h[h]</code></li>
<li><code class="language-plaintext highlighter-rouge">+|-hh[:]mm</code></li>
<li><code class="language-plaintext highlighter-rouge">+|-hh:mm:ss</code></li>
<li><code class="language-plaintext highlighter-rouge">+|-hhmmss</code></li>
</ul>
</li>
<li>Region-based zone IDs in the form <code class="language-plaintext highlighter-rouge">area/city</code>, such as <code class="language-plaintext highlighter-rouge">Europe/Paris</code></li>
</ul>
<p><strong>Note:</strong> defaults to the session local timezone (set via <code class="language-plaintext highlighter-rouge">spark.sql.session.timeZone</code>) if <code class="language-plaintext highlighter-rouge">zone_id</code> is not specified.</p>
<h4 id="timestamp-examples">Timestamp Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="nb">TIMESTAMP</span> <span class="s1">'1997-01-31 09:26:56.123'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="o">|</span><span class="mi">1997</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">31</span> <span class="mi">09</span><span class="p">:</span><span class="mi">26</span><span class="p">:</span><span class="mi">56</span><span class="p">.</span><span class="mi">123</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------+</span>
<span class="k">SELECT</span> <span class="nb">TIMESTAMP</span> <span class="s1">'1997-01-31 09:26:56.66666666UTC+08:00'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">--------------------------+</span>
<span class="o">|</span> <span class="n">col</span> <span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------+</span>
<span class="o">|</span><span class="mi">1997</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">30</span> <span class="mi">17</span><span class="p">:</span><span class="mi">26</span><span class="p">:</span><span class="mi">56</span><span class="p">.</span><span class="mi">666666</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------+</span>
<span class="k">SELECT</span> <span class="nb">TIMESTAMP</span> <span class="s1">'1997-01'</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
<span class="o">|</span><span class="mi">1997</span><span class="o">-</span><span class="mi">01</span><span class="o">-</span><span class="mi">01</span> <span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="p">:</span><span class="mi">00</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------------------+</span>
</code></pre></div></div>
<h3 id="interval-literal">Interval Literal</h3>
<p>An interval literal is used to specify a fixed period of time.
The interval literal supports two syntaxes: ANSI syntax and multi-units syntax.</p>
<h4 id="ansi-syntax">ANSI Syntax</h4>
<p>The ANSI SQL standard defines interval literals in the form:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">INTERVAL</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">sign</span><span class="o">&gt;</span> <span class="p">]</span> <span class="o">&lt;</span><span class="n">interval</span> <span class="n">string</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">interval</span> <span class="n">qualifier</span><span class="o">&gt;</span>
</code></pre></div></div>
<p>where <code class="language-plaintext highlighter-rouge">&lt;interval qualifier&gt;</code> can be a single field or in the field-to-field form:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="o">&lt;</span><span class="n">interval</span> <span class="n">qualifier</span><span class="o">&gt;</span> <span class="p">::</span><span class="o">=</span> <span class="o">&lt;</span><span class="k">start</span> <span class="n">field</span><span class="o">&gt;</span> <span class="k">TO</span> <span class="o">&lt;</span><span class="k">end</span> <span class="n">field</span><span class="o">&gt;</span> <span class="o">|</span> <span class="o">&lt;</span><span class="n">single</span> <span class="n">field</span><span class="o">&gt;</span>
</code></pre></div></div>
<p>The field name is case-insensitive, and can be one of <code class="language-plaintext highlighter-rouge">YEAR</code>, <code class="language-plaintext highlighter-rouge">MONTH</code>, <code class="language-plaintext highlighter-rouge">DAY</code>, <code class="language-plaintext highlighter-rouge">HOUR</code>, <code class="language-plaintext highlighter-rouge">MINUTE</code> and <code class="language-plaintext highlighter-rouge">SECOND</code>.</p>
<p>An interval literal can have either year-month or day-time interval type. The interval sub-type defines format of <code class="language-plaintext highlighter-rouge">&lt;interval string&gt;</code>:</p>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="o">&lt;</span><span class="n">interval</span> <span class="n">string</span><span class="o">&gt;</span> <span class="p">::</span><span class="o">=</span> <span class="o">&lt;</span><span class="n">quote</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">sign</span><span class="o">&gt;</span> <span class="p">]</span> <span class="err">{</span> <span class="o">&lt;</span><span class="nb">year</span><span class="o">-</span><span class="k">month</span> <span class="n">literal</span><span class="o">&gt;</span> <span class="o">|</span> <span class="o">&lt;</span><span class="k">day</span><span class="o">-</span><span class="nb">time</span> <span class="n">literal</span><span class="o">&gt;</span> <span class="err">}</span> <span class="o">&lt;</span><span class="n">quote</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="nb">year</span><span class="o">-</span><span class="k">month</span> <span class="n">literal</span><span class="o">&gt;</span> <span class="p">::</span><span class="o">=</span> <span class="o">&lt;</span><span class="n">years</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">minus</span> <span class="n">sign</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">months</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">]</span> <span class="o">|</span> <span class="o">&lt;</span><span class="n">months</span> <span class="n">value</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="k">day</span><span class="o">-</span><span class="nb">time</span> <span class="n">literal</span><span class="o">&gt;</span> <span class="p">::</span><span class="o">=</span> <span class="o">&lt;</span><span class="k">day</span><span class="o">-</span><span class="nb">time</span> <span class="n">interval</span><span class="o">&gt;</span> <span class="o">|</span> <span class="o">&lt;</span><span class="nb">time</span> <span class="n">interval</span><span class="o">&gt;</span>
<span class="o">&lt;</span><span class="k">day</span><span class="o">-</span><span class="nb">time</span> <span class="n">interval</span><span class="o">&gt;</span> <span class="p">::</span><span class="o">=</span> <span class="o">&lt;</span><span class="n">days</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="k">space</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">hours</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">colon</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">minutes</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">colon</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">seconds</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">]</span> <span class="p">]</span> <span class="p">]</span>
<span class="o">&lt;</span><span class="nb">time</span> <span class="n">interval</span><span class="o">&gt;</span> <span class="p">::</span><span class="o">=</span> <span class="o">&lt;</span><span class="n">hours</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">colon</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">minutes</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">colon</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">seconds</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">]</span> <span class="p">]</span>
<span class="o">|</span> <span class="o">&lt;</span><span class="n">minutes</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">[</span> <span class="o">&lt;</span><span class="n">colon</span><span class="o">&gt;</span> <span class="o">&lt;</span><span class="n">seconds</span> <span class="n">value</span><span class="o">&gt;</span> <span class="p">]</span>
<span class="o">|</span> <span class="o">&lt;</span><span class="n">seconds</span> <span class="n">value</span><span class="o">&gt;</span>
</code></pre></div></div>
<p>Supported year-month interval literals and theirs formats:</p>
<table>
<thead>
<tr>
<th><code class="language-plaintext highlighter-rouge">&lt;interval qualifier&gt;</code></th>
<th>Interval string pattern</th>
<th>An instance of the literal</th>
</tr>
</thead>
<tbody>
<tr>
<td>YEAR</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]y'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL -'2021' YEAR</code></td>
</tr>
<tr>
<td>YEAR TO MONTH</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]y-m'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '-2021-07' YEAR TO MONTH</code></td>
</tr>
<tr>
<td>MONTH</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]m'</code></td>
<td><code class="language-plaintext highlighter-rouge">interval '10' month</code></td>
</tr>
</tbody>
</table>
<p>Formats of supported day-time interval literals:</p>
<table>
<thead>
<tr>
<th><code class="language-plaintext highlighter-rouge">&lt;interval qualifier&gt;</code></th>
<th>Interval string pattern</th>
<th>An instance of the literal</th>
</tr>
</thead>
<tbody>
<tr>
<td>DAY</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]d'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL -'100' DAY</code></td>
</tr>
<tr>
<td>DAY TO HOUR</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]d h'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '-100 10' DAY TO HOUR</code></td>
</tr>
<tr>
<td>DAY TO MINUTE</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]d h:m'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '100 10:30' DAY TO MINUTE</code></td>
</tr>
<tr>
<td>DAY TO SECOND</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]d h:m:s.n'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '100 10:30:40.999999' DAY TO SECOND</code></td>
</tr>
<tr>
<td>HOUR</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]h'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '123' HOUR</code></td>
</tr>
<tr>
<td>HOUR TO MINUTE</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]h:m'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL -'-123:10' HOUR TO MINUTE</code></td>
</tr>
<tr>
<td>HOUR TO SECOND</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]h:m:s.n'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '123:10:59' HOUR TO SECOND</code></td>
</tr>
<tr>
<td>MINUTE</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]m'</code></td>
<td><code class="language-plaintext highlighter-rouge">interval '1000' minute</code></td>
</tr>
<tr>
<td>MINUTE TO SECOND</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]m:s.n'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '1000:01.001' MINUTE TO SECOND</code></td>
</tr>
<tr>
<td>SECOND</td>
<td><code class="language-plaintext highlighter-rouge">[+|-]'[+|-]s.n'</code></td>
<td><code class="language-plaintext highlighter-rouge">INTERVAL '1000.000001' SECOND</code></td>
</tr>
</tbody>
</table>
<h4 id="ansi-examples">ANSI Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="n">INTERVAL</span> <span class="s1">'2-3'</span> <span class="nb">YEAR</span> <span class="k">TO</span> <span class="k">MONTH</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="o">|</span><span class="n">col</span> <span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="o">|</span><span class="n">INTERVAL</span> <span class="s1">'2-3'</span> <span class="nb">YEAR</span> <span class="k">TO</span> <span class="k">MONTH</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------------+</span>
<span class="k">SELECT</span> <span class="n">INTERVAL</span> <span class="o">-</span><span class="s1">'20 15:40:32.99899999'</span> <span class="k">DAY</span> <span class="k">TO</span> <span class="k">SECOND</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">--------------------------------------------+</span>
<span class="o">|</span><span class="n">col</span> <span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------+</span>
<span class="o">|</span><span class="n">INTERVAL</span> <span class="s1">'-20 15:40:32.998999'</span> <span class="k">DAY</span> <span class="k">TO</span> <span class="k">SECOND</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------------------------------+</span>
</code></pre></div></div>
<h4 id="multi-units-syntax">Multi-units Syntax</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">INTERVAL</span> <span class="n">interval_value</span> <span class="n">interval_unit</span> <span class="p">[</span> <span class="n">interval_value</span> <span class="n">interval_unit</span> <span class="p">...</span> <span class="p">]</span> <span class="o">|</span>
<span class="n">INTERVAL</span> <span class="s1">'interval_value interval_unit [ interval_value interval_unit ... ]'</span> <span class="o">|</span>
</code></pre></div></div>
<h4 id="multi-units-parameters">Multi-units Parameters</h4>
<ul>
<li>
<p><strong>interval_value</strong></p>
<p><strong>Syntax:</strong></p>
<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>[ + | - ] number_value | '[ + | - ] number_value'
</code></pre></div> </div>
</li>
<li>
<p><strong>interval_unit</strong></p>
<p><strong>Syntax:</strong></p>
<div class="language-plaintext highlighter-rouge"><div class="highlight"><pre class="highlight"><code>YEAR[S] | MONTH[S] | WEEK[S] | DAY[S] | HOUR[S] | MINUTE[S] | SECOND[S] |
MILLISECOND[S] | MICROSECOND[S]
Mix of the YEAR[S] or MONTH[S] interval units with other units is not allowed.
</code></pre></div> </div>
</li>
</ul>
<h4 id="multi-units-examples">Multi-units Examples</h4>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="k">SELECT</span> <span class="n">INTERVAL</span> <span class="mi">3</span> <span class="nb">YEAR</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------+</span>
<span class="o">|</span><span class="mi">3</span> <span class="n">years</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-------+</span>
<span class="k">SELECT</span> <span class="n">INTERVAL</span> <span class="o">-</span><span class="mi">2</span> <span class="n">HOUR</span> <span class="s1">'3'</span> <span class="k">MINUTE</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">--------------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------+</span>
<span class="o">|-</span><span class="mi">1</span> <span class="n">hours</span> <span class="o">-</span><span class="mi">57</span> <span class="n">minutes</span><span class="o">|</span>
<span class="o">+</span><span class="c1">--------------------+</span>
<span class="k">SELECT</span> <span class="n">INTERVAL</span> <span class="s1">'1 YEAR 2 DAYS 3 HOURS'</span><span class="p">;</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="o">|</span><span class="mi">1</span> <span class="n">years</span> <span class="mi">2</span> <span class="n">days</span> <span class="mi">3</span> <span class="n">hours</span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------------------+</span>
<span class="k">SELECT</span> <span class="n">INTERVAL</span> <span class="mi">1</span> <span class="n">YEARS</span> <span class="mi">2</span> <span class="k">MONTH</span> <span class="mi">3</span> <span class="n">WEEK</span> <span class="mi">4</span> <span class="n">DAYS</span> <span class="mi">5</span> <span class="n">HOUR</span> <span class="mi">6</span> <span class="n">MINUTES</span> <span class="mi">7</span> <span class="k">SECOND</span> <span class="mi">8</span>
<span class="n">MILLISECOND</span> <span class="mi">9</span> <span class="n">MICROSECONDS</span> <span class="k">AS</span> <span class="n">col</span><span class="p">;</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span> <span class="n">col</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
<span class="o">|</span><span class="mi">1</span> <span class="n">years</span> <span class="mi">2</span> <span class="n">months</span> <span class="mi">25</span> <span class="n">days</span> <span class="mi">5</span> <span class="n">hours</span> <span class="mi">6</span> <span class="n">minutes</span> <span class="mi">7</span><span class="p">.</span><span class="mi">008009</span> <span class="n">seconds</span><span class="o">|</span>
<span class="o">+</span><span class="c1">-----------------------------------------------------------+</span>
</code></pre></div></div>
</div>
<!-- /container -->
</div>
<script src="js/vendor/jquery-3.5.1.min.js"></script>
<script src="js/vendor/bootstrap.bundle.min.js"></script>
<script src="js/vendor/anchor.min.js"></script>
<script src="js/main.js"></script>
<script type="text/javascript" src="js/vendor/docsearch.min.js"></script>
<script type="text/javascript">
// DocSearch is entirely free and automated. DocSearch is built in two parts:
// 1. a crawler which we run on our own infrastructure every 24 hours. It follows every link
// in your website and extract content from every page it traverses. It then pushes this
// content to an Algolia index.
// 2. a JavaScript snippet to be inserted in your website that will bind this Algolia index
// to your search input and display its results in a dropdown UI. If you want to find more
// details on how works DocSearch, check the docs of DocSearch.
docsearch({
apiKey: 'd62f962a82bc9abb53471cb7b89da35e',
appId: 'RAI69RXRSK',
indexName: 'apache_spark',
inputSelector: '#docsearch-input',
enhancedSearchInput: true,
algoliaOptions: {
'facetFilters': ["version:3.5.3"]
},
debug: false // Set debug to true if you want to inspect the dropdown
});
</script>
<!-- MathJax Section -->
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
TeX: { equationNumbers: { autoNumber: "AMS" } }
});
</script>
<script>
// Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS.
// We could use "//cdn.mathjax...", but that won't support "file://".
(function(d, script) {
script = d.createElement('script');
script.type = 'text/javascript';
script.async = true;
script.onload = function(){
MathJax.Hub.Config({
tex2jax: {
inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ],
displayMath: [ ["$$","$$"], ["\\[", "\\]"] ],
processEscapes: true,
skipTags: ['script', 'noscript', 'style', 'textarea', 'pre']
}
});
};
script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') +
'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' +
'?config=TeX-AMS-MML_HTMLorMML';
d.getElementsByTagName('head')[0].appendChild(script);
}(document));
</script>
</body>
</html>