如何在PostgreSQL函数中使用Unix时间戳作为入参插入数据

核心需要解决两个问题：

1. 你给出的示例Unix时间戳是13位毫秒级精度，PostgreSQL内置的to_timestamp函数默认接收秒级时间戳参数，需要先做单位转换
2. 毫秒级Unix时间戳数值远超PostgreSQL integer类型的最大值（2147483647），因此入参不能用integer，需要改为bigint类型

修改后的完整函数代码 #

CREATE OR REPLACE FUNCTION public.insert_data(responsetime integer, 
responsetimestamp_ms bigint)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    data_id integer; -- 声明返回用的data_id，删除重复的responsetime声明
BEGIN
    INSERT INTO data(responsetime, dim_date_id) 
    VALUES (
        responsetime, 
        -- 将毫秒级时间戳转换为日期id
        CAST(to_char(to_timestamp(responsetimestamp_ms::double precision / 1000), 'YYYYMMDD') as INT)
    );
    SELECT currval('data_id_seq') INTO data_id;
    RETURN data_id;
END;
$function$;

调用方式 #

直接传入你的Unix毫秒时间戳即可：

select insert_data(
457,
1624978969607);

额外说明 #

• 如果你后续要传入10位的秒级Unix时间戳，删掉转换逻辑里的/1000即可
• 注意统一函数名，你原代码里定义的函数名是insert_date，调用时用的是insert_data，这里统一改成了调用时用的名称

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