How to Set TTL Attribute for BIGINT Timestamps

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: BIGINT 时间戳如何设置TTL属性

| username: TiDBer_qgMRUyIV

[TiDB Usage Environment] Production Environment
[TiDB Version] V6.5.0
I want to set TTL to automatically delete data, but my data table only has a BIGINT attribute for the second timestamp. When I use the following SQL statement to set it, an error is reported. How should I handle it?

CREATE TABLE t2 (
    id int PRIMARY KEY,
    created_at BIGINT(16)
) TTL = FROM_UNIXTIME(created_at) + INTERVAL 3 MONTH;

The error message is as follows:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 4 column 23 near "(created_at) + INTERVAL 3 MONTH"
2
| username: 有猫万事足 | Original post link

6.5 feels like there’s no solution.

7.1 can be used together with generated columns, which makes it easier. Creating a virtual generated column will solve it.

3
| username: tidb菜鸟一只 | Original post link

Try this:

CREATE TABLE t2 (
id INT PRIMARY KEY,
created_at BIGINT(16),
expire_at TIMESTAMP AS (
FROM_UNIXTIME(created_at)
)
) TTL = `expire_at` + INTERVAL 3 MONTH;
4
| username: 有猫万事足 | Original post link

This method is really good, and it can be added at any time. v7.3

5
| username: ajin0514 | Original post link

Try upgrading the version.