You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@drill.apache.org by cg...@apache.org on 2022/11/04 15:11:57 UTC
[drill-site] branch master updated: Added DATE_TRUNC to documentation
This is an automated email from the ASF dual-hosted git repository.
cgivre pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill-site.git
The following commit(s) were added to refs/heads/master by this push:
new d0c138728 Added DATE_TRUNC to documentation
d0c138728 is described below
commit d0c13872865d050bf9c87337a72586e03efff96a
Author: Charles S. Givre <cg...@apache.org>
AuthorDate: Fri Nov 4 11:11:52 2022 -0400
Added DATE_TRUNC to documentation
---
.../030-date-time-functions-and-arithmetic.md | 64 ++++++++++++++++++++++
1 file changed, 64 insertions(+)
diff --git a/_docs/en/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md b/_docs/en/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
index 728bd2378..bab5113ba 100644
--- a/_docs/en/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
+++ b/_docs/en/sql-reference/sql-functions/030-date-time-functions-and-arithmetic.md
@@ -19,6 +19,7 @@ This section covers the Drill [time zone limitation]({{site.baseurl}}/docs/data-
| [DATE_DIFF]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#date_diff) | DATE, TIMESTAMP |
| [DATE_PART]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#date_part) | DOUBLE |
| [DATE_SUB]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#date_sub) | DATE, TIMESTAMP |
+| [DATE_TRUNC]({{ site.baseurl }}/docs/date-time-functions-and-arithmetic/#date_trunc) | DATE, TIMESTAMP |
| [DAY]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#day) | BIGINT |
| [HOUR]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#hour) | BIGINT |
| [ISDATE]({{site.baseurl}}/docs/date-time-functions-and-arithmetic/#isdate) | BOOLEAN |
@@ -464,6 +465,69 @@ The `employee.json` file, which Drill includes in the installation, lists the hi
|------------------------|
2 rows selected (0.161 seconds)
+## DATE_TRUNC
+Rounds or truncates a date or timestamp to the interval you need. When used to aggregate data, it allows you to find time-based trends like daily purchases or messages per second.
+
+The `DATE_TRUNC` function can return either a `date`, `timestamp`, `time` or `interval`.
+
+### DATE_TRUNC Syntax
+
+`DATE_TRUNC(time_increment, expr)`
+`DATE_TRUNC(time_increment, keyword expr)`
+`DATE_TRUNC(time_increment, interval interval_expr TO time_increment)`
+
+*time_increment* is the time increment to which you would like to round the datetime. It must be one of the options listed below and enclosed by single quotes.
+*keyword* is the word `date`, `time`, or `timestamp`. This corresponds to the desired return type.
+*interval* is the word `interval`
+*interval_expr* is an interval expression
+*column* is date, time, or timestamp data in the data source.
+*expr* is an time expression, such as the name of a data source column containing temporal data, or a string.
+
+
+* Second
+* Minute
+* Hour
+* Day
+* Month
+* Quarter
+* Year
+* Decade
+* Century
+* Millennium
+
+### DATE_TRUNC Examples
+`DATE_TRUNC` returning dates truncated to specific increments:
+
+ SELECT date_trunc('DECADE',
+ date '1983-05-18') AS decade,
+ date_trunc('YEAR', date '1983-05-18') AS `year`,
+ date_trunc('QUARTER', date '1983-05-18') AS quarter, date_trunc('WEEK', date '1983-05-18') AS week;
+ +------------+------------+------------+------------+
+ | decade | year | quarter | week |
+ +------------+------------+------------+------------+
+ | 1980-01-01 | 1983-01-01 | 1983-04-01 | 1983-05-16 |
+ +------------+------------+------------+------------+
+
+`DATE_TRUNC` returning timestamps truncated to specific increments:
+
+ SELECT date_trunc('HOUR', timestamp '1983-05-18 10:14:00') AS HOUR;
+ +-----------------------+
+ | HOUR |
+ +-----------------------+
+ | 1983-05-18 10:00:00.0 |
+ +-----------------------+
+
+`DATE_TRUNC` example using an interval.
+
+ SELECT date_trunc('YEAR', interval '217-7' year(3) to month) as `year`,
+ date_trunc('DECADE', interval '217-7' year(3) to month) AS `decade`;
+ +--------------------+--------------------+
+ | year | decade |
+ +--------------------+--------------------+
+ | 217 years 0 months | 210 years 0 months |
+ +--------------------+--------------------+
+
+
## DAY
Returns the day portion of a date/time. Also accepts a string as input.