I believe these are the options you are try -

Instead of using a timestamp, you can directly reference an explicit version number in your queries. This completely avoids dynamic/deterministic date handling or any need for variables.

SELECT * FROM my_table VERSION AS OF <version_number>;

or RESTORE:
RESTORE TABLE my_table TO VERSION AS OF <version_number>;

If you know the exact timestamp you want to use, you can hardcode this value as a literal in your query.

SELECT * FROM my_table TIMESTAMP AS OF '2024-07-06 16:00:00';
RESTORE TABLE my_table TO TIMESTAMP AS OF '2024-07-06 16:00:00';

If your time-travel needs are simple (e.g., "give me yesterday's state"), you can use inline expressions like:

CURRENT_DATE() - 1
date_sub(current_date(), 1)
current_timestamp() - interval 12 hours

These are the only form of calculation allowed within the TIMESTAMP AS OF or RESTORE commands.