Create DATETIMECONVERTWINDOWHOP function
- Dominant language
- Java
- Stars
- 6.1k
- Forks
- 1.5k
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 189
Description
# Create a Window HOP function similar to DATETIMECONVERT.
The `DATETIMECONVERTWINDOWHOP` converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity and window hop size.
A hopping time window has a fixed duration (hopWindow paramenter) and hops by a specified hop interval (outputGranularity parameter). If the hop interval is smaller than the window size, hopping windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a hopping window of 15 minutes size and 5 minute hop interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. This is why the function returns an array of timestamps, in this example the array would contain 3 elements.
# Signature
```
DATETIMECONVERTWINDOWHOP(columnName, inputFormat, outputFormat, outputGranularity, hopWindow)
inputFormat and outputFormat are defined using the following structure:
# Usage Examples
These examples are based on the [Batch JSON Quick Start](https://docs.pinot.apache.org/basics/getting-started/quick-start#batch-json).
created_at_timestamp from milliseconds since epoch to seconds since epoch, bucketed to 1 hour window with 15 min granularity:
```
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERTWINDOWHOP(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:SECONDS:EPOCH',
'15:MINUTES',
'1:HOURS'
) AS windowHops
from githubEvents
WHERE id = 7044874134
```
id | created_at_timestamp | timeInMs | windowHops
-- | -- | -- | --
7044874134 | 2018-01-01 11:00:00.0 | 1514804402000 | [1514804402, 1514803502, 1514802602, 1514801702]
Moving window of unique user counts per hour with 15 min granularity:
```
select
DATETIMECONVERTWINDOWHOP(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:SECONDS:EPOCH',
'15:MINUTES',
'1:HOURS'
) AS windowHops,
DISTINCTCOUNT(id) AS unique_prs
from githubEvents
group by 1
ORDER BY 1
```
windowHops | unique_prs
-- | --
1514801700 | 6680
1514802600 | 10000
1514803500 | 10000
1514804400 | 10000
1514805300 | 3320
Contributor guide
Research direction
Start by locating the existing DATETIMECONVERT function implementation and its tests, then trace how SQL functions are registered and validated. Add the analogous hopping-window behavior described by the signature and examples, and verify the documented query results and returned timestamp arrays with focused tests.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100