An Attendance System Plays Timezone Roulette

Part 10 ended on a single projection that reads two clocks — filtering on UtcNow, formatting Now — and disagrees with itself by five and a half hours. That line is not an outlier; it is the estate in miniature. This is a system whose entire product is timestamps — when you arrived, when you left, how long you worked, which shift that implies, what allowance that pays — and at no point did it ever decide, estate-wide, what time it is. Five executables, at least three notions of “now”, and a schema that records none of them distinctly. This part is about why that roulette wheel kept paying out anyway, and where the losing numbers were.

The saving grace: one country, one offset, no DST

Let me start with the confession that reframes everything: this system survived because India has exactly one timezone and no daylight saving. IST is UTC+05:30, year-round, nationwide. The badge gates in all three metros stamp the same local time; no spring-forward ever created a phantom 25-hour workday; no employee ever badged out in a different offset than they badged in. Every classic timezone catastrophe requires either multiple zones or a DST transition, and the platform's deployment geography supplies neither.

I say this without pride, because it means the system's correctness is geographic luck, not design. The code that runs cleanly across three Indian metros would produce quietly wrong attendance the week it was deployed for a client in Sydney or London. And even within the lucky geography, the mixed clocks produce real, bounded wrongness — a five-and-a-half-hour band around every midnight where the system's idea of today is up for grabs.

Five executables, three clocks

The inventory, unit by unit:

The three Azure Functions mostly speak UTC, because Functions timers fire on UTC cron expressions and UtcNow is what the samples used. The console job speaks DateTime.Now exclusively — which resolves to the host machine's timezone, an infrastructure setting nobody versioned. Run it on a VM configured for IST and it agrees with the badge gates; lift it to a container or a rebuilt host defaulting to UTC and every “today” in six hundred lines shifts by five and a half hours, with no code change and no error. The calculator's correctness is stored in a control panel setting on a machine.

The daily sync's 00:00 UTC trigger deserves its footnote of accidental genius: midnight UTC is 05:30 IST, which is a perfect time to refresh the org mirror — after the previous business day has fully ended, before the earliest shift arrives. The schedule reads like a bug (why would an Indian HR sync run at midnight UTC?) and behaves like a feature. Nobody documented whether it was chosen or fluked, which means nobody will defend it when someone “fixes” it to local midnight.

Which day is today?

The losing numbers cluster around day boundaries, because the estate's unit of business meaning is the IST calendar day — a report row is one employee, one day — while half the writers compute “today” in UTC.

Consider the notifier's window from part 10: sessions with LogDate == today || yesterday, where today is UTC. Between 00:00 and 05:29 IST, UTC is still on the previous date — so a night-shift employee's fresh virtual swipe-in, stamped with an IST-flavoured LogDate by the web app, sits at the edge of a UTC-computed window. The two-day span mostly absorbs the skew — that is what it is unknowingly for — but “mostly absorbs” is the phrase you never want in the sentence that decides whether someone gets nagged, or whether a leave-sync watermark re-fetches the right day's records.

The leave sync's watermark makes the same bet: it truncates a UTC timestamp to yyyy-MM-dd and asks the leave system for changes since that date. The leave system, run by and for people in IST, means IST days. For five and a half hours out of every twenty-four, those dates differ by one. The overlap-and-upsert design from part 3 means the error is re-fetching too much rather than too little — idempotency once again absorbing what precision didn't provide. It is remarkable how many of this estate's sins are quietly forgiven by that one property.

Columns with no opinion

The schema's contribution is neutrality: every timestamp in the database is plain datetime. Not datetimeoffset, which records the offset alongside the instant; just an unlabelled wall-clock value. A datetime cannot tell you which clock wrote it — and in this estate, different writers used different clocks on the same columns. A StatusSheet.RunTime written by the calculator is IST-ish; one written by a function is UTC; they sort interleaved in the same table, silently five and a half hours apart. Nobody can ever repair this retroactively, because the information needed to repair it — which clock? — was never captured.

The rule I would tattoo on the estate: a timestamp without a declared clock is not data, it is a number that resembles data. The fixes are old and boring — store UTC everywhere and convert at the display edge, or use datetimeoffset and let every row carry its own provenance. Either one works. Choosing is the part that matters, and it must be estate-wide, because a shared database with per-writer clock conventions is the shared-contract problem of part 1 in its purest form: the column's type says nothing, so the meaning lives in five codebases at once.

The 24-hour ceiling

One last temporal trap, this time about durations. The jobs record their elapsed run time into StatusSheet.TotalTime, whose type is SQL time — a time-of-day type, maximum value 23:59:59.9999999. A duration is not a time of day, but time is what the column was, so every unit in the estate contains this idiom:

var elapsed = DateTime.Now - startTime;
if (elapsed.Days == 0)
    status.TotalTime = elapsed;   // else: leave it null, say nothing

If a run takes more than twenty-four hours, its duration is silently dropped — the one metric you would most want about a pathological run is discarded precisely because the run was pathological. The backfill from part 6, with its round-trip-per-row writes, is exactly the kind of job that could cross the ceiling; the record of it crossing would be a NULL. The same mismatch caps every duration column in the reporting table too — office hours, WFH hours — all time, all structurally unable to express “more than a day”, in a system that part 9 showed detecting ≥24-hour totals as data errors. The type system knew something was wrong before anyone else did; it just expressed the knowledge as data loss.

Durations wanted to be integers — seconds, minutes, ticks — or bigint intervals; time-of-day wanted time; instants wanted a declared clock. Three different meanings, one type, and the estate paid for the conflation in dropped facts.

Enough about when. The next part descends into the stored procedures and stays there for the rest of the series — beginning with the estate's most instructive pairing: two procedures, in the same database, solving the same sortable-paging problem, one of them a textbook SQL-injection hole and the other a textbook fix. Next, two dynamic-SQL procs — one injectable, one correct.