New Job Hour History Table

The job_hour_history table holds job hour history data that is used in the calculation of TPM (Total Performance Management) statistics by job and hour to make reporting faster.

Because job statistical data for the current records is considered volatile with a possibility of changing frequently, only the statistical data that occurs before or during the cut‑off period prior to the current time is updated. These updates are performed on an hourly basis. Statistical data that occurs after the cut‑off period is calculated dynamically for TPM statistics and is not maintained in this table.

The default cut-off period is 2 hours, which means updates are done hourly for the 3rd hour prior to the current time. So, for example, at 9 am, the statistical data for the 6‑am hour will be updated. However, statistical data for the 7‑am hour and newer is not maintained. That statistical data will be dynamically calculated upon the TPM statistics data being called.

The job statistical data includes the following fields:

• qty_good

• qty_reject

• runtime

• downtime

• idletime

If an entity can track OEE statistics, the following occurrences will cause data write operations to this table:

• When a job is started, a new job_hour_history record is created

• If a job is currently running, at the top of the hour (during hourly task execution) a new job_hour_history record is created

• A new job is inserted in the job_history table after the fact

• A job duration is modified after the fact and the job end time is within the time period of records in the table

• A utilization event is modified after the fact and the event time is within the time period of records in the table

• Production quantity adjustments are made after the fact and the production event time is within the time period of records in the table

Note the following about hour start and end fields:

• For TPM statistics, the hour boundaries are determined based on the UTC time, for consistency and data integrity.

• For the hour_start_utc and hour_end_utc fields, minutes and seconds are always 0. However, for the hour_start_local and hour_end_local fields, the minutes could be at the 15‑ or 30‑minute mark. For example, the IST timezone is +5:30 hours from UTC. Therefore, the local time will always begin and end at the 30‑minute mark.

Several fields in the table that could be marked as foreign keys are not in case the primary key item is eliminated, but the ID number is still meaningful.

FK from ent_id to ent. (Cascade delete)