You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by br...@apache.org on 2019/01/03 19:17:05 UTC
[drill] branch gh-pages updated: edit Drill datetime/add functions
and add note about support starting in Drill 1.15
This is an automated email from the ASF dual-hosted git repository.
bridgetb pushed a commit to branch gh-pages
in repository https://gitbox.apache.org/repos/asf/drill.git
The following commit(s) were added to refs/heads/gh-pages by this push:
new e1ae5ab edit Drill datetime/add functions and add note about support starting in Drill 1.15
e1ae5ab is described below
commit e1ae5ab0aab0ad3581886ac12055d93a80fa5245
Author: Bridget Bevens <bb...@maprtech.com>
AuthorDate: Thu Jan 3 11:16:21 2019 -0800
edit Drill datetime/add functions and add note about support starting in Drill 1.15
---
.../030-date-time-functions-and-arithmetic.md | 28 +++++++++++++---------
1 file changed, 17 insertions(+), 11 deletions(-)
diff --git a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
index a60f6ab..afa0ad5 100644
--- a/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
+++ b/_docs/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
@@ -1,6 +1,6 @@
---
title: "Date/Time Functions and Arithmetic"
-date: 2018-12-29
+date: 2019-01-03
parent: "SQL Functions"
---
@@ -23,9 +23,11 @@ This section covers the Drill [time zone limitation]({{site.baseurl}}/docs/data-
[NOW]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions) | TIMESTAMP
[TIMEOFDAY]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#other-date-and-time-functions) | VARCHAR
[UNIX_TIMESTAMP]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#unix_timestamp) | BIGINT
-[TIMESTAMPADD]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#timestampadd) | Inferred based on unit of time
-[TIMESTAMPDIFF]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#timestampdiff) | Inferred based on unit of time |
-|
+[TIMESTAMPADD]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#timestampadd)* | Inferred based on unit of time
+[TIMESTAMPDIFF]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#timestampdiff)* | Inferred based on unit of time |
+|
+
+* Supported in Drill 1.15 and later.
## AGE
Returns the interval between two timestamps or subtracts a timestamp from midnight of the current date.
@@ -567,7 +569,9 @@ SELECT UNIX_TIMESTAMP('2015-05-29 08:18:53.0', 'yyyy-MM-dd HH:mm:ss.SSS') FROM (
```
##TIMESTAMPADD
-Adds an interval of time, in the given time units, to a datetime expression.
+Adds an interval of time, in the given time units, to a datetime expression.
+
+**Note:** Drill 1.15 and later supports the TIMESTAMPADD function.
###TIMESTAMPADD Syntax
TIMESTAMPADD(*time\_unit, interval, datetime\_expression*)
@@ -600,14 +604,14 @@ Add three years to a date literal:
Add a quarter (3 months) to the date values in the first column of the dates.csv file:
- SELECT TIMESTAMPADD(QUARTER, 1, COLUMNS[0]) q1 FROM dfs.`/quarter/dates.csv`;
+ SELECT TIMESTAMPADD(QUARTER, 1, CAST(COLUMNS[0] as date)) q1 FROM dfs.`/quarter/dates.csv`;
+------------------------+
| q1 |
+------------------------+
| 2018-04-01 00:00:00.0 |
| 2017-05-02 00:00:00.0 |
| 2000-08-06 00:00:00.0 |
- +------------------------+
+ +------------------------+
Dates in column[0] before applying the TIMESTAMPADD function:
@@ -622,7 +626,9 @@ Dates in column[0] before applying the TIMESTAMPADD function:
##TIMESTAMPDIFF
-Calculates an interval of time, in the given time units, by subtracting *datetime\_expression1* from *datetime\_expression2* (*datetime\_expression2* − *datetime\_expression1*).
+Calculates an interval of time, in the given time units, by subtracting *datetime\_expression1* from *datetime\_expression2* (*datetime\_expression2* − *datetime\_expression1*).
+
+**Note:** Drill 1.15 and later supports the TIMESTAMPDIFF function.
###TIMESTAMPDIFF Syntax
TIMESTAMPDIFF(*time\_unit, datetime\_expression1, datetime\_expression2*)
@@ -656,7 +662,7 @@ Subtracts the date literal '1982-05-06' from the date literal '2018-12-26' and r
Subtracts the date literal '1970-01-15' from the dates in the first column of the dates.csv file and returns the difference in seconds:
- SELECT TIMESTAMPDIFF(SECOND, DATE '1970-01-15', columns[0]) a from dfs.`/quarter/dates.csv`;
+ SELECT TIMESTAMPDIFF(SECOND, DATE '1970-01-15', CAST(COLUMNS[0] as date)) a FROM dfs.`/quarter/dates.csv`
+-------------+
| a |
+-------------+
@@ -667,11 +673,11 @@ Subtracts the date literal '1970-01-15' from the dates in the first column of th
Subtracts the date in the third column from the date in the first column (columns[0]-columns[2]) of the dates.csv file and returns the difference in seconds:
- SELECT TIMESTAMPDIFF(SECOND, columns[2], columns[0]) a from dfs.`/quarter/dates.csv`;
+ SELECT TIMESTAMPDIFF(SECOND, CAST(COLUMNS[2] as date), CAST(COLUMNS[0] as date)) a from dfs.`/home/bee/pets.csv`;
+------------+
| a |
+------------+
| 0 |
| 0 |
| -92016000 |
- +------------+
\ No newline at end of file
+ +------------+