Affects any SQL server in a European timezone

The bug arrives without a deploy. No config changed, no library updated. On a Monday morning in late March or late October, your contract-defaulting job starts misfiring — and when you dig in, every line of the query looks correct.

That's because it is correct. The clock is the problem.

What breaks and why:

A common pattern for "has this contract been open more than 59 days?" looks like this:

(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(due_date)) / 86400 >= 59

One day = 86,400 seconds. Clean, obvious, testable. Works perfectly — until the day Europe springs its clocks forward.

A contract created in winter is stored with a CET timestamp (UTC+1). When you query it in summer, NOW() returns CEST (UTC+2). The raw second gap between those two timestamps is 3,600 seconds shorter than the calendar gap. Divide by 86,400 and a genuine 59-day-old contract returns 58.958. It slips through.

In autumn the reverse happens: clocks fall back, the difference bloats by 3,600 seconds, and a 58-day contract returns 59.041 — defaulted one day too early.

This happens on exactly two dates per year: the last Sunday of March (spring forward, CET → CEST) and the last Sunday of October (fall back, CEST → CET). Your CI pipeline runs in UTC and never sees it.

The fix is one function swap

Stop doing arithmetic on raw seconds when you mean calendar days. Use a function that actually counts days:

-- Before (breaks twice a year)
(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(due_date)) / 86400 >= 59

-- After (DST-safe)
DATEDIFF(NOW(), due_date) >= 59

DATEDIFF() counts calendar day boundaries — it doesn't care about the offset. No DST, no drift.

If you can't change the query

The architectural fix is to store all timestamps in UTC from day one. Then NOW() and due_date always share the same offset and the division-by-86400 approach works correctly. If you're on SQL Server 2016+, AT TIME ZONE 'UTC' can normalize both sides at query time without a schema change.

The rule to remember

Raw timestamp subtraction measures wall-clock seconds. Calendar days are not wall-clock seconds — two days per year they differ by a full hour. Any time your business logic cares about days, use a date-level function. Any time you store timestamps in local time, your arithmetic is one clock-shift away from a silent mistake.

Write one regression test with dates that span the last Sunday of March. You'll catch this class of bug forever.