This is an automated email from the ASF dual-hosted git repository.
maxgekk pushed a commit to branch branch-3.2
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.2 by this push:
new 8dbcbeb [SPARK-36468][SQL][DOCS] Update docs about ANSI interval
literals
8dbcbeb is described below
commit 8dbcbebc3604f01b865215f2d02eb823cad582ec
Author: Max Gekk <[email protected]>
AuthorDate: Wed Aug 11 13:38:39 2021 +0300
[SPARK-36468][SQL][DOCS] Update docs about ANSI interval literals
### What changes were proposed in this pull request?
In the PR, I propose to update the doc page
https://spark.apache.org/docs/latest/sql-ref-literals.html#interval-literal,
and describe formats of ANSI interval literals.
<img width="1032" alt="Screenshot 2021-08-11 at 10 31 36"
src="https://user-images.githubusercontent.com/1580697/128988454-7a6ac435-409b-4961-9b79-ebecfb141d5e.png";>
<img width="1030" alt="Screenshot 2021-08-10 at 20 58 04"
src="https://user-images.githubusercontent.com/1580697/128912018-a4ea3ee5-f252-49c7-a90e-5beaf7ac868f.png";>
### Why are the changes needed?
To improve UX with Spark SQL, and inform users about recently added ANSI
interval literals.
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
Manually checked the generated docs:
```
$ SKIP_API=1 SKIP_RDOC=1 SKIP_PYTHONDOC=1 SKIP_SCALADOC=1 bundle exec
jekyll build
```
Closes #33693 from MaxGekk/doc-ansi-interval-literals.
Authored-by: Max Gekk <[email protected]>
Signed-off-by: Max Gekk <[email protected]>
(cherry picked from commit bbf988bd73b00d18dd1d443f225b3915a2c4433f)
Signed-off-by: Max Gekk <[email protected]>
---
docs/sql-ref-literals.md | 91 +++++++++++++++++++++++++++++++++++++-----------
1 file changed, 70 insertions(+), 21 deletions(-)
diff --git a/docs/sql-ref-literals.md b/docs/sql-ref-literals.md
index b23c63a..355ac69 100644
--- a/docs/sql-ref-literals.md
+++ b/docs/sql-ref-literals.md
@@ -439,13 +439,78 @@ SELECT TIMESTAMP '1997-01' AS col;
An interval literal is used to specify a fixed period of time.
+#### ANSI style
+
+The ANSI SQL standard defines interval literals in the form:
+```sql
+INTERVAL [ <sign> ] <interval string> <interval qualifier>
+```
+where `<interval qualifier>` can be a single field or in the field-to-field
form:
+```sql
+<interval qualifier> ::= <start field> TO <end field> | <single field>
+```
+The field name is case-insensitive, and can be one of `YEAR`, `MONTH`, `DAY`,
`HOUR`, `MINUTE` and `SECOND`.
+
+An interval literal can have either year-month or day-time interval type. The
interval sub-type defines format of `<interval string>`:
+```sql
+<interval string> ::= <quote> [ <sign> ] { <year-month literal> | <day-time
literal> } <quote>
+<year-month literal> ::= <years value> [ <minus sign> <months value> ] |
<months value>
+<day-time literal> ::= <day-time interval> | <time interval>
+<day-time interval> ::= <days value> [ <space> <hours value> [ <colon>
<minutes value> [ <colon> <seconds value> ] ] ]
+<time interval> ::= <hours value> [ <colon> <minutes value> [ <colon> <seconds
value> ] ]
+ | <minutes value> [ <colon> <seconds value> ]
+ | <seconds value>
+```
+
+Supported year-month interval literals and theirs formats:
+
+|`<interval qualifier>`|Interval string pattern|An instance of the literal|
+|---------|-------|------------|
+|YEAR|`[+|-]'[+|-]y'`|`INTERVAL -'2021' YEAR`|
+|YEAR TO MONTH|`[+|-]'[+|-]y-m'`|`INTERVAL '-2021-07' YEAR TO MONTH`|
+|MONTH|`[+|-]'[+|-]m'`|`interval '10' month`|
+
+Formats of supported day-time interval literals:
+
+|`<interval qualifier>`|Interval string pattern|An instance of the literal|
+|---------|----|-------------------|
+|DAY|`[+|-]'[+|-]d'`|`INTERVAL -'100' DAY`|
+|DAY TO HOUR|`[+|-]'[+|-]d h'`|`INTERVAL '-100 10' DAY TO HOUR`|
+|DAY TO MINUTE|`[+|-]'[+|-]d h:m'`|`INTERVAL '100 10:30' DAY TO MINUTE`|
+|DAY TO SECOND|`[+|-]'[+|-]d h:m:s.n'`|`INTERVAL '100 10:30:40.999999' DAY TO
SECOND`|
+|HOUR|`[+|-]'[+|-]h'`|`INTERVAL '123' HOUR`|
+|HOUR TO MINUTE|`[+|-]'[+|-]h:m'`|`INTERVAL -'-123:10' HOUR TO MINUTE`|
+|HOUR TO SECOND|`[+|-]'[+|-]h:m:s.n'`|`INTERVAL '123:10:59' HOUR TO SECOND`|
+|MINUTE|`[+|-]'[+|-]m'`|`interval '1000' minute`|
+|MINUTE TO SECOND|`[+|-]'[+|-]m:s.n'`|`INTERVAL '1000:01.001' MINUTE TO
SECOND`|
+|SECOND|`[+|-]'[+|-]s.n'`|`INTERVAL '1000.000001' SECOND`|
+
+##### Examples
+
+```sql
+SELECT INTERVAL '2-3' YEAR TO MONTH AS col;
++----------------------------+
+|col |
++----------------------------+
+|INTERVAL '2-3' YEAR TO MONTH|
++----------------------------+
+
+SELECT INTERVAL -'20 15:40:32.99899999' DAY TO SECOND AS col;
++--------------------------------------------+
+|col |
++--------------------------------------------+
+|INTERVAL '-20 15:40:32.998999' DAY TO SECOND|
++--------------------------------------------+
+```
+
+#### Multi-units style
+
```sql
INTERVAL interval_value interval_unit [ interval_value interval_unit ... ] |
INTERVAL 'interval_value interval_unit [ interval_value interval_unit ... ]' |
-INTERVAL interval_string_value interval_unit TO interval_unit
```
-#### Parameters
+##### Parameters
* **interval_value**
@@ -453,10 +518,6 @@ INTERVAL interval_string_value interval_unit TO
interval_unit
[ + | - ] number_value | '[ + | - ] number_value'
-* **interval_string_value**
-
- year-month/day-time interval string.
-
* **interval_unit**
**Syntax:**
@@ -464,7 +525,9 @@ INTERVAL interval_string_value interval_unit TO
interval_unit
YEAR[S] | MONTH[S] | WEEK[S] | DAY[S] | HOUR[S] | MINUTE[S] | SECOND[S] |
MILLISECOND[S] | MICROSECOND[S]
-#### Examples
+ Mix of the YEAR[S] or MONTH[S] interval units with other units is not
allowed.
+
+##### Examples
```sql
SELECT INTERVAL 3 YEAR AS col;
@@ -495,18 +558,4 @@ SELECT INTERVAL 1 YEARS 2 MONTH 3 WEEK 4 DAYS 5 HOUR 6
MINUTES 7 SECOND 8
+-----------------------------------------------------------+
|1 years 2 months 25 days 5 hours 6 minutes 7.008009 seconds|
+-----------------------------------------------------------+
-
-SELECT INTERVAL '2-3' YEAR TO MONTH AS col;
-+----------------+
-| col|
-+----------------+
-|2 years 3 months|
-+----------------+
-
-SELECT INTERVAL '20 15:40:32.99899999' DAY TO SECOND AS col;
-+---------------------------------------------+
-| col|
-+---------------------------------------------+
-|20 days 15 hours 40 minutes 32.998999 seconds|
-+---------------------------------------------+
```
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
![https://user-images.githubusercontent.com/1580697/128988454-7a6ac435-409b-4961-9b79-ebecfb141d5e.png"](https://user-images.githubusercontent.com/1580697/128988454-7a6ac435-409b-4961-9b79-ebecfb141d5e.png"){kind=link}
![https://user-images.githubusercontent.com/1580697/128912018-a4ea3ee5-f252-49c7-a90e-5beaf7ac868f.png"](https://user-images.githubusercontent.com/1580697/128912018-a4ea3ee5-f252-49c7-a90e-5beaf7ac868f.png"){kind=link}