Why does an overnight booking have a negative duration?

Prepare the diagnostic input

A booking starts at 23:30 and ends at 01:00 on the following day. Clock components alone discard the day advance. Use the difference between complete canonical timestamps, then convert days to minutes and round the floating-point calculation for this minute-granularity fixture.

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 bookings(id TEXT PRIMARY KEY,starts_at TEXT,ends_at TEXT);
INSERT INTO bookings VALUES ('B1','2026-10-10 23:30','2026-10-11 01:00');

Reproduce the misleading result and correct it

SELECT id,60*(CAST(strftime('%H',ends_at) AS INTEGER)-CAST(strftime('%H',starts_at) AS INTEGER))
+CAST(strftime('%M',ends_at) AS INTEGER)-CAST(strftime('%M',starts_at) AS INTEGER) AS duration_minutes FROM bookings ORDER BY id;

SELECT id,CAST(ROUND((julianday(ends_at)-julianday(starts_at))*1440,0) AS INTEGER) AS duration_minutes FROM bookings ORDER BY id;

The wrong result is −1350 minutes. The corrected result is 90. An overnight span is valid here, while an actual end before start would remain a separate invalid-record check rather than being fixed with an absolute value.

Check the boundary before applying the fix

Add equal start and end timestamps. Duration is zero, not a full day; whether zero-length bookings are permitted is a separate booking rule.

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 bookings VALUES ('B2','2026-10-11 01:00','2026-10-11 01:00');

Timestamps share the same declared clock. Mixed timezones, ambiguous local times, seconds and billing increments require a more specific duration 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.