如何为Snowflake数据库表添加date_created和date_updated列？

1. 添加date_created列 #

你的第一条语句是完全可行的，直接执行即可：

ALTER TABLE test ADD COLUMN date_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

这条语句会为现有行自动填充当前时间戳，后续插入的新行也会默认使用当前时间。

2. 实现date_updated列（自动更新） #

Snowflake不支持DEFAULT ON UPDATE CURRENT_TIMESTAMP这类语法，需要通过以下两种常用方式实现自动更新：

方式一：使用触发器 #

触发器可以在表发生UPDATE操作时，自动同步更新date_updated列。

步骤1：先添加date_updated列

ALTER TABLE test ADD COLUMN date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

步骤2：创建触发器函数

CREATE OR REPLACE FUNCTION update_date_updated()
RETURNS TRIGGER
LANGUAGE JAVASCRIPT
AS $$
    function main(event) {
        const rows = event.rows;
        for (let i = 0; i < rows.length; i++) {
            rows[i].NEW.date_updated = CURRENT_TIMESTAMP();
        }
        return rows;
    }
$$;

步骤3：绑定触发器到表的UPDATE事件

CREATE OR REPLACE TRIGGER test_update_trigger
AFTER UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION update_date_updated();

之后每次更新表中的行，date_updated列都会自动刷新为当前时间戳。

方式二：使用流+任务 #

这种方式适合不需要实时更新，或对触发器性能有顾虑的场景，通过流捕获表的变更，再用任务定期更新时间戳。

步骤1：添加date_updated列

ALTER TABLE test ADD COLUMN date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;

步骤2：创建流捕获UPDATE变更

CREATE OR REPLACE STREAM test_update_stream
ON TABLE test
APPEND_ONLY = FALSE
SHOW_INITIAL_ROWS = FALSE;

步骤3：创建任务执行更新

CREATE OR REPLACE TASK test_update_task
WAREHOUSE = YOUR_WAREHOUSE_NAME -- 替换为你的仓库名称
SCHEDULE = '1 MINUTE' -- 可按需调整执行间隔
AS
UPDATE test t
SET t.date_updated = CURRENT_TIMESTAMP
FROM test_update_stream s
WHERE t.id = s.id; -- 替换为表的主键或唯一标识列

步骤4：启动任务

ALTER TASK test_update_task RESUME;

任务会按照设定的间隔自动更新流中捕获到的变更行的date_updated列。

内容的提问来源于stack exchange，提问作者Justin Battaglia