SQL Server System Temporal Date Precision

I have a system-temporal SQL Server table, and the dates being generated for the row start date seem like they are incorrect.

I have a Flask + SQLAlchemy REST API inserting data into the temporal table. I have two dates in the table - one is application-generated first; the other is the row start date, created when writing the record to the DB. Even if I put time.sleep(1) between the two steps, the application-generated date is after the system-temporal date.

Why isn't the application-generated date always prior to the system-generated date?

Below is an example.

Model:

class TestModel(db.Model):
    __tablename__ = "testtbl"

    id = db.Column(db.Integer, primary_key=True)
    asofdts = db.Column(DATETIME2, nullable=False)

    @classmethod
    def add_system_versioning(
        cls,
        row_eff_dts="row_eff_dts",
        row_exp_dts="row_exp_dts",
        *args,
        **kwargs,
    ):
        """
        Enable system versioning on this table. First, add the row eff/exp dates. Then, add a history table.
        """
        HISTORY_TABLE_NAME = f"dbo.{cls.__tablename__}_history"

        sql = f"""
        ALTER TABLE {cls.__tablename__}
        ADD 
            {row_eff_dts} DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(),
            {row_exp_dts} DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 00:00:00.0000000'),
        PERIOD FOR SYSTEM_TIME ({row_eff_dts}, {row_exp_dts})
        """
        try:
            db.session.execute(text(sql))
            db.session.commit()
        except Exception:
            db.session.rollback()

        sql = f"""
        ALTER TABLE {cls.__tablename__}
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE={HISTORY_TABLE_NAME}))
        """
        try:
            db.session.execute(text(sql))
            db.session.commit()
        except Exception:
            db.session.rollback()

API Resource:

class TestResource(Resource):
    def get(self, *args, **kwargs):
        # setup the table
        db.create_all()
        TestModel.add_system_versioning()
        return {"hello": "world"}

    def post(self, *args, **kwargs):
        # get the current UTC datetime from the database (which is what the row_eff_dts should use too!)
        asofdts = db.session.execute(
            text("SELECT CAST(GETUTCDATE() AS DATETIME2)")
        ).scalar()
        time.sleep(1)
        # create a new row
        obj = TestModel(asofdts=asofdts)
        db.session.add(obj)
        db.session.commit()

        return {"hello": "world"}

Results:

Notice that the row_eff_dts is 5 or more seconds before the asofdts column.

Everything is running locally. SQL Server is running in a Docker container locally as well.

I figured out the solution thanks to Charlieface. The original code ran in a single transaction, and the row start date is the start of the transaction. If I modify the code to get the asofdts in a separate transaction, then everything works as expected.

def post(self, *args, **kwargs):
    # get the current UTC datetime from the database (which is what the row_eff_dts should use too!)
    with db.session.begin():
        asofdts, transaction_id = db.session.execute(
            text(
                "SELECT CAST(GETUTCDATE() AS DATETIME2) AS asofdts, CURRENT_TRANSACTION_ID() AS transaction_id"
            )
        ).first()
        print(transaction_id)


    _, transaction_id = db.session.execute(
        text(
            "SELECT CAST(GETUTCDATE() AS DATETIME2) AS asofdts, CURRENT_TRANSACTION_ID() AS transaction_id"
        )
    ).first()
    print(transaction_id) # this is a new transaction
    # create a new row
    obj = TestModel(asofdts=asofdts)
    db.session.add(obj)
    db.session.commit()

    return {"hello": "world"}

New results: