Якщо ви коли-небудь бачили розбіжність між агрегованими даними в UI та результатами прямого SQL-запиту до тих самих таблиць — і при цьому працюєте в часовому поясі з половинним зміщенням від UTC — ви майже напевно зіткнулися з цією проблемою. Це не баг у традиційному розумінні. Це детермінований, повністю відтворюваний наслідок архітектурного рішення, що коректно працює для цілогодинних часових поясів і тихо порушує очікування для всіх інших.
Контекст: як працює погодинна агрегація
Більшість систем аудиту та моніторингу зберігають часові мітки подій як Unix timestamps — кількість секунд, що минули з епохи 1970-01-01 00:00:00 UTC. Для побудови погодинних звітів записи групуються в часові кошики за годиною. Стандартна реалізація виглядає так:
trunc(TIME_SLOT / 3600) * 3600Операція проста: ділимо timestamp на 3600 (секунд у годині), відкидаємо залишок, множимо назад. Результат — початок поточної UTC-години. Наприклад:
| Сирий timestamp | Час UTC | Значення кошика | Час кошика UTC |
|---|---|---|---|
| 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 |
Для відображення локальної мітки часу в UI до значення кошика додається зміщення часового поясу в секундах:
to_char(
to_date('01-01-1970', 'DD-MM-YYYY') + (bucket + offset_seconds) / 60 / 60 / 24,
'HH24'
)Для UTC+3 (зміщення = 10800 секунд) це працює ідеально: UTC-кошик 13:00 стає місцевою годиною 16. Межа кошика точно збігається з межею місцевої години. Без сюрпризів.
Де ламається: часові пояси з дробовими зміщеннями
Не кожен часовий пояс має зміщення, кратне повній годині. Ряд регіонів історично використовує зміщення з компонентом у 30 або навіть 45 хвилин:
| Часовий пояс | Зміщення | Дробова частина | Країни / регіони |
|---|---|---|---|
| IST | UTC+5:30 | +30 хв | Індія, Шрі-Ланка |
| IRT / IRST | UTC+3:30 | +30 хв | Іран |
| AFT | UTC+4:30 | +30 хв | Афганістан |
| NST | UTC−3:30 | −30 хв | Ньюфаундленд, Канада |
| ACST | UTC+9:30 | +30 хв | Центральна Австралія |
| NPT | UTC+5:45 | +45 хв | Непал |
| ACWST | UTC+8:45 | +45 хв | Західна Австралія (частково) |
Для цих часових поясів межа UTC-години та межа місцевої години ніколи не збігаються. Саме ця невідповідність є першопричиною проблеми.
Конкретний приклад: IST (UTC+5:30)
IST — найбільш показовий випадок з огляду на масштаб ураженого регіону — понад 1,4 млрд людей. Зміщення становить +5:30, тобто +19800 секунд.
Як формується кошик
Розглянемо подію, що відбулася о 19:45:00 IST:
IST 19:45:00 = UTC 14:15:00 = timestamp 1758204900Застосовуємо формулу розбивки на кошики:
trunc(1758204900 / 3600) * 3600
= trunc(488390.25) * 3600
= 488390 * 3600
= 1758204000Кошик 1758204000 відповідає 14:00:00 UTC.
Як UI перетворює кошик на мітку години
(1758204000 + 19800) / 3600 / 24 → to_char(..., 'HH24') → 'Hour19'UI відображає Hour19. Виглядає розумно: 14:00 UTC + 5:30 = 19:30 IST, округлення вниз до години дає 19.
Який часовий інтервал насправді стоїть за Hour19
Кошик 1758204000 (14:00 UTC) акумулює всі події з timestamp від 1758204000 до 1758207599, тобто 14:00:00–14:59:59 UTC. Конвертуємо в IST:
| UTC | IST (UTC+5:30) |
|---|---|
| 14:00:00 | 19:30:00 |
| 14:59:59 | 20:29:59 |
Висновок: UI позначає цей кошик як Hour19, але фізично він містить події з 19:30:00 до 20:29:59 IST. Не 19:00–19:59. Не 20:00–20:59. Рівно на 30 хвилин зміщено відносно того, що припускає мітка.
Чому прямий SQL-запит повертає нуль рядків
Саме тут розбіжність стає видимою і виглядає як баг. Аналітик бачить 65 хітів для Hour19 в UI і пише SQL-запит для верифікації:
-- Здається логічним: запит на 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 ISTРезультат: 0 рядків.
Це не помилка запиту. Даних у цьому діапазоні дійсно не існує — тому що події, які UI приписує Hour19, фізично знаходяться в інтервалі 19:30–20:29 IST:
-- Коректний діапазон для Hour19 в IST
SELECT count(*)
FROM AUDIT_HIT_<policy_id>
WHERE TIME_SLOT BETWEEN 1758204000 AND 1758207599;
-- ^19:30 IST ^20:29:59 ISTРезультат: 65 рядків — збігається з UI.
Діагностика: як відтворити та підтвердити
Крок 1 — Визначення фактичних меж кошика для заданої мітки години
-- Відображає кожен кошик на реальний місцевий часовий інтервал
-- Замініть 19800 на ваше зміщення в секундах
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;Крок 2 — Перевірка агрегатів із тією ж логікою, що й матеріалізований вью
-- Еквівалент того, як заповнюється AUD_SUM_*
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;Крок 3 — Визначення коректного діапазону timestamp для цільової місцевої години
Для коректної фільтрації за місцевою годиною H у часовому поясі з дробовим зміщенням знайдіть UTC-кошик, який UI позначає як цю годину, і використовуйте його межі:
-- UI показує годину H коли:
-- to_char(to_date('01-01-1970') + (bucket + offset) / 86400, 'HH24') = H
--
-- Для Hour19 в IST (offset = 19800):
-- bucket + 19800 має давати 19:xx:xx
-- => bucket = 1758204000 (14:00 UTC = 19:30 IST)
--
-- Коректний діапазон сирих даних для Hour19 IST:
SELECT *
FROM AUDIT_HIT_<policy_id>
WHERE TIME_SLOT BETWEEN 1758204000 AND 1758207599;Загальна формула для довільного часового поясу
Для довільного часового поясу зі зміщенням O секунд кошик для події з timestamp T:
bucket = trunc(T / 3600) * 3600Мітка UI-години для цього кошика:
hour_label = floor((bucket + O) / 3600) mod 24Межа місцевої години H в розумінні користувача відповідає моменту UTC:
local_H_start_utc = H * 3600 - O (для заданої календарної дати)Але кошик, що буде позначений як H, будується інакше:
bucket_labeled_H = trunc((H * 3600 - O) / 3600) * 3600Для цілогодинного зміщення (UTC+3, UTC+5, UTC+8 тощо) різниця між місцевою межею та UTC-кошиком дорівнює нулю — все збігається.
Для дробового зміщення (UTC+5:30, UTC+3:30, UTC+9:30) виникає систематичний зсув, рівний дробовій частині зміщення. Для IST це 30 хвилин. Для NPT (UTC+5:45) — 45 хвилин.
| Часовий пояс | Зміщення | Зсув кошика | Hour19 фактично містить |
|---|---|---|---|
| UTC+3 (MSK) | +10800 с | 0 хв | 19:00:00 – 19:59:59 |
| IST (UTC+5:30) | +19800 с | +30 хв | 19:30:00 – 20:29:59 |
| ACST (UTC+9:30) | +34200 с | +30 хв | 19:30:00 – 20:29:59 |
| NPT (UTC+5:45) | +20700 с | +45 хв | 19:45:00 – 20:44:59 |
| NST (UTC−3:30) | −12600 с | −30 хв | 18:30:00 – 19:29:59 |
Практичні наслідки
1. Розбіжність між UI та прямими SQL-запитами
Будь-який аналітик, що працює в IST і намагається відтворити метрику UI через прямий запит із фільтрацією за місцевою годиною, побачить розбіжність. Це особливо критично в сценаріях аудиту, де потрібна незалежна верифікація показників, що надає система.
2. Порушення порогів сповіщень та тригерів на основі правил
Якщо правила сповіщень написані з фільтрами на межі місцевих годин — наприклад, “кількість хітів у робочі години 09:00–18:00” — вони захоплюватимуть неправильний діапазон даних. Для IST кожен кошик годин зміщений на 30 хвилин, тому правило, що цілить у 09:00–18:00 IST, насправді оцінює 09:30–18:30 IST.
3. Хибні розслідування інцидентів
Оператор бачить аномальну активність у Hour02 в UI. Він запитує сирі дані за 02:00–02:59 IST. Рядків не повернуто. Висновок: “UI показує сміття.” Справжнє пояснення: дані знаходяться в діапазоні 02:30–03:29 IST.
Варіанти виправлення
Варіант 1 — Кошики за місцевим часом (рекомендовано для нових систем)
Будуйте кошик не в UTC, а із застосованим зміщенням часового поясу з самого початку:
-- Кошик місцевої години, збережений як UTC
trunc((TIME_SLOT + offset_seconds) / 3600) * 3600 - offset_secondsРезультат зберігається в UTC, але межа кошика збігається з межею місцевої години. UI застосовує зміщення як зазвичай при рендерингу міток. Зсув не виникає для жодного часового поясу.
Важливо: це зміна на рівні схеми. Вимагає міграції всіх наявних даних і перебудови кожного матеріалізованого вью. Застосовно лише для нових систем або запланованого вікна міграції.
Варіант 2 — Коригування фільтрів запитів для наявних систем
При роботі з даними, що зберігаються за наявною логікою розбивки, використовуйте коректний діапазон timestamp, що відображає реальне розташування даних:
-- Замість фільтрації за межею місцевої години:
-- WHERE TIME_SLOT BETWEEN local_H_start AND local_H_end
-- Фільтруйте за межею UTC-кошика:
-- WHERE trunc(TIME_SLOT / 3600) * 3600 = target_bucket
-- Де target_bucket обчислюється як:
-- trunc((H * 3600 - offset_seconds) / 3600) * 3600Варіант 3 — Задокументувати як відоме обмеження
Якщо зміна логіки розбивки нездійсненна — legacy-система, відсутність доступу до джерел, контрактні обмеження — задокументуйте поведінку явно:
- Додайте в UI попередження для користувачів у годинних поясах із половинним зміщенням: “Мітки годин представляють UTC-вирівняні кошики, зміщені на N хвилин відносно місцевого часу.”
- Надайте таблицю відповідності, що відображає кожну UI-мітку години на фактичний місцевий часовий інтервал, який вона охоплює.
- Включіть до технічної документації розрахунок коректного діапазону timestamp для прямих SQL-запитів.
Швидкий діагностичний довідник
| Симптом | Ймовірна причина | Що перевірити |
|---|---|---|
| UI показує хіти, прямий SQL на місцеву годину повертає 0 рядків | Зсув кошика через часовий пояс із половинним зміщенням | Зсунути діапазон фільтру на дробову частину зміщення |
| Сума сирих хітів за годину не збігається з UI | Межа кошика не збігається з межею місцевої години | Використовувати trunc(TIME_SLOT/3600)*3600 у GROUP BY для відповідності логіці системи |
| Hour19 в UI містить події з timestamp 20:xx місцевого часу | Очікувана поведінка для IST, ACST та інших зон UTC+X:30 | Кошик 14:00 UTC = 19:30–20:29 IST; UI позначає його як Hour19 |
| Сповіщення спрацьовує на 30 хвилин пізніше за очікуване | Фільтр правила використовує межу місцевої години замість межі UTC-кошика | Перерахувати граничні timestamp з урахуванням зсуву кошика |
Підсумок
Проблема є систематичною та детермінованою. Для будь-якого заданого часового поясу зсув завжди однаковий — рівний дробовій частині його UTC-зміщення. Це не нестабільність і не випадковий баг: знаючи зміщення, можна точно передбачити, який реальний місцевий інтервал прихований за будь-якою UI-міткою години.
Користувачі в Індії, Ірані, Афганістані, Ньюфаундленді та Центральній Австралії стикатимуться з цією поведінкою постійно. Для всіх цілогодинних часових поясів система працює точно так, як очікується.
При написанні SQL-запитів для верифікації даних із систем, що використовують UTC-based погодинну розбивку, завжди працюйте від меж кошиків, а не від меж місцевих годин. Правило просте: фільтруйте так само, як система групує.