OEE Calculation Record

Overview

The OeeCalculationRecord entity stores pre-computed OEE metrics for a single location over a fixed time period. Records are generated automatically at period close and may also be rebuilt on demand. Each record captures the full set of OEE results — availability, performance, quality, OEE, utilization, TEEP, duration breakdowns, event counts, and production metrics — that would otherwise require a full on-the-fly calculation from raw OeeRecord, OeeStateRecord, and OeeModeRecord data.

Table Structure

The following table outlines the SQL columns for the oee_calculation_records table, providing a brief description of each, along with sample data where applicable.

Column	Type	Uniqueness	Description	Example
id	String (ULID)	Unique	Unique identifier for the entity.	01JAP8RJBN-8ZTPXSGY-J9GSDPE1
enabled	Boolean		If the entity is enabled or not.	true
created_date	DateTime		Date the entity was created.	2025-01-15T08:00:00Z
created_by	String		Person who created the entity.	TamakiMES
modified_date	DateTime		Date the entity was last modified.	2025-01-15T08:00:00Z
modified_by	String		Last person to modify the entity.	TamakiMES
notes	String		Notes about the entity.	This entity has these extra notes
spare1	String		First spare column for additional context.	some extra context 1
spare2	String		Second spare column for additional context.	some extra context 2
spare3	String		Third spare column for additional context.	some extra context 3
location_id	String (ULID)	Unique with period_type, start_date	References the location this record is for. No database constraint. Required.	01JAP8R5RT-3FPXQABY-7KQZT6VF
period_type	String (Enum)	Unique with location_id, start_date	Granularity of this record (HOUR, DAY, WEEK, MONTH, SHIFT). Required.	HOUR
start_date	Instant	Unique with location_id, period_type	Start of the period bucket. Required.	2025-01-15T08:00:00Z
end_date	Instant		End of the period bucket. Required.	2025-01-15T09:00:00Z
shift_record_id	String (ULID)		References the ShiftRecord whose time window was used. No database constraint. Null unless period_type = SHIFT.	01JAP8RJBN-8ZTPXSGY-J9GSDPE1
calculated_date	Instant		Timestamp of when this record was last computed. Required.	2025-01-15T09:01:00Z
availability	Double		Availability as a decimal (0.0–1.0). Null if not enough data to calculate.	0.92
performance	Double		Performance as a decimal (0.0–1.0). Null if not enough data to calculate.	0.87
quality	Double		Quality as a decimal (0.0–1.0). Null if not enough data to calculate.	0.99
oee	Double		Overall Equipment Effectiveness as a decimal (0.0–1.0). Null if not enough data to calculate.	0.79
utilization	Double		Utilization as a decimal (0.0–1.0). Null if not enough data to calculate.	0.95
teep	Double		Total Effective Equipment Performance as a decimal (0.0–1.0). Null if not enough data to calculate.	0.75
total_duration_sec	Double		Total elapsed seconds in the period window. Default 0.	3600.0
scheduled_duration_sec	Double		Seconds the location was in a scheduled production mode. Default 0.	3420.0
running_duration_sec	Double		Seconds the location was in a Running state. Default 0.	3150.0
downtime_duration_sec	Double		Seconds the location was in a Downtime state. Default 0.	270.0
blocked_duration_sec	Double		Seconds the location was in a Blocked state. Default 0.	60.0
starved_duration_sec	Double		Seconds the location was in a Starved state. Default 0.	45.0
idle_duration_sec	Double		Seconds the location was in an Idle state. Default 0.	180.0
scheduled_downtime_duration_sec	Double		Seconds in a scheduled downtime mode. Default 0.	120.0
unscheduled_downtime_duration_sec	Double		Seconds in an unscheduled downtime mode. Default 0.	60.0
running_state_event_count	Integer		Number of Running state transitions in the period. Default 0.	4
downtime_state_event_count	Integer		Number of Downtime state transitions in the period. Default 0.	3
blocked_state_event_count	Integer		Number of Blocked state transitions in the period. Default 0.	1
starved_state_event_count	Integer		Number of Starved state transitions in the period. Default 0.	2
idle_state_event_count	Integer		Number of Idle state transitions in the period. Default 0.	1
scheduled_production_mode_event_count	Integer		Number of scheduled production mode transitions. Default 0.	2
scheduled_downtime_mode_event_count	Integer		Number of scheduled downtime mode transitions. Default 0.	1
unscheduled_downtime_mode_event_count	Integer		Number of unscheduled downtime mode transitions. Default 0.	1
production_count	Double		Total actual units produced in the period. Default 0.	1800.0
expected_production_count	Double		Total expected units based on the standard rate. Default 0.	2100.0
waste_count	Double		Total waste or reject units in the period. Default 0.	18.0
good_count	Double		Total good units in the period. Default 0.	1782.0
average_standard_rate	Double		Average standard production rate across all OEE records in the period (units/minute). Default 0.	35.0
maximum_standard_rate	Double		Maximum standard production rate observed in the period (units/minute). Default 0.	40.0
production_count_uom_id	String (ULID)		No database constraint. References the unit of measure for production and quality counts.	01JAP8R5RT-3FPXQABY-7KQZT6VF

Field Details

No-constraint foreign keys

location_id, shift_record_id, and production_count_uom_id use no database-level constraint. This is intentional: calculation records are historical snapshots and must persist even if the referenced location, shift, or unit of measure is later deleted.

Unique constraint

The combination of (location_id, period_type, start_date) is unique. For SHIFT records, start_date is the ShiftRecord's start time, which is already unique per location, so the same constraint applies naturally.

period_type

Controls the granularity of the time bucket:

• HOUR — one calendar hour (e.g., 14:00–15:00)
• DAY — one calendar day (midnight to midnight in the gateway's configured time zone)
• WEEK — one calendar week (Monday 00:00 to Sunday 23:59)
• MONTH — one calendar month
• SHIFT — a single shift instance, bounded by the referenced ShiftRecord's start and end times

shift_record_id

Populated only when period_type = SHIFT. References the specific ShiftRecord instance whose time window was used as the calculation boundary. Null for all other period types.

OEE percentage fields

availability, performance, quality, oee, utilization, and teep are stored as decimals between 0.0 and 1.0. A value of null indicates there was insufficient data to compute the metric for the period (for example, no scheduled production time).

calculated_date

Records when this row was last computed. If a record is rebuilt (e.g., after a manual rebuild API call), calculated_date is updated to reflect the new computation time while start_date and end_date remain unchanged.