If you have ever seen a discrepancy between aggregated data shown in a UI and the results of a direct SQL query against the same tables — and you happen to operate in a timezone with a half-hour offset from UTC — you have almost certainly run into this problem. It is not a bug in the conventional sense. It is a deterministic, fully reproducible consequence of an architectural decision that works correctly for integer-hour timezones and silently breaks expectations for everyone else.
Background: How Hourly Aggregation Works
Most audit and monitoring systems store event timestamps as Unix timestamps — the number of seconds elapsed since the epoch 1970-01-01 00:00:00 UTC. To build hourly reports, records are grouped into time buckets by hour. The standard implementation looks like this:
trunc(TIME_SLOT / 3600) * 3600The operation is straightforward: divide the timestamp by 3600 (seconds per hour), discard the remainder, multiply back. The result is the start of the current UTC hour. For example:
| Raw timestamp | UTC time | Bucket value | UTC bucket time |
|---|---|---|---|
| 1758202500 | 13:35:00 UTC | 1758200400 | 13:00:00 UTC |
| 1758204900 | 14:15:00 UTC | 1758204000 | 14:00:00 UTC |
| 1758208200 | 15:10:00 UTC | 1758207600 | 15:00:00 UTC |
To display a local time label in the UI, the timezone offset in seconds is added to the bucket value:
to_char(
to_date('01-01-1970', 'DD-MM-YYYY') + (bucket + offset_seconds) / 60 / 60 / 24,
'HH24'
)For UTC+3 (offset = 10800 seconds) this works perfectly: a UTC bucket of 13:00 becomes local hour 16. The bucket boundary aligns exactly with the local hour boundary. No surprises.
Where It Breaks: Timezones with Fractional Offsets
Not every timezone has an offset that is a multiple of a full hour. A number of regions historically use offsets with a 30- or even 45-minute component:
| Timezone | Offset | Fractional part | Countries / regions |
|---|---|---|---|
| IST | UTC+5:30 | +30 min | India, Sri Lanka |
| IRT / IRST | UTC+3:30 | +30 min | Iran |
| AFT | UTC+4:30 | +30 min | Afghanistan |
| NST | UTC−3:30 | −30 min | Newfoundland, Canada |
| ACST | UTC+9:30 | +30 min | Central Australia |
| NPT | UTC+5:45 | +45 min | Nepal |
| ACWST | UTC+8:45 | +45 min | Western Australia (part) |
For these timezones the UTC hour boundary and the local hour boundary never coincide. That misalignment is the root of the problem.
A Concrete Example: IST (UTC+5:30)
IST is the most illustrative case given the scale of the affected region — over 1.4 billion people. The offset is +5:30, which is +19800 seconds.
How a bucket is formed
Consider an event that occurred at 19:45:00 IST:
IST 19:45:00 = UTC 14:15:00 = timestamp 1758204900Applying the bucketing formula:
trunc(1758204900 / 3600) * 3600
= trunc(488390.25) * 3600
= 488390 * 3600
= 1758204000Bucket 1758204000 corresponds to 14:00:00 UTC.
How the UI converts the bucket into an hour label
(1758204000 + 19800) / 3600 / 24 → to_char(..., 'HH24') → 'Hour19'The UI displays Hour19. This looks reasonable: 14:00 UTC + 5:30 = 19:30 IST, rounded down to the hour gives 19.
What time interval is actually behind Hour19
Bucket 1758204000 (14:00 UTC) accumulates all events with timestamps from 1758204000 through 1758207599, i.e. 14:00:00–14:59:59 UTC. Converting to IST:
| UTC | IST (UTC+5:30) |
|---|---|
| 14:00:00 | 19:30:00 |
| 14:59:59 | 20:29:59 |
Bottom line: The UI labels this bucket Hour19, but it physically contains events from 19:30:00 to 20:29:59 IST. Not 19:00–19:59. Not 20:00–20:59. Exactly 30 minutes shifted from what the label implies.
Why a Direct SQL Query Returns Zero Rows
This is where the discrepancy becomes visible and looks like a bug. An analyst sees 65 hits for Hour19 in the UI and writes a SQL query to verify:
-- Seems logical: query 19:00–19:59 IST
SELECT count(*)
FROM AUDIT_HIT_<policy_id>
WHERE TIME_SLOT BETWEEN 1758202200 AND 1758205799;
-- ^19:00 IST ^19:59:59 ISTResult: 0 rows.
This is not a query error. The data genuinely does not exist in that range — because the events the UI attributes to Hour19 physically reside in the interval 19:30–20:29 IST:
-- The correct range for Hour19 in IST
SELECT count(*)
FROM AUDIT_HIT_<policy_id>
WHERE TIME_SLOT BETWEEN 1758204000 AND 1758207599;
-- ^19:30 IST ^20:29:59 ISTResult: 65 rows — matches the UI.
Diagnostics: How to Reproduce and Confirm
Step 1 — Identify the actual bucket boundaries for a given hour label
-- Maps each bucket to its real local time interval
-- Replace 19800 with your offset in seconds
SELECT DISTINCT
trunc(TIME_SLOT / 3600) * 3600 AS bucket_utc,
to_char(
to_date('01-01-1970','DD-MM-YYYY')
+ trunc(TIME_SLOT / 3600) * 3600 / 86400,
'YYYY-MM-DD HH24:MI:SS'
) AS bucket_start_utc,
to_char(
to_date('01-01-1970','DD-MM-YYYY')
+ (trunc(TIME_SLOT / 3600) * 3600 + 19800) / 86400,
'HH24:MI'
) AS ui_label_local,
to_char(
to_date('01-01-1970','DD-MM-YYYY')
+ (TIME_SLOT + 19800) / 86400,
'HH24:MI:SS'
) AS actual_local_min,
to_char(
to_date('01-01-1970','DD-MM-YYYY')
+ (trunc(TIME_SLOT / 3600) * 3600 + 3599 + 19800) / 86400,
'HH24:MI:SS'
) AS actual_local_max
FROM AUDIT_HIT_<policy_id>
WHERE TIME_SLOT BETWEEN <ts_start> AND <ts_end>
ORDER BY bucket_utc;Step 2 — Verify aggregates using the same logic as the materialized view
-- Equivalent to how AUD_SUM_* is populated
SELECT
trunc(t2.TIME_SLOT / 3600) * 3600 AS hours_bucket,
to_char(
to_date('01-01-1970','DD-MM-YYYY')
+ (trunc(t2.TIME_SLOT / 3600) * 3600 + 19800) / 86400,
'HH24'
) AS ui_hour_label,
sum(t2.HITS) AS hits,
sum(decode(t1.EVENT_TYPE, 'Query', t2.HITS, 0)) AS queries,
sum(decode(t1.EVENT_TYPE, 'Login', t2.HITS, 0)) AS logins
FROM audit_keys t1
JOIN AUDIT_HIT_<policy_id> t2 ON t1.CRC = t2.CRC
WHERE t2.TIME_SLOT BETWEEN <ts_start> AND <ts_end>
GROUP BY trunc(t2.TIME_SLOT / 3600) * 3600
ORDER BY hours_bucket;Step 3 — Derive the correct timestamp range for a target local hour
To correctly filter by local hour H in a timezone with a fractional offset, find the UTC bucket that the UI labels as that hour and use its boundaries:
-- The UI shows hour H when:
-- to_char(to_date('01-01-1970') + (bucket + offset) / 86400, 'HH24') = H
--
-- For Hour19 in IST (offset = 19800):
-- bucket + 19800 must resolve to 19:xx:xx
-- => bucket = 1758204000 (14:00 UTC = 19:30 IST)
--
-- Correct raw data range for Hour19 IST:
SELECT *
FROM AUDIT_HIT_<policy_id>
WHERE TIME_SLOT BETWEEN 1758204000 AND 1758207599;General Formula for Any Timezone
For an arbitrary timezone with offset O seconds, the bucket for an event with timestamp T is:
bucket = trunc(T / 3600) * 3600The UI hour label for that bucket:
hour_label = floor((bucket + O) / 3600) mod 24The local hour H boundary as the user understands it corresponds to the UTC moment:
local_H_start_utc = H * 3600 - O (for a given calendar date)But the bucket that will be labeled H is constructed differently:
bucket_labeled_H = trunc((H * 3600 - O) / 3600) * 3600For an integer-hour offset (UTC+3, UTC+5, UTC+8, etc.) the difference between the local boundary and the UTC bucket is zero — everything aligns.
For a fractional offset (UTC+5:30, UTC+3:30, UTC+9:30) a systematic shift emerges equal to the fractional part of the offset. For IST this is 30 minutes. For NPT (UTC+5:45) it is 45 minutes.
| Timezone | Offset | Bucket shift | Hour19 actually contains |
|---|---|---|---|
| UTC+3 (MSK) | +10800 s | 0 min | 19:00:00 – 19:59:59 |
| IST (UTC+5:30) | +19800 s | +30 min | 19:30:00 – 20:29:59 |
| ACST (UTC+9:30) | +34200 s | +30 min | 19:30:00 – 20:29:59 |
| NPT (UTC+5:45) | +20700 s | +45 min | 19:45:00 – 20:44:59 |
| NST (UTC−3:30) | −12600 s | −30 min | 18:30:00 – 19:29:59 |
Practical Consequences
1. Mismatch between the UI and direct SQL queries
Any analyst working in IST and trying to reproduce a UI metric through a direct query filtered by local hour will see a discrepancy. This is especially critical in audit scenarios where independent verification of system-reported figures is required.
2. Broken alert thresholds and rule-based triggers
If alert rules are written with filters on local hour boundaries — for example, “hit count during business hours 09:00–18:00” — they will capture the wrong data range. For IST every hour bucket is shifted by 30 minutes, so a rule targeting 09:00–18:00 IST is actually evaluating 09:30–18:30 IST.
3. False incident investigations
An operator sees anomalous activity at Hour02 in the UI. They query the raw data for 02:00–02:59 IST. No rows returned. Conclusion: “the UI is showing garbage data.” The real explanation: the data lives in the 02:30–03:29 IST range.
Remediation Options
Option 1 — Bucket by local time (recommended for new systems)
Build the bucket not in UTC but with the timezone offset applied from the start:
-- Local-hour bucket, stored as UTC
trunc((TIME_SLOT + offset_seconds) / 3600) * 3600 - offset_secondsThe result is stored in UTC, but the bucket boundary aligns with the local hour boundary. The UI applies the offset as usual when rendering labels. No shift occurs for any timezone.
Important: this is a schema-level change. It requires migrating all existing data and rebuilding every materialized view. Applicable only to new systems or a planned migration window.
Option 2 — Adjust query filters for existing systems
When working with data stored under the existing bucketing logic, use the correct timestamp range that reflects where the data actually lives:
-- Instead of filtering by local hour boundary:
-- WHERE TIME_SLOT BETWEEN local_H_start AND local_H_end
-- Filter by the UTC bucket boundary:
-- WHERE trunc(TIME_SLOT / 3600) * 3600 = target_bucket
-- Where target_bucket is computed as:
-- trunc((H * 3600 - offset_seconds) / 3600) * 3600Option 3 — Document as a known limitation
If changing the bucketing logic is not feasible — legacy system, no source access, contractual constraints — document the behavior explicitly:
- Add a UI warning for users in half-hour timezones: “Hour labels represent UTC-aligned buckets shifted by N minutes relative to local time.”
- Provide a conversion table mapping each UI hour label to the actual local time interval it covers.
- Include in technical documentation the correct timestamp range calculation for direct SQL queries.
Quick Diagnostic Reference
| Symptom | Likely cause | What to check |
|---|---|---|
| UI shows hits, direct SQL on local hour returns 0 rows | Half-hour timezone bucket shift | Shift the filter range by the fractional part of the offset |
| Sum of raw hits per hour does not match UI | Bucket boundary does not align with local hour boundary | Use trunc(TIME_SLOT/3600)*3600 in GROUP BY to match the system’s logic |
| Hour19 in the UI contains events timestamped 20:xx local time | Expected behavior for IST, ACST, and other UTC+X:30 zones | Bucket 14:00 UTC = 19:30–20:29 IST; UI labels it Hour19 |
| Alert fires 30 minutes later than expected | Rule filter uses local hour boundary instead of UTC bucket boundary | Recalculate boundary timestamps accounting for the bucket shift |
Summary
The problem is systematic and deterministic. For any given timezone the shift is always the same — equal to the fractional part of its UTC offset. This is not instability or a random bug: knowing the offset, you can precisely predict which real local interval is hidden behind any UI hour label.
Users in India, Iran, Afghanistan, Newfoundland, and Central Australia will encounter this behavior consistently. For all integer-hour timezones the system works exactly as expected.
When writing SQL queries to verify data from systems that use UTC-based hourly bucketing, always work from bucket boundaries rather than local hour boundaries. The rule is simple: filter the same way the system groups.