Why do UTC settlements appear on the wrong Hong Kong day?

Prepare the diagnostic input

The bank extract in this fixture stores UTC values, while the operational day uses an explicit fixed eight-hour offset. Extracting the UTC date first loses the midnight boundary. Convert the complete timestamp before taking its date, keeping the original UTC value for reconciliation.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE settlements(id TEXT PRIMARY KEY,utc_at TEXT,amount_cents INTEGER);
INSERT INTO settlements VALUES ('S1','2026-10-09 15:30:00',10000),('S2','2026-10-09 16:30:00',20000);

Reproduce the misleading result and correct it

SELECT date(utc_at) AS business_day,SUM(amount_cents) AS settled_cents FROM settlements GROUP BY business_day ORDER BY business_day;

SELECT date(utc_at,'+8 hours') AS business_day,SUM(amount_cents) AS settled_cents
FROM settlements GROUP BY business_day ORDER BY business_day;

15:30 UTC is 23:30 on 9 October under the supplied offset; 16:30 UTC is 00:30 on 10 October. The wrong query combines 30000 cents on the ninth. The corrected report allocates 10000 and 20000 cents to separate days.

Check the boundary before applying the fix

Insert exactly 16:00 UTC. It belongs to the next business day at 00:00, increasing 10 October to 25000 cents.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO settlements VALUES ('S3','2026-10-09 16:00:00',5000);

UTC+8 is an explicit fixture assumption, not an automatic timezone detector. Mixed offsets, daylight-saving zones and provider cut-offs need a dedicated conversion policy.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.