How I calculate overtime pay across midnight — the edge cases most time-tracking apps get wrong

Here's a scenario that most time-tracking apps handle wrong: you work from 22:00 on a Friday and clock out at 02:00 on Saturday morning.

You worked four hours. But which four hours?

Two of them were Friday — a weekday. Two of them were Saturday — a weekend. If your overtime policy pays a 1.5× multiplier for weekend work, the correct calculation is:

2 hours × base rate × 1.0 (weekday)   = X
2 hours × base rate × 1.5 (weekend)   = 1.5X
total = 2.5X

Most apps do this instead:

4 hours × base rate × 1.0 (weekday, because you punched in on Friday) = 2X

or this:

4 hours × base rate × 1.5 (weekend, because you punched out on Saturday) = 3X

Both are wrong. The session crosses a boundary, and each segment should earn the rate that applies to its calendar date.

This is the edge case that shaped one of OvertimeIQ's core schema decisions.

---

The problem is worse than it looks

The Friday → Saturday case is the obvious one. But there are harder versions.

New Year's Eve: A shift starts at 23:00 on December 31st and ends at 03:00 on January 1st. The first segment is a regular weekday. The second segment is a public holiday — potentially earning a 2× multiplier. Two different rates, with a calendar year boundary in the middle.

Holiday-to-weekday: A shift starts on Diwali and ends after midnight on a regular working day. The first segment earns the holiday rate. The second earns the standard rate.

The wrong fix: Some apps ask users to manually split entries that cross midnight — enter one record for 22:00–00:00 and another for 00:00–02:00. This works, but it's the wrong UX for a tool that's supposed to remove friction. Users shouldn't need to understand the system's internal model to log their time.

---

The design decision: crosses_midnight flag

OvertimeIQ stores each overtime session as a single log entry, regardless of midnight crossings. The schema has one boolean field:

crosses_midnight INTEGER DEFAULT 0  -- 1 if end_time is on the following calendar day

The date stored on the entry is always the punch-in date — the calendar date when the session started. The end_time is stored as a 24h time string, which can be less than start_time when crosses_midnight = 1.

A shift from 22:00 to 02:00 is stored as:

date:             2025-01-03
start_time:       22:00
end_time:         02:00
crosses_midnight: 1
duration_hours:   4.0

The two-segment calculation happens at earnings-calculation time, not at entry time.

---

The earnings calculation

For entries where crosses_midnight = 0, the calculation is straightforward:

function calculateEarning(log: Log, job: Job, holidays: Holiday[]): number {
  const multiplier = getMultiplier(log.date, job, holidays);
  return log.duration_hours * job.hourly_rate * multiplier;
}

Where getMultiplier checks in priority order:

1. Is the date in the holidays table with is_active = 1? → job.holiday_multiplier
2. Is the date a Saturday or Sunday? → job.weekend_multiplier
3. Otherwise → 1.0 For crosses_midnight = 1, two segments are calculated separately:

function calculateMidnightCrossingEarning(log: Log, job: Job, holidays: Holiday[]): number {
  // Parse start and end as minutes past midnight
  const [startH, startM] = log.start_time.split(':').map(Number);
  const [endH, endM] = log.end_time.split(':').map(Number);

  // Segment A: from start_time to 00:00 (midnight)
  const minutesBeforeMidnight = (24 * 60) - (startH * 60 + startM);
  const hoursBeforeMidnight = minutesBeforeMidnight / 60;

  // Segment B: from 00:00 to end_time
  const hoursAfterMidnight = (endH * 60 + endM) / 60;

  // The day after punch-in date
  const nextDate = addDays(log.date, 1); // e.g. '2025-01-04'

  const multiplierA = getMultiplier(log.date, job, holidays);
  const multiplierB = getMultiplier(nextDate, job, holidays);

  const earningA = hoursBeforeMidnight * job.hourly_rate * multiplierA;
  const earningB = hoursAfterMidnight * job.hourly_rate * multiplierB;

  return earningA + earningB;
}

The key insight: multiplierA uses the punch-in date. multiplierB uses punch-in date + 1. They're looked up independently against the holidays table. The Dec 31 → Jan 1 case works automatically — no special handling, no user input.

---

Why shift_type was removed from the schema

An earlier version of the schema had a shift_type field: "weekday" | "weekend" | "holiday". The idea was to store the calculated type alongside the entry for display purposes.

This turned out to be wrong for two reasons:

First, it requires a decision at entry time about a value that depends on a date lookup. If the user adds a holiday to their holidays list after creating a log entry, the stored shift_type becomes stale. The earnings calculation would be correct (it re-looks up the multiplier from the date), but the displayed shift_type would be wrong.

Second, for midnight-crossing entries, there is no single shift_type. The entry is simultaneously two types. Storing one value would require either an arbitrary tiebreak rule or a composite value — both worse than just computing it from the date.

The fix was to remove the field entirely. Rate multipliers are always derived from the calendar date at calculation time, never stored. The display layer computes the label on the fly from the same lookup logic. If the user manually overrides a rate (via the per-entry ShiftTypeOverride control), that override is stored separately and takes precedence over the automatic detection.

---

The multiplier priority order matters

The three-level priority check (holiday → weekend → weekday) isn't arbitrary. Consider a public holiday that falls on a Saturday. In most overtime policies, the holiday rate takes precedence — the day earns holiday_multiplier, not weekend_multiplier. Checking the holiday list first enforces this correctly.

In OvertimeIQ, holiday_multiplier defaults to 2.0 and weekend_multiplier defaults to 1.5. A holiday Saturday earns 2×, not 1.5×.

The getMultiplier function:

function getMultiplier(date: string, job: Job, holidays: Holiday[]): number {
  // 1. Check holidays table first
  const isHoliday = holidays.some(h => h.date === date && h.is_active === 1);
  if (isHoliday) return job.holiday_multiplier;

  // 2. Check weekend
  const dayOfWeek = new Date(date + 'T00:00:00').getDay(); // 0=Sun, 6=Sat
  if (dayOfWeek === 0 || dayOfWeek === 6) return job.weekend_multiplier;

  // 3. Standard rate
  return 1.0;
}

Note the date parsing: date + 'T00:00:00' appends a local time to prevent the Date constructor from interpreting the ISO date string as UTC midnight and potentially shifting it back one calendar day in timezones behind UTC. This is a real bug — easily introduced, annoying to debug.

---

Duration is pre-calculated and stored

duration_hours is calculated at log-creation time and stored in the database. It's the source of truth for all earnings calculations.

For a midnight-crossing entry:

duration = (24 * 60 - start_minutes) / 60 + end_minutes / 60

For a standard entry:

duration = (end_minutes - start_minutes) / 60

Storing it avoids recalculating on every query. More importantly, it handles the edge case where someone manually edits start_time or end_time independently — the stored duration reflects what was computed at save time, and is only updated when the entry is explicitly re-saved.

---

The user-facing result

The user enters one log entry. If it crosses midnight, a crosses_midnight warning appears in the form's duration preview. The earnings display shows the total earning — the split calculation is invisible to them.

For the Dec 31 → Jan 1 example, the user sees: "4 hours earned ₹X" where ₹X correctly accounts for the holiday rate on the second segment. They never had to think about midnight, segments, or rate rules.

That's the goal: the edge case the app handles for you is the edge case you would otherwise get wrong in a spreadsheet.