You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2022/08/02 23:59:16 UTC
[GitHub] [arrow-datafusion] stuartcarnie opened a new issue, #3015: Proposal: Add date_bin function
stuartcarnie opened a new issue, #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015
**Introduction**
This proposal suggests adding a new scalar function, `date_bin`, to DataFusion, for transforming timestamp values to arbitrary intervals for the purpose of grouping and aggregating time-series data.
**Motivation**
Time-series data is typically analysed in aggregate where one axis is almost always time. DataFusion's [`date_trunc`](https://github.com/apache/arrow-datafusion/blob/cd3164918b0415b072a3109f3ccf654da7518ec1/datafusion/physical-expr/src/datetime_expressions.rs#L237) is modelled after the PostgreSQL [`date_trunc`](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC) function, which allows truncating a timestamp column for the purpose of grouping, however, the intervals are limited to an enumeration, such as second, minute, hour, day, week, month, quarter and year. To address this limitation, PostgreSQL 14 introduced the [`date_bin`](https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-BIN) function, which can bin or adjust the input timestamp to arbitrary intervals.
**Describe the solution you'd like**
Add a new function, `date_bin` to DataFusion.
**Name**: `date_bin(stride, source, origin)`
Per the PostgreSQL 14 docs
> The function date_bin βbinsβ the input timestamp into the specified interval (the stride) aligned with a specified origin.
**Required arguments**
| Name | Type | Description |
| :---- | :---- | :---- |
| `stride` | INTERVAL | time interval specifying the width of each bin. |
| `source` | TIMESTAMP | The timestamp to transform. |
| `origin` | TIMESTAMP | Acts as an offset applied to the transformed timestamp |
----
**Example Usage**
Demonstrate `date_bin`[^1]:
```sql
SELECT
DATE_BIN(INTERVAL '15' minute, CAST(time as TIMESTAMP), TIMESTAMP '2001-01-01') AS time,
val
FROM (
VALUES
('2021-06-10 17:05:00Z', 0.5),
('2021-06-10 17:19:10Z', 0.3)
) as t (time, val);
```
[^1]: DataFusion does not support "typed string" literals in a `VALUES` statement, like `VALUES ((TIMESTAMP '2021-06-10 17:05:00Z'))`
producing the following output:
```
+---------------------+-----+
| time | val |
+---------------------+-----+
| 2021-06-10 17:00:00 | 0.5 |
| 2021-06-10 17:15:00 | 0.3 |
+---------------------+-----+
2 rows in set. Query took 0.002 seconds.
```
----
**Example Usage: time offset for origin**
```sql
SELECT
DATE_BIN(INTERVAL '15' minute, CAST(time as TIMESTAMP), TIMESTAMP '2001-01-01 00:02:30') AS time,
val
FROM (
VALUES
('2021-06-10 17:05:00Z', 0.5),
('2021-06-10 17:19:10Z', 0.3)
) as t (time, val);
```
producing the following output:
```
+---------------------+-----+
| time | val |
+---------------------+-----+
| 2021-06-10 17:02:30 | 0.5 |
| 2021-06-10 17:17:30 | 0.3 |
+---------------------+-----+
2 rows in set. Query took 0.002 seconds.
```
**Describe alternatives you've considered**
`date_trunc`, as mentioned, provides limited support for binning timestamps, but there is no alternative but to provide a native function.
--
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: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb closed issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
alamb closed issue #3015: Proposal: Add `date_bin` function
URL: https://github.com/apache/arrow-datafusion/issues/3015
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1203854597
@ovr , @andygrove any concerns or thoughts?
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] avantgardnerio commented on issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
avantgardnerio commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1203920683
> I think following postgres is a good idea π thanks @stuartcarnie
>
> cc @avantgardnerio
Love it!
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] Dandandan commented on issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
Dandandan commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1204373287
Sounds like a great feature :+1:
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] avantgardnerio commented on issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
avantgardnerio commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1205467234
> optional origin argument
This definitely seems like a worthwhile extension, but it also makes sense to not expand the scope of this PR.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] alamb commented on issue #3015: Proposal: Add date_bin function
Posted by GitBox <gi...@apache.org>.
alamb commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1203854180
I think following postgres is a good idea π thanks @stuartcarnie
cc @avantgardnerio
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] stuartcarnie commented on issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
stuartcarnie commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1204807984
@jacobmarble these are great suggestions.
* PostgreSQL: The `date_bin` implementation accepts a string or an `INTERVAL` type for the first argument. If it is a string, it is parsed as an `INTERVAL`, which saves the user from having to specify the `INTERVAL` prefix.
* β
Will do that as part of this issue, as it remains compatible with PostgreSQL.
* PostgreSQL: Supports several formats for `INTERVAL` input expressions, including ISO 8601 intervals, [as noted in the documentation](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT)
* Fractional seconds support `PT0.nnnnnnS` for microsecond precision. We would extend that to 9 places for nanosecond precision.
* DataFusion: no support for ISO 8601 interval types, so we can't use shorthand syntax like `PT5S` for 5 seconds.
* ποΈ Will need to write a separate issue to add additional `INTERVAL` syntax.
* DataFusion: We can also support the other data types for the `origin` argument
* ππ» I'd like to propose we recommend these extensions in a follow up issue
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow-datafusion] jacobmarble commented on issue #3015: Proposal: Add `date_bin` function
Posted by GitBox <gi...@apache.org>.
jacobmarble commented on issue #3015:
URL: https://github.com/apache/arrow-datafusion/issues/3015#issuecomment-1204739188
I don't know how far DataFusion likes to deviate from PostgreSQL-flavored SQL, but here's an idea for this feature.
### Suggestion 1
Make the default value of the `origin` argument `1970-01-01 00:00:00 UTC`.
It seems there is no default value specified by PostgreSQL. Certainly there exists a need for `origin` values like `XXXX-XX-XX 00:02:30 UTC`, but the far more common uses of `date_bin` are certainly with `stride` values like 1s, 10s, 30s, 1m, 5m, 10m, 15m, 20m, 30m, 1h, 2h, 3h, 6h, etc, with the reasonable expectation that the result of `date_bin` align with a top-of-the-hour origin.
The result is that 99% of queries written with this function will be shorter and less error prone.
Some proposed, equivalent statements:
```sql
-- as specified by PostgreSQL - origin at Unix epoch
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', timestamp '1970-01-01 00:00:00 UTC');
-- 2022-08-03 14:45:00
-- proposed
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST');
```
### Suggestion 2
Allow `origin` values with type `timestamp`, `date`, `time` or `interval`. Again, PostgreSQL doesn't have any prior art here.
The result enables better readability.
Some proposed, equivalent statements:
```sql
-- as specified by PostgreSQL - origin shifts bins forward 5 minutes
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', timestamp '1970-01-01 00:05:00 UTC');
-- 2022-08-03 14:35:00
-- proposed
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', time '00:05:00');
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', interval '5 minutes');
```
```sql
-- as specified by PostgreSQL - origin shifts bins back 5 minutes
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', timestamp '1969-12-31 23:55:00 UTC');
-- 2022-08-03 14:40:00
-- proposed
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', - time '00:05:00');
select date_bin('15 minutes', timestamp '2022-08-03 14:49:50 PST', interval '- 5 minutes');
```
```sql
-- as specified by PostgreSQL - origin shifts bins forward one day
select date_bin('7 days', timestamp '2022-08-03 14:49:50 PST', timestamp '1970-01-02 00:00:00 UTC');
-- 2022-07-29 00:00:00
-- proposed
select date_bin('7 days', timestamp '2022-08-03 14:49:50 PST', date '1970-01-02');
select date_bin('7 days', timestamp '2022-08-03 14:49:50 PST', interval '1 day');
```
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org