You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "edgar2020 (via GitHub)" <gi...@apache.org> on 2024/08/14 22:29:24 UTC
[PR] [docs] Batch10 date and time functions (druid)
edgar2020 opened a new pull request, #16900:
URL: https://github.com/apache/druid/pull/16900
### Description
Add examples to the sql-functions.md page
Updates the `CURRENT_TIMESTAMP `, `CURRENT_DATE `, `DATE_TRUNC `, `TIME_CEIL `, `TIME_FLOOR `, `TIME_SHIFT `, `TIME_EXTRACT `, `TIME_PARSE `, and `TIME_FORMAT` functions.
This PR has:
- [X] been self-reviewed.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1717621475
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Review Comment:
For example converts:
`2013-08-01T08:14:37.000Z` to
`1970-01-01T00:00:00.000Z`(EPOCH time). Should it not be `2000-01-01T00:00:00.000Z`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724003804
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
Review Comment:
```suggestion
|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725531389
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
Review Comment:
```suggestion
The following example parses the `FlightDate` STRING column from the `flight-carriers` datasource into a valid timestamp with an offset of `-05:00` hours.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723958796
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724086994
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
I believe time_floor and time_ceil should be consistent with one another, will rewrite both
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724091456
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
Will mimic how you fixed it in this example: https://github.com/apache/druid/pull/16900/files#r1724009889
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1719219058
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Rounds down a timestamp by a given time unit.
+* **Syntax:** `DATE_TRUNC(unit, timestamp_expr)`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example truncates a timestamp from the `__time` column in
Review Comment:
incomplete
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723988038
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
-Rounds down a timestamp by a given time period, optionally from some reference time or timezone.
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds down to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_FLOOR("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_floor"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_floor` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:00:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T08:45:00.000Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FORMAT
-`TIME_FORMAT(<TIMESTAMP>, [<pattern>, [<timezone>]])`
+Formats a timestamp as a string in a provided [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If no pattern is provided, `pattern` defaults to ISO 8601. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+ The following example formats the `__time` column from the `flight-carriers` datasource into a string format and includes an offset of `-05:00`
Review Comment:
```suggestion
The following example formats the `__time` column from the `flight-carriers` datasource into a string format and includes an offset of `-05:00`.
```
"includes an offset of `-05:00`" sounds odd to me. Maybe "offsets its timezone by `-05:00` hours?"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725526654
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. You can specify `origin` to provide a reference timestamp from which to start rounding. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example rounds up the `__time` column from the `taxi-trips` datasource to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`.
Review Comment:
```suggestion
The following example rounds up the `__time` column from the `taxi-trips` datasource to the nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724004414
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
Review Comment:
```suggestion
|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723998616
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Shifts a timestamp by a given number of time units. `period` can be any ISO 8601 period. `step` may be negative. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "techdocsmith (via GitHub)" <gi...@apache.org>.
techdocsmith merged PR #16900:
URL: https://github.com/apache/druid/pull/16900
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724120311
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
This was the change
"Shifts a timestamp by a given number of time units. The `period` parameter can be any ISO 8601 period. T `step` parameter can be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`."
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1720473993
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Review Comment:
Spoke with an engineer, who confirmed that it is indeed a bug, will keep "century" and "millennium" in documentation since they both are part of intended functionality
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725539467
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
Review Comment:
```suggestion
|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. `timezone` is used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725528604
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If no pattern is provided, `pattern` defaults to ISO 8601. Returns NULL if string cannot be parsed. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723955666
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,85 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
Review Comment:
```suggestion
The following example returns the current timestamp.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724015648
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
+|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. Pattern and time zone must be literals.|
Review Comment:
```suggestion
|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. `pattern` and `timezone` must be literals.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725537435
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
Review Comment:
```suggestion
|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If not provided, `pattern` defaults to ISO 8601. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. `timezone` is used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723967570
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Extracts the value of `unit` from the timestamp and returns it as a number. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
Assuming my understanding of this function is correct, we should specify that it returns the value as a number.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724090156
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
I do not like the idea of starting a sentence with code format, will try to re-write it in another way
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725535315
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
Review Comment:
```suggestion
|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`. This function is similar to `EXTRACT` but is more flexible. |
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725537435
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
Review Comment:
```suggestion
|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If not provided, `pattern` defaults to ISO 8601. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. `timezone` is used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "techdocsmith (via GitHub)" <gi...@apache.org>.
techdocsmith commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725826396
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,85 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
Review Comment:
This is returning UTC for me. For example it is 15:16 right now locally and I get: `2024-08-21T22:16:59.342Z`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723998616
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. `step` can be a negative value. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723999717
##########
docs/querying/sql-scalar.md:
##########
@@ -143,11 +143,11 @@ Literal timestamps in the connection time zone can be written using `TIMESTAMP '
simplest way to write literal timestamps in other time zones is to use TIME_PARSE, like
`TIME_PARSE('2000-02-01 00:00:00', NULL, 'America/Los_Angeles')`.
-The best ways to filter based on time are by using ISO8601 intervals, like
+The best ways to filter based on time are by using ISO 8601 intervals, like
Review Comment:
```suggestion
The best way to filter based on time is by using ISO 8601 intervals, like
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724087455
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
-Rounds down a timestamp by a given time period, optionally from some reference time or timezone.
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds down to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
Review Comment:
Changes are consistent with `TIME_CEIL`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724015026
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
Review Comment:
```suggestion
|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. It will be used for strings without a time zone offset. Both `pattern` and `timezone` must be literals. Strings that cannot be parsed as timestamps will return NULL.|
```
Do `pattern` and `timezone` need to be quoted literals similar to `TIME_EXTRACT`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1720479074
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
Found another bug, PR for fix created by engineers :https://github.com/apache/druid/pull/16915
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724092712
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
Review Comment:
Probably should, I will try and make it match with this suggestion: https://github.com/apache/druid/pull/16900/files#r1724009889. I just do not like starting a sentence with code format
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724009889
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
Review Comment:
```suggestion
|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724121588
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
"|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period, and the `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|"
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724121588
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
"Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`."
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724120311
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
This was the change
"Shifts a timestamp by a given number of time units. The `period` parameter can be any ISO 8601 period, and the `step` parameter can be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`."
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725534131
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
```suggestion
|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "techdocsmith (via GitHub)" <gi...@apache.org>.
techdocsmith commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725840568
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,85 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
Review Comment:
what is the "connection's time zone?" I think it might just be UTC
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1717620756
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Review Comment:
It accepts `century` and `millennium ` but I cannot understand its outputs. May be a bug?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1717621475
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Review Comment:
For example both `century` and `millennium` converts:
`2013-08-01T08:14:37.000Z` to
`1970-01-01T00:00:00.000Z`(EPOCH time). Should it not be `2000-01-01T00:00:00.000Z`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723974734
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723986771
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
-Rounds down a timestamp by a given time period, optionally from some reference time or timezone.
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds down to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_FLOOR("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_floor"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_floor` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:00:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T08:45:00.000Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FORMAT
-`TIME_FORMAT(<TIMESTAMP>, [<pattern>, [<timezone>]])`
+Formats a timestamp as a string in a provided [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If no pattern is provided, `pattern` defaults to ISO 8601. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Formats a timestamp as a string in a provided [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If no pattern is provided, `pattern` defaults to ISO 8601. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723990004
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724015026
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
Review Comment:
```suggestion
|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. It will be used for strings without a time zone offset. `pattern` and `timezone` must be literals. Strings that cannot be parsed as timestamps will return NULL.|
```
Do `pattern` and `timezone` need to be quoted literals similar to `TIME_EXTRACT`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724091017
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
Same as above, I do not like starting a sentence with code format, will re-write to mimic how you suggested I changed this: xhttps://github.com/apache/druid/pull/16900/files#r1724009889
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
Same as above, I do not like starting a sentence with code format, will re-write to mimic how you suggested I changed this: https://github.com/apache/druid/pull/16900/files#r1724009889
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725531945
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. The `period` parameter can be any ISO 8601 period. T `step` parameter can be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Shifts a timestamp by a given number of time units. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1719218975
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Review Comment:
Good question.
I'm not sure why 'century' and 'millennium' are supported in Druid while I didn't find they're supported in other DBMSs like SQLServer. From intuition, `2013-08-01T08:14:37.000Z` should be truncated to `2000-01-01T00:00:00.000Z`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723959727
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
Review Comment:
```suggestion
The following example rounds up the `__time` column from the `taxi-trips` datasource to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723988038
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
-Rounds down a timestamp by a given time period, optionally from some reference time or timezone.
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds down to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_FLOOR("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_floor"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_floor` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:00:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T08:45:00.000Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FORMAT
-`TIME_FORMAT(<TIMESTAMP>, [<pattern>, [<timezone>]])`
+Formats a timestamp as a string in a provided [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html). If no pattern is provided, `pattern` defaults to ISO 8601. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+ The following example formats the `__time` column from the `flight-carriers` datasource into a string format and includes an offset of `-05:00`
Review Comment:
```suggestion
The following example formats the `__time` column from the `flight-carriers` datasource into a string format and includes an offset of `-05:00`.
```
"includes an offset of `-05:00`" sounds odd to me. What does it offset by `-05:00`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723986221
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
-Rounds down a timestamp by a given time period, optionally from some reference time or timezone.
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds down to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
Review Comment:
```suggestion
The following example rounds down the `__time` column from the `taxi-trips` datasource to the nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723974734
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example takes the `__time` column from the `taxi-trips` datasource and rounds up to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of some `unit` of the timestamp and returns the value. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. Specify a timestamp, `origin`, to provide a reference time from which to start. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Rounds down a timestamp to a given ISO 8601 time period. Use the `origin` parameter to provide a reference time from which to start rounding. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725526185
##########
docs/querying/sql-functions.md:
##########
@@ -2239,35 +2296,121 @@ Returns a union of Theta sketches.
## TIME_CEIL
-`TIME_CEIL(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds up a timestamp to a given ISO 8601 time period. You can specify `origin` to provide a reference timestamp from which to start rounding. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example rounds up the `__time` column from the `taxi-trips` datasource to nearest 45th minute in reference to the timestamp `2013-08-01 08:0:00`.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_CEIL("__time", 'PT45M', TIMESTAMP '2013-08-01 08:0:00') AS "time_ceiling"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `time_ceiling` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `2013-08-01T08:45:00.000Z` |
+| `2013-08-01T09:13:00.000Z` | `2013-08-01T09:30:00.000Z` |
+</details>
-Rounds up a timestamp by a given time period, optionally from some reference time or timezone.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_EXTRACT
-`TIME_EXTRACT(<TIMESTAMP>, [<unit>, [<timezone>]])`
+Extracts the value of `unit` from the timestamp and returns it as a number. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example extracts the hour from the `__time` column in the `taxi-trips` datasource and offsets its timezone by `-04:00` hours.
+
+```sql
+SELECT
+ "__time" AS "original_timestamp",
+ TIME_EXTRACT("__time", 'hour', '-04:00') AS "extract_hour"
+FROM "taxi-trips"
+LIMIT 2
+```
+
+Returns the following:
+
+| `original_timestamp` | `extract_hour` |
+| -- | -- |
+| `2013-08-01T08:14:37.000Z` | `4` |
+| `2013-08-01T09:13:00.000Z` | `5` |
+
+</details>
-Extracts the value of some unit of the timestamp and returns the number.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_FLOOR
-`TIME_FLOOR(<TIMESTAMP>, <period>, [<origin>, [<timezone>]])`
+Rounds down a timestamp to a given ISO 8601 time period. You can specify `origin` to provide a reference timestamp from which to start rounding. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
```suggestion
Rounds down a timestamp to a given ISO 8601 time period. You can specify `origin` to provide a reference timestamp from which to start rounding. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1717621475
##########
docs/querying/sql-functions.md:
##########
@@ -807,28 +807,71 @@ Returns the cumulative distribution of the current row within the window calcula
## CURRENT_DATE
-`CURRENT_DATE`
+Returns the current date in the connection's time zone.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `CURRENT_DATE`
+* **Function type:** Scalar, date and time
-Returns the current date in the connection's time zone.
+<details><summary>Example</summary>
-## CURRENT_TIMESTAMP
+The following example returns the current date.
-`CURRENT_TIMESTAMP`
+```sql
+SELECT CURRENT_DATE AS "current_date"
+```
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+Returns the following:
+
+| `current_date` |
+| -- |
+| `2024-08-14T00:00:00.000Z `|
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## CURRENT_TIMESTAMP
Returns the current timestamp in the connection's time zone.
-## DATE_TRUNC
-`DATE_TRUNC(<CHARACTER>, <TIMESTAMP>)`
+* **Syntax:** `CURRENT_TIMESTAMP`
+* **Function type:** Scalar, date and time
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+<details><summary>Example</summary>
+
+The following example returns the current timestamp:
+
+```sql
+SELECT CURRENT_TIMESTAMP AS "current_timestamp"
+```
+
+Returns the following:
+
+| `current_timestamp` |
+| -- |
+| `2024-08-14T21:30:13.793Z` |
+
+</details>
+
+[Learn more](sql-scalar.md#date-and-time-functions)
+
+## DATE_TRUNC
Review Comment:
For example both `century` and `millennium` converts:
`2013-08-01T08:14:37.000Z` to
`1970-01-01T00:00:00.000Z`(EPOCH time). Should it not be `2000-01-01T00:00:00.000Z` for both?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1725537435
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. The `period` parameter can be any ISO 8601 period. The `step` parameter can be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. The `unit` and `timezone` parameters must be provided as quoted literals, such as `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
Review Comment:
```suggestion
|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. If provided, `timezone` should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`. `timezone` is used as the time zone for strings that do not include a time zone offset. The `pattern` and `timezone` parameters must be literals. Strings that cannot be parsed as timestamps return NULL.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724005391
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
```suggestion
|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. `period` can be any ISO 8601 period. `step` may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724005391
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
```suggestion
|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. `period` can be any ISO 8601 period. `step` can be a negative value. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.|
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "ektravel (via GitHub)" <gi...@apache.org>.
ektravel commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1723999057
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_SHIFT(timestamp_expr, period, step[, timezone])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example shifts the `__time` column from `taxi-trips` back by 24 hours.
Review Comment:
```suggestion
The following example shifts the `__time` column from the `taxi-trips` datasource back by 24 hours.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724093073
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
+|`TIME_EXTRACT(timestamp_expr, unit[, timezone])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
+|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`, and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
+|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. Pattern and time zone must be literals.|
Review Comment:
Same with this, will rewrite to avoid starting with code format
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724091017
##########
docs/querying/sql-scalar.md:
##########
@@ -163,13 +163,13 @@ overhead.
|`CURRENT_TIMESTAMP`|Current timestamp in the connection's time zone.|
|`CURRENT_DATE`|Current date in the connection's time zone.|
|`DATE_TRUNC(unit, timestamp_expr)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be 'milliseconds', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year', 'decade', 'century', or 'millennium'.|
-|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `CEIL` but is more flexible.|
-|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `FLOOR` but is more flexible.|
-|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO8601 period. Step may be negative. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00".|
-|`TIME_EXTRACT(timestamp_expr[, unit[, timezone]])`|Extracts a time part from `expr`, returning it as a number. Unit can be EPOCH, SECOND, MINUTE, HOUR, DAY (day of month), DOW (day of week), DOY (day of year), WEEK (week of [week year](https://en.wikipedia.org/wiki/ISO_week_date)), MONTH (1 through 12), QUARTER (1 through 4), or YEAR. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". This function is similar to `EXTRACT` but is more flexible. Unit and time zone must be literals, and must be provided quoted, like `TIME_EXTRACT(__time, 'HOUR')` or `TIME_EXTRACT(__time, 'HOUR', 'America/Los_Angeles')`.|
-|`TIME_PARSE(string_expr[, pattern[, timezone]])`|Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as NULL.|
-|`TIME_FORMAT(timestamp_expr[, pattern[, timezone]])`|Formats a timestamp as a string with a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO8601 (e.g. `2000-01-02T03:04:05Z`) if the pattern is not provided. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00". Pattern and time zone must be literals.|
-|`TIME_IN_INTERVAL(timestamp_expr, interval)`|Returns whether a timestamp is contained within a particular interval. The interval must be a literal string containing any ISO8601 interval, such as `'2001-01-01/P1D'` or `'2001-01-01T01:00:00/2001-01-02T01:00:00'`. The start instant of the interval is inclusive and the end instant is exclusive.|
+|`TIME_CEIL(timestamp_expr, period[, origin[, timezone]])`|Rounds up a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_CEIL(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `CEIL` but is more flexible.|
+|`TIME_FLOOR(timestamp_expr, period[, origin[, timezone]])`|Rounds down a timestamp, returning it as a new timestamp. Period can be any ISO 8601 period, like P3M (quarters) or PT12H (half-days). Specify `origin` as a timestamp to set the reference time for rounding. For example, `TIME_FLOOR(__time, 'PT1H', TIMESTAMP '2016-06-27 00:30:00')` measures an hourly period from 00:30-01:30 instead of 00:00-01:00. See [Period granularities](granularities.md) for details on the default starting boundaries. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`. This function is similar to `FLOOR` but is more flexible.|
+|`TIME_SHIFT(timestamp_expr, period, step[, timezone])`|Shifts a timestamp by a period (step times), returning it as a new timestamp. Period can be any ISO 8601 period. Step may be negative. The time zone, if provided, should be a time zone name like `America/Los_Angeles` or offset like `-08:00`.|
Review Comment:
Same as above, I do not like starting a sentence with code format, will re-write
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] [docs] Batch10 date and time functions (druid)
Posted by "edgar2020 (via GitHub)" <gi...@apache.org>.
edgar2020 commented on code in PR #16900:
URL: https://github.com/apache/druid/pull/16900#discussion_r1724120311
##########
docs/querying/sql-functions.md:
##########
@@ -2279,19 +2422,62 @@ Returns whether a timestamp is contained within a particular interval, formatted
## TIME_PARSE
-`TIME_PARSE(<string_expr>, [<pattern>, [<timezone>]])`
+Parses a string into a timestamp using a given [Joda DateTimeFormat pattern](http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html), or ISO 8601 if the pattern is not provided. Returns NULL if string cannot be parsed. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
-**Function type:** [Scalar, date and time](sql-scalar.md#date-and-time-functions)
+* **Syntax:** `TIME_PARSE(string_expr[, pattern[, timezone]])`
+* **Function type:** Scalar, date and time
+
+<details><summary>Example</summary>
+
+The following example parses the `FlightDate` column from the `flight-carriers` datasource, which is stored as a `string`, into a valid timestamp with an offset of `-05:00` hours.
+
+```sql
+SELECT
+ "FlightDate" AS "original_string",
+ TIME_PARSE("FlightDate", 'YYYY-MM-dd', '-05:00') AS "timestamp"
+FROM "flight-carriers"
+LIMIT 1
+```
+
+Returns the following:
+
+| `original_string` | `timestamp` |
+| -- | -- |
+| `2005-11-01` | `2005-11-01T05:00:00.000Z` |
+
+</details>
-Parses a string into a timestamp.
+[Learn more](sql-scalar.md#date-and-time-functions)
## TIME_SHIFT
-`TIME_SHIFT(<TIMESTAMP>, <period>, <step>, [<timezone>])`
+Shifts a timestamp by a given number of time units. Period can be any ISO 8601 period. Step may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`.
Review Comment:
This was the change
"Shifts a timestamp by a given number of time units. The `period` parameter can be any ISO 8601 period, and the `step` parameter may be negative. If provided, `timezone`, should be a time zone name like `America/Los_Angeles` or an offset like `-08:00`."
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org