如何用函数将无时区时间戳转为日期并在PostgreSQL建表时调用

原代码问题分析 #

• 参数类型错误：将timestamp without time zone类型的列以TEXT类型传入，完全没必要，直接使用对应类型参数更高效且避免转换错误
• 返回类型错误：RETURNS RECRUIT 试图返回表类型，但未定义对应的复合类型，逻辑混乱
• RETURN语句语法错误：RETURN DATE(recruit_date) FROM recruit; 不符合PLPGSQL语法，函数返回单个值时无需FROM子句

场景1：单个timestamp值转date的函数 #

PLPGSQL版本 #

CREATE OR REPLACE FUNCTION ts_to_date(p_ts TIMESTAMP WITHOUT TIME ZONE)
RETURNS DATE AS $$
BEGIN
    RETURN p_ts::DATE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

更简洁的SQL版本 #

CREATE OR REPLACE FUNCTION ts_to_date(p_ts TIMESTAMP WITHOUT TIME ZONE)
RETURNS DATE AS $$
    SELECT p_ts::DATE;
$$ LANGUAGE sql IMMUTABLE;

调用方式 #

• 转换单个值：

SELECT ts_to_date('2024-05-20 14:30:00'::TIMESTAMP WITHOUT TIME ZONE);

• 查询表中列时转换：

SELECT ts_to_date(recruit_date) AS converted_date FROM recruit;

场景2：创建表时使用函数生成date列 #

可以用生成列自动转换timestamp值为date：

CREATE TABLE recruit (
    id SERIAL PRIMARY KEY,
    recruit_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    recruit_date DATE GENERATED ALWAYS AS (ts_to_date(recruit_ts)) STORED
);

插入数据时只需传入timestamp值，date列会自动生成：

INSERT INTO recruit(recruit_ts) VALUES ('2024-05-20 15:00:00');

场景3：批量修改表中timestamp列的类型为date（函数实现） #

如果需要批量将表中的timestamp列改为date类型，可写如下函数：

CREATE OR REPLACE FUNCTION alter_ts_column_to_date(p_table TEXT, p_column TEXT)
RETURNS VOID AS $$
BEGIN
    EXECUTE format('ALTER TABLE %I ALTER COLUMN %I TYPE DATE USING %I::DATE', p_table, p_column, p_column);
END;
$$ LANGUAGE plpgsql;

调用函数修改列类型：

SELECT alter_ts_column_to_date('recruit', 'recruit_date');

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