SYSTEM_TIME period

The SYSTEM_TIME period columns for a system-period temporal table indicate when the version of a row is current.

The SYSTEM_TIME period contains a pair of TIMESTAMP(12) columns whose values are generated by the database manager. The columns must be defined as NOT NULL with an applicable GENERATED ALWAYS AS option. The begin column of the period must be a row-begin column and the end column of the period must be a row-end column.

CREATE TABLE policy_info
(
 policy_id       CHAR(4) NOT NULL,
 coverage        INT NOT NULL,
 sys_start 	 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
 sys_end	 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
 ts_id		 TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
 PERIOD SYSTEM_TIME (sys_start, sys_end)
) IN policy_space;

Row-begin column: This column represents the time when the row data became current. The database manager generates a value for this column by using a reading of the system clock at the moment it executes the first data change statement in the transaction that generates the row. If multiple rows are inserted or updated within a single SQL transaction, the values for the row-begin column are the same for all the impacted rows. The values for these row-begin columns are unique from the values generated for the row-begin columns for other transactions. A row-begin column is required as the begin column of a SYSTEM_TIME period, which must be defined for each system-period temporal table.; When an existing regular table is altered to make it a system-period temporal table, a row-begin column is added to the table. The row-begin column is populated with a default of 0001-01-01-00.00.00.000000000000 for the TIMESTAMP(12) data type value for any existing rows.
Row-end column: This column represents the time when the row data was no longer current. For rows in a history table, the value in the row-end column represents when the row was added to the history table. The rows in the system-period temporal table are by definition current, so the row-end column is populated with a default value for the TIMESTAMP(12) data type (for example: 9999-12-30-00.00.00.000000000000). A row-end column is required as the end column of a SYSTEM_TIME period, which must be defined for each system-period temporal table.; When an existing regular table is altered to make it a system-period temporal table, a row-end column is added to the table. The row-end column is populated with the a value for the TIMESTAMP(12) data type (default value: 9999-12-30-00.00.00.000000000000) for any existing rows. The value is intentionally one day earlier than the maximum possible time stamp to avoid a possible overflow to a 5-digit year when converting the value to a different time zone.

Since row-begin and row-end are generated columns, there is no implicit check constraint generated for SYSTEM_TIME that ensures that the value for an end column is greater than the value for its begin column in a system-period temporal table. This lack of a check constraint differs from an application-period temporal table where there is a check constraint associated with its BUSINESS_TIME. A row where the value for the end column is less than the value for the begin column cannot be returned when a period-specification is used to query the table. You can define a constraint to guarantee that the value for end column is greater than the value for begin column. This guarantee is useful when supporting operations that explicitly input data into these generated columns, such as a load operation.

The systime_period_adj database configuration parameter is used to specify what action to take when a history row for a system-period temporal table is generated with an end column value that is less than the value for begin column.