The Shift Calendar Data Model Behind Every OEE Number

Key takeaways

• Availability divides run time by planned production time, and planned production time is a row in a calendar table, not a measurement. Edit the row and the number moves.
• In the worked week below the same 6,400 run minutes report 84.3% or 89.4% availability, a gap of 435 denominator minutes and 5.1 percentage points.
• A rotating pattern resolves to a crew by arithmetic, not by hand: cycle_length_days, anchor_date and cycle_offset_days, and one modulo.
• Store a shift as two real instants, never as a wall clock duration. The night shift starting 28 March 2026 is 420 minutes long, not 480, and the one starting 24 October 2026 is 540.
• A stop split at a shift boundary and the same stop given whole to the shift it started in produce the same daily number and two different shift numbers, 96.9% against 89.0%.

Four times, and only one of them is the denominator

Every argument about an OEE number starts in the same place: what was this machine supposed to be doing?

That question has four legitimate answers, and a plant that has not written down which one it uses does not really have an availability figure.

The chain runs in one direction, and each step removes minutes from the line above it.

Calendar time minus unscheduled time = scheduled time.

Scheduled time minus deducted planned blocks = planned production time.

Planned production time minus unplanned downtime = run time.

OEE availability divides run time by planned production time, which is the third line of that chain and not the first.

The OEE calculation guide carries the rest of the formula, and our OEE data model names the five tables an OEE number is built from.

This page specifies the first of those five, the one that decides the denominator.

Different plants choose differently, and that is allowed

One plant deducts breaks only. Another deducts breaks, scheduled changeovers, planned maintenance windows and hours with no order on the book.

Neither is wrong as arithmetic, and the second will always report the higher number.

What is wrong is not publishing the choice, because an unpublished denominator turns every comparison between two lines into a guess.

The shift pattern: a template, not a schedule

A pattern says how a working week repeats. It says nothing about any particular date.

Keeping those two ideas in separate tables is what stops a calendar becoming a spreadsheet somebody edits by hand every December.

The timezone field has to hold an IANA name such as Europe/Berlin, never a stored offset like plus two hours.

An offset is right for about seven months a year, and two of the boundary cases below are consequences of storing one.

Never update a pattern in place. Close the row with an effective_to and insert the new version, or every shift you already materialised becomes unexplainable.

The slots inside the cycle

The shape of the week lives in child rows, one per position in the cycle.

Store duration_min rather than an end time, because a nominal eight hour shift is an intention and an end time is a fact about one specific date.

The shift instance: one dated shift on one line

An instance is one concrete shift, on one machine or line, on one production date.

It is the row an OEE query joins to, and it should be materialised in advance by a job, not computed at report time.

The pair of fields that matters most is duration_min next to the two timestamps, with the duration derived and never typed.

If your schema lets a human write 480 into a row whose two instants are 420 minutes apart, you have already lost the argument about that week.

Why the production date is not the clock date

A night shift that starts at 22:00 on Saturday and ends at 06:00 on Sunday belongs to exactly one production date, and the sane convention is the date it started.

Derive the date from each event timestamp instead and that shift splits in two, so every daily report is wrong at both edges.

Publish the rule in one sentence next to the schema: the production date of a shift is the local date of start_utc.

Planned non-production blocks

A block is a stretch of a shift that the plant never expected to produce anything in.

Breaks, scheduled changeovers, planned maintenance windows and no-demand hours are the same shape of thing, so they belong in one table with a type column.

The field that makes this table worth building is deduct_flag.

Record the block whatever your policy says, and let the flag decide whether it leaves the denominator.

The block type list

Keep this list short and stable, and put the detail in reason_ref rather than inventing a seventh type every quarter.

Unplanned stops never appear here. They live in the machine state event table and carry their own codes, as set out in our guide to downtime reason code design.

Because the block is stored either way, changing policy is a flag and a recompute rather than a data migration, and it is reversible.

Calendar exceptions

An exception is the row that says this date is not like the pattern says it is.

Public holidays, shutdown weeks, an extra Saturday and a cancelled night shift are all the same table.

The priority field looks like over engineering until the first national holiday lands inside an annual shutdown week.

Two exceptions then claim the same date, and without a tiebreak the answer depends on which row the query happened to read first.

Point scope_ref at a stable surrogate key so a plant reorganisation does not orphan a year of exceptions, which is the argument made in our asset hierarchy data model.

The assignment that ties a pattern to a line and a crew

