[
https://issues.apache.org/jira/browse/SPARK-54507?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-54507:
-----------------------------------
Labels: pull-request-available (was: )
> Add time_bucket function for TIME type bucketing
> ------------------------------------------------
>
> Key: SPARK-54507
> URL: https://issues.apache.org/jira/browse/SPARK-54507
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.1.0
> Reporter: Vinod KC
> Priority: Major
> Labels: pull-request-available
>
> Add a new `time_bucket()` SQL function that buckets TIME values into
> fixed-width intervals, returning the start time of each bucket. This enables
> histogram generation and time-of-day pattern analysis for TIME columns.
> Currently, Spark SQL users cannot easily group TIME values by intervals
> (e.g., 15-minute or 1-hour buckets) without complex manual calculations.
> Common use cases that require this function:
> - Retail: Analyze customer traffic by 30-minute slots to optimize staffing
> - Healthcare: Group appointments by 15-minute intervals for scheduling
> optimization
> - Manufacturing: Aggregate sensor readings by hourly buckets to detect
> production patterns
> - DevOps: Bucket system events by 5-minute intervals for performance
> monitoring
> Industry precedent:
> - SQL Server 2022: `DATE_BUCKET()` supports TIME type bucketing
> - TimescaleDB: `time_bucket()` is one of their most popular functions
> Proposed Spark SQL examples
>
> {code:sql}
> -- Bucket a single time value by 15 minutes
> SELECT time_bucket(INTERVAL '15' MINUTE, TIME'09:37:22');-- Result: 09:30:00
> -- Bucket by 30 minutes
> SELECT time_bucket(INTERVAL '30' MINUTE, TIME'14:47:00');-- Result: 14:30:00
> -- Bucket by 1 hour
> SELECT time_bucket(INTERVAL '1' HOUR, TIME'16:35:00');-- Result: 16:00:00
> -- Bucket by 2 hours
> SELECT time_bucket(INTERVAL '2' HOUR, TIME'15:20:00');-- Result: 14:00:00
> {code}
> Group by Example
> {code:java}
> WITH time_bucket_data AS (
> SELECT * FROM VALUES
> (1, TIME'09:15:30.123456'),
> (2, TIME'09:37:45.654321'),
> (3, TIME'10:05:12.987654'),
> (4, TIME'14:42:00.111222'),
> (5, TIME'14:55:30.333444')
> AS t(id, event_time)
> )
> SELECT time_bucket(INTERVAL '30' MINUTE, event_time) AS time_slot,
> COUNT(*) AS cnt
> FROM time_bucket_data
> GROUP BY time_slot
> ORDER BY time_slot;
> +---------+---+
> |time_slot|cnt|
> +---------+---+
> | 09:00:00| 1|
> | 09:30:00| 1|
> | 10:00:00| 1|
> | 14:30:00| 2|
> +---------+---+
> {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]
