Datetime value lost before trigger modification

I'm creating an authentication server with MySQL as the database. I'm using a table to store the refresh tokens, it looks like this:

CREATE TABLE IF NOT EXISTS REFRESH_TOKEN (
    TOKEN VARCHAR(512) NOT NULL,
    EXPIRATION DATETIME
) ENGINE=INNODB;

To ensure correct DATETIME, I'm using the following trigger:

DROP TRIGGER IF EXISTS TG_RT_EXP;
DELIMITER //
CREATE TRIGGER TG_RT_EXP BEFORE INSERT ON REFRESH_TOKEN
FOR EACH ROW 
BEGIN
    SET NEW.EXPIRATION = FROM_UNIXTIME(NEW.EXPIRATION);
END;//
DELIMITER ;

And to test both, here's the used statement (values extracted from jwt.io)

INSERT INTO REFRESH_TOKEN
(TOKEN, EXPIRATION)
VALUES (
    'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiaWF0IjoxNTE2MjM5MDIyfQ.SflKxwRJSMeKKF2QT4fwpMeJf36POk6yJV_adQssw5c',
    1796239022
);

After the INSERT occurs, a warning with ID 1265 states that data is being truncated and every single new row has 1969-12-31 16:00:00 value instead of the corresponding conversion. I remove the trigger and the value that I get is 0000-00-00 00:00:00, so I'm guessing that it has to do with the INSERT value format. Does this has something to do with the trigger, the INSERT value format, the column data type or is it something else?

EDIT: If I try to do

INSERT INTO REFRESH_TOKEN
(TOKEN, EXPIRATION)
VALUES (
    'eyJhbGciOiJIUzI...',
    FROM_UNIXTIME(1796239022)
);

The value is inserted correctly and no warning is shown, why can't this be translated into the trigger?

Solution

why can't this be translated into the trigger?

You try to insert BIGINT value into DATETIME column.

The value is converted to DATETIME according the column datatype before any trigger action. This convertion is performed according to the rule described in Date and Time Literals:

As a number in either YYYYMMDDhhmmss or YYMMDDhhmmss format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

So the provided value 1796239022 is treated as incorrect DATETIME value (it does not match any above format because it produces incorrect MONTH value 96 or 23), and the convertion result is zero date or NULL (you may ensure with SELECT CAST(1796239022 AS DATETIME)). And the value provided for the column is already converted to NULL when the trigger fires. Which results in error, NULL, or zero-date value, depends on the Server SQL Mode.

fiddle