A pattern on its own covers nothing. The assignment says which asset runs it, from when, and where each crew sits inside the cycle.

The anchor_date must never move once rows exist, because moving it silently reassigns every crew on every past date.

If the rotation genuinely has to shift, close the assignment with a valid_to and open a new one with a new offset.

Resolving a rotating pattern without a spreadsheet

A three shift, four crew continuous rotation is the case that defeats most calendars, and it does not have to.

The whole rotation is three numbers and one modulo.

day_in_cycle = (production_date minus anchor_date, in days, minus cycle_offset_days) modulo cycle_length_days.

Look the slot up by day_in_cycle and you have that crew's shift, computed and identical on every machine that runs the query.

The eight day cycle

Take the common metric rotation, two days, two afternoons, two nights, two off, with cycle_length_days set to 8.

The four crews and their offsets

Four crews sit two days apart in the same cycle, which is the only thing the offsets have to say.

Those four offsets are not a convention, they are a proof obligation.

Walk all eight days and every one has exactly one crew on days, one on afternoons, one on nights and one off. That walk is the test your materialising job should run before it writes a row.

One week resolved

Set anchor_date to Monday 5 January 2026 and take the week of Monday 23 March 2026, which is day 77 of the cycle.

Nobody typed that table. It is nine stored numbers and a modulo, which is exactly why it will still be right in 2031.

Store the resolved crew_id on the instance once the shift is materialised, so a later change to the assignment cannot rewrite who was on shift last March.

The boundary cases that corrupt OEE

These are the cases that never reach a requirements document and always reach a dispute. Each one needs a written rule that is identical in the dashboard, the export and the monthly pack.

1. A shift that crosses midnight

The production date is the local date of start_utc, which is why Sunday 29 March shows zero shift instances in the worked week below, even though the machine ran until 06:00 that morning.

2. The spring clock change

In 2026 the European clocks go forward on Sunday 29 March, at 01:00 UTC, so on Central European clocks 02:00 becomes 03:00.

That local day is 23 hours long, 1,380 minutes, and the night shift that starts at 22:00 on Saturday 28 March lasts 420 real minutes against a nominal 480.

A calendar that subtracts wall clock times puts 480 in the denominator for a shift the machine could not physically have run for more than 420 minutes.

The reported availability comes out too low, and nobody can explain why one night in March always looks bad.

3. The autumn clock change

In 2026 the clocks go back on Sunday 25 October, again at 01:00 UTC, so on Central European clocks 03:00 becomes 02:00.

That local day is 25 hours long, 1,500 minutes, and the night shift starting at 22:00 on Saturday 24 October lasts 540 real minutes.

Run that shift without stopping and a naive calendar reports availability of 540 divided by 480, which is 112.5%.

Availability above 100% is at least loud. The quiet version is what it does to a month.

Take a continuously running plant whose true availability is exactly 90.0% in both months.

March run time is 40,122 minutes and October run time is 40,230, because the months are genuinely different lengths.

Divide both by the naive 44,640 and March reports 89.9% while October reports 90.1%.

A 0.2 point swing is small enough to survive every review and large enough to start a project, and the machines did nothing.

The fix is not a correction factor. It is storing start_utc and end_utc and letting the database subtract two instants.

4. A stop that crosses a shift boundary

On Thursday 26 March a breakdown starts at 21:48 and ends at 22:31, so 43 minutes in total, 12 of them before the 22:00 boundary and 31 after.

The afternoon shift has 390 minutes of planned production time that day and the night shift has 450, and this is the only unplanned stop in either.

The two rules move the afternoon crew by 7.9 points and the night crew by 6.9 points.

Both rules produce 797 run minutes over 840 planned minutes for the pair, so the day total is 94.9% either way.

The daily number is safe, the shift comparison is not, and the choice has to be written down before anyone puts shift league tables on a screen.

Split at the boundary is the better default: give the two parts a shared stop_id so a stop count is still one stop, and the duration still adds up.

Our guide to micro stops covers the short stops that never become events at all.

5. Overlapping and gapped patterns

Change a pattern from 1 April while the old pattern is already materialised through 30 April and you get two sets of instances for the same dates.

A three shift day with 1,350 planned production minutes becomes 2,700, so 1,200 run minutes report 44.4% instead of 88.9%.

That failure is at least obvious. The gapped version is the dangerous one.

Leave two production days with no shift instance at all and they do not report zero, they vanish from the denominator entirely.

A month that should read 25,200 run minutes over 29,700 planned, which is 84.8%, instead reads 24,000 over 27,000, which is 88.9%.

The month gained 4.0 points by losing 2,700 minutes of denominator, and no report anywhere shows a missing day.

Enforce it in the database, not in a screen: for one asset, no two instances may overlap in real time, and on a continuous pattern the next start_utc must equal the previous end_utc.

Then have that job refuse to commit when an asset's instance count for a day changes with no exception row to explain it.

Worked example: one machine, one week

Line LINE‑04 runs the eight day rotation above, three shifts a day, Monday to Friday, in Europe/Berlin.

Two exceptions apply to the week of Monday 23 March 2026: the Wednesday night shift is cancelled for an electrical inspection, and Saturday 28 March is opened as an extra day.

1. The shift instances

Wednesday is 960 because the SHIFT_CANCEL exception removed one instance, and Saturday is 1,380 because its night shift crosses the spring clock change.

Seventeen shifts at a nominal 480 minutes would be 8,160, so the clock change costs this week 60 minutes of scheduled time before anything else happens.

Calendar time for the same seven days is 10,020 real minutes, not the 10,080 a week normally holds.

2. The deductions

Every block below is stored on the shift instance it sits inside, with its own deduct_flag.

Check the events before computing anything: 6,400 run plus 755 unplanned plus 945 of planned blocks is 8,100, which ties back to scheduled time exactly.

The 43 minute breakdown from the boundary case above is 43 of those 755 minutes.

3. Availability, two defensible ways

Now apply the two policies to the identical set of events.

The numerator is 6,400 minutes in both rows, and not one machine event differs between them.

The gap is 435 minutes of denominator and 5.1 percentage points, which is larger than most improvement projects deliver in a quarter.

Both numbers are honest. Only one of them can be the number on the wall, and the choice has to be written down with a date on it.

4. What the clock change did on its own

Take the second policy and make one mistake: let the calendar subtract wall clock times, so the Saturday night shift counts 480 minutes instead of 420.

Scheduled time becomes 8,160, planned production time becomes 7,215, and availability falls from 89.4% to 88.7%.

That is 0.7 of a point lost to a single hour, on one week, on one machine.

5. What the example proves

Three availability figures came out of this week: 84.3%, 88.7% and 89.4%.

The stops never moved, the counts never moved and the run time was 6,400 minutes in every version.

Every point of difference came from the calendar, which is the argument to have first and the table to specify first.

Once the denominator is settled, a short daily OEE meeting over stable numbers starts to be worth holding.

For the losses inside run time, the six big losses is the map and overall equipment effectiveness is the whole metric in one page.

Five questions to ask a vendor about calendar data

1. Show me the shift instance row for a shift that crosses midnight. Which date owns it, is the duration derived from two timestamps, and can I see the stored time zone?
2. What did your system store for the night of 28 March 2026? If the answer is 480 minutes rather than 420, every clock change week in your history is wrong, in both directions.
3. How does a four crew rotation resolve to a date? Ask for the cycle length, the anchor date and the offsets, and refuse any answer that involves a maintained spreadsheet of dates.
4. Can I change the deduction policy without editing history? Planned blocks should be stored once and deducted by a flag, so the old number stays reproducible after the policy changes.
5. What happens to a stop that spans two shifts? Ask them to run the same shift under both rules and show you the two availability figures, not a description of the two rules.

The second question is the one that sorts vendors fastest, because a system that cannot answer it is doing wall clock arithmetic everywhere else too.

Our comparison of machine monitoring systems covers how the events get collected, and the production work order data model covers the order side of the join.

How Fabrico helps

Fabrico holds work shifts and collects the machine events this model is built on through PLC connections, IoT sensors and computer vision cameras.

From those events it calculates availability, performance, quality and OEE in real time, tracks downtime by machine and reason, and detects micro stops instead of absorbing them into slow running.

Be clear about the line: Fabrico does not plan production and does not schedule orders.

If your ERP owns the master production calendar, Fabrico measures against it, and the REST API, webhooks, Excel import and export and SAP PM sync keep the two aligned.

Because a full CMMS sits in the same platform, your team can raise a work order or a preventive plan against the machine behind the worst week, and technicians reach the machine record by scanning its QR code from the iOS or Android app.

The AI assistant answers questions about one machine's losses in plain language, and the platform is ISO 27001 certified.

Want your own week reconciled from shift instances instead of argued over in a spreadsheet? Book a 30 minute demo with a Fabrico consultant, no commitment, or contact us with your current calendar tables and we will tell you what is missing.