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 6a826ab add nearestdate function
6a826ab is described below
commit 6a826ab83be76d7f5126216f4ef681eb19e8fffc
Author: Bridget Bevens <[email protected]>
AuthorDate: Tue Apr 9 18:53:31 2019 -0700
add nearestdate function
---
.../035-plugin-configuration-basics.md | 4 +-
.../030-date-time-functions-and-arithmetic.md | 58 +++++++++++++++++++++-
2 files changed, 59 insertions(+), 3 deletions(-)
diff --git a/_docs/connect-a-data-source/035-plugin-configuration-basics.md
b/_docs/connect-a-data-source/035-plugin-configuration-basics.md
index d058867..1712ed0 100644
--- a/_docs/connect-a-data-source/035-plugin-configuration-basics.md
+++ b/_docs/connect-a-data-source/035-plugin-configuration-basics.md
@@ -1,6 +1,6 @@
---
title: "Plugin Configuration Basics"
-date: 2019-04-09
+date: 2019-04-10
parent: "Storage Plugin Configuration"
---
There are several ways you can configure storage plugins. For example, you can
configure storage plugins in the Drill Web UI, using REST API, or through
configuration files. See [Configuring Storage
Plugins]({{site.baseurl}}/docs/configuring-storage-plugins/) for more
information.
@@ -79,7 +79,7 @@ The following table describes the attributes you configure
for storage plugins i
</tr>
<tr>
<td>"formats" . . . "type"</td>
-
<td>"pcap"<br>"pcapng"<br>"text"<br>"parquet"<br>"json"<br>"maprdb"<br>"avro"<br>"image"<br>"sequencefile"<br>"httpd"<br>"[syslog]({{site.baseurl}}/docs/sys-log-format-plugin/)"</td>
+
<td>"pcap"<br>"pcapng"<br>"text"<br>"parquet"<br>"json"<br>"maprdb"<br>"avro"<br>"image"<br>"sequencefile"<br>"httpd"<br>"[syslog]({{site.baseurl}}/docs/syslog-format-plugin/)"</td>
<td>yes</td>
<td>Format type. You can define two formats, csv and psv, as type "Text",
but having different delimiters. </td>
</tr>
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 5ed65c2..e56ab5e 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: 2019-01-15
+date: 2019-04-10
parent: "SQL Functions"
---
@@ -24,11 +24,13 @@ 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
+[NEARESTDATE]({{ site.baseurl
}}/docs/date-time-functions-and-arithmetic/#nearestdate)**
| TIMESTAMP
[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.
+**Supported in Drill 1.16 and later.
## AGE
Returns the interval between two timestamps or subtracts a timestamp from
midnight of the current date.
@@ -677,6 +679,60 @@ SELECT UNIX_TIMESTAMP('2015-05-29 08:18:53.0', 'yyyy-MM-dd
HH:mm:ss.SSS') FROM (
1 row selected (0.171 seconds)
```
+##NEARESTDATE
+Quickly and easily aggregates timestamp data by various units of time.
+
+**Note:** Drill 1.16 and later supports the NEARESTDATE function.
+
+###NEARESTDATE Syntax
+
+NEARESTDATE(*column*, '*interval*' )
+
+
+###NEARESTDATE Usage Notes
+* Use with COUNT and GROUP BY to aggregate timestamp data.
+* *column* is a data source column with timestamp values.
+* *interval* is any of the following units of time:
+ * YEAR
+ * QUARTER
+ * MONTH
+ * WEEK_SUNDAY
+ * WEEK_MONDAY
+ * DAY
+ * HOUR
+ * HALF_HOUR
+ * QUARTER_HOUR
+ * MINUTE
+ * 30SECOND
+ * 15SECOND
+ * SECOND
+
+###NEARESTDATE Examples
+
+The following example uses the NEARESTDATE function to aggregate hire dates by
year:
+
+ SELECT NEARESTDATE(hire_date, 'YEAR' ) AS hireDate, COUNT(*) AS `count`
FROM cp.`employee.json` GROUP BY NEARESTDATE(hire_date, 'YEAR');
+ +-----------------------+-------+
+ | hireDate | count |
+ +-----------------------+-------+
+ | 1994-01-01 00:00:00.0 | 23 |
+ | 1998-01-01 00:00:00.0 | 539 |
+ | 1996-01-01 00:00:00.0 | 503 |
+ | 1995-01-01 00:00:00.0 | 12 |
+ | 1997-01-01 00:00:00.0 | 74 |
+ | 1993-01-01 00:00:00.0 | 4 |
+ +-----------------------+-------+
+
+The following example applies the NEARESTDATE function to a timestamp value
(2019-02-01 07:22:00) and returns the timestamp value for each time unit
indicated:
+
+ SELECT nearestDate( TO_TIMESTAMP('2019-02-01 07:22:00', 'yyyy-MM-dd
HH:mm:ss'), 'YEAR') AS nearest_year, nearestDate( TO_TIMESTAMP('2019-02-01
07:22:00', 'yyyy-MM-dd HH:mm:ss'), 'QUARTER') AS nearest_quarter, nearestDate(
TO_TIMESTAMP('2019-02-15 07:22:00', 'yyyy-MM-dd HH:mm:ss'), 'MONTH') AS
nearest_month, nearestDate( TO_TIMESTAMP('2019-02-15 07:22:00', 'yyyy-MM-dd
HH:mm:ss'), 'DAY') AS nearest_day, nearestDate( TO_TIMESTAMP('2019-02-15
07:22:00', 'yyyy-MM-dd HH:mm:ss'), 'WEEK_SUNDAY' [...]
+
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
+ | nearest_year | nearest_quarter | nearest_month
| nearest_day | nearest_week_sunday | nearest_week_monday |
nearest_hour | nearest_half_hour | nearest_quarter_hour |
nearest_minute | nearest_30second | nearest_15second |
nearest_second |
+
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
+ | 2019-01-01 00:00:00.0 | 2019-01-01 00:00:00.0 | 2019-02-01 00:00:00.0
| 2019-02-15 00:00:00.0 | 2019-02-10 00:00:00.0 | 2019-02-11 00:00:00.0 |
2019-02-15 07:00:00.0 | 2019-02-15 07:30:00.0 | 2019-02-15 07:45:00.0 |
2019-02-15 07:22:00.0 | 2019-02-15 07:22:00.0 | 2019-02-15 07:22:15.0 |
2019-02-15 07:22:31.0 |
+
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+
+
+
##TIMESTAMPADD
Adds an interval of time, in the given time units, to a datetime expression.
