confluentinc / confluentinc/ksql

Windowed aggregations: calculate daily aggregates based on time zones other than UTC

Open
#1,968 9 comments 17 reactions 0 assignees View on GitHub
data-accessibility enhancement requires-streams-change streaming-engine user-experience window-functions
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.