Vinod KC created SPARK-54630:
--------------------------------
Summary: Add date_bucket function for arbitrary interval bucketing
of dates
Key: SPARK-54630
URL: https://issues.apache.org/jira/browse/SPARK-54630
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 4.0.0, 3.5.0
Reporter: Vinod KC
Add {{date_bucket}} function to bucket DATE values into arbitrary
fixed-duration intervals aligned to the Unix epoch (1970-01-01)
Currently, Spark provides:
* {*}{{date_trunc()}}{*}: Truncates to calendar-based units (year, quarter,
month, week, day) but only supports predefined units
* {*}{{trunc()}}{*}: Similar to {{date_trunc()}} for DATE type
* {*}{{window()}}{*}: For TIMESTAMP bucketing with tumbling/sliding windows,
returns a struct with start/end times
However, there is no function to bucket dates by arbitrary fixed durations like:
* 7-day buckets (weekly reporting aligned to epoch, not calendar weeks)
* 14-day buckets (bi-weekly cycles)
* Custom N-day intervals for business cycles
This is a common requirement for:
* {*}Time-series analytics{*}: Grouping events by custom time periods
* {*}Reporting{*}: Creating consistent bucketing across different date ranges
* {*}Data partitioning{*}: Organizing data by fixed-duration intervals
{code:java}
-- 7-day buckets
SELECT date_bucket(INTERVAL '7' DAY, DATE'2025-01-15');
-- Result: 2025-01-09
-- 2 WEEKS = 14 days
SELECT date_bucket(INTERVAL '2' WEEK, DATE'2025-01-15');
-- Result: 2025-01-08
-- Group orders by 7-day buckets
SELECT date_bucket(INTERVAL '7' DAY, order_date) AS week_bucket,
COUNT(*) AS order_count,
SUM(amount) AS total_sales
FROM orders
WHERE order_date >= DATE'2025-01-01'
GROUP BY week_bucket
ORDER BY week_bucket;
-- Demonstrate date_bucket with Multiple Input Dates
SELECT stack(8,
DATE'2025-01-01', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-01'),
DATE'2025-01-05', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-05'),
DATE'2025-01-08', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-08'),
DATE'2025-01-15', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-15'),
DATE'2025-01-14', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-14'),
DATE'2025-01-13', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-13'),
DATE'2025-01-22', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-22'),
DATE'2025-01-29', date_bucket(INTERVAL '1' WEEK, DATE'2025-01-29')
) AS (input_date, bucket_start)
2025-01-01 2024-12-26
2025-01-05 2025-01-02
2025-01-08 2025-01-02
2025-01-15 2025-01-09
2025-01-14 2025-01-09
2025-01-13 2025-01-09
2025-01-22 2025-01-16
2025-01-29 2025-01-23{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
