-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Major
-
Component/s: DB
-
None
Some monthly audit records are not being calculated correctly. It may be due to a bug in the SQL handling month offsets with proper time zone support. For example, in the solaragg.process_one_aud_datum_daily_stale function, this calculation:
WHERE aud.node_id = stale.node_id AND aud.source_id = stale.source_id AND aud.ts_start >= stale.ts_start AND aud.ts_start < stale.ts_start + interval '1 month'
won't correctly handle time-zone specific date ranges because ts_start + interval '1 month' should be done in the node's local time zone. For reference:
select '2020-04-01 17:00:00+13'::timestamptz at time zone 'America/New_York' , ('2020-04-01 17:00:00+13'::timestamptz at time zone 'America/New_York' + interval '1 month') at time zone 'America/New_York' , '2020-04-01 17:00:00+13'::timestamptz + interval '1 month'
returns these 3 results; notice the last 2 results differ (the 3rd result is correct):
2020-04-01 00:00:00 2020-05-01 17:00:00+12 -- WRONG 2020-05-01 16:00:00+12 -- CORRECT
Additionally, audit month rows might be getting deleted from the time zone cleanup routines because of the incorrect month calculation:
DELETE FROM solaragg.aud_datum_monthly WHERE node_id = stale.node_id AND source_id = stale.source_id AND ts_start > stale.ts_start - interval '1 month' AND ts_start < stale.ts_start + interval '1 month' AND ts_start <> stale.ts_start;