confluentinc / confluentinc/ksql
Windowed aggregations: calculate daily aggregates based on time zones other than UTC
- Dominant language
- Java
- Stars
- 315
- Forks
- 1k
- Avg merge
- 1d 7h
- Merged PRs (30d)
- 36
Description
Per [StackOverflow question](https://stackoverflow.com/questions/52574467/ksql-change-the-time-zone-in-window-tumbling-clause), if calculating aggregates with KSQL at day-level and above (day/week/month/etc) it would be useful to be able to do these based on the local timezone.
KSQL windows are based on the epoch (number of milliseconds since Jan 01 1970, **UTC**).
For aggregates at a second/minute/hour level this doesn't really matter, but for an organisation wanting to report on "yesterday" etc then the window would need to include events within the 24 hour time window based on the specified timezone, not necessarily UTC.
One idea is if KSQL supported a `DATEADD` function, where the event timestamp could be shifted by the required hours to match the local timezone relative to UTC, and then that used as the timestamp for aggregations. However, this feels like a bit of a hack—open to better suggestions for doing this properly.
Contributor guide
Research direction
No source files, tests, or entry points are named. Start by tracing how KSQL assigns timestamps and constructs day-level windows, then compare the requested local-time behavior with the proposed DATEADD workaround. Done means the project has a defined, supported approach for non-UTC daily aggregation boundaries.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases, stream-processing
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100