PostgreSQL - 如何将数字字段中的秒转换为 HHMMSS

将数字字段中的秒转换为 HH:MM:SS 的方法

sql
SELECT EXTRACT(HOUR FROM timestamp 'epoch' + column_name * interval '1 second') || ':' || EXTRACT(MINUTE FROM timestamp 'epoch' + column_name * interval '1 second') || ':' || EXTRACT(SECOND FROM timestamp 'epoch' + column_name * interval '1 second') AS converted_time
FROM table_name;

sql
-- 创建 employees 表格
CREATE TABLE employees (
    id serial PRIMARY KEY,
    name varchar(100),
    total_seconds integer,
    formatted_time varchar(8)
);
-- 插入样本数据
INSERT INTO employees (name, total_seconds)
VALUES ('John Doe', 3600),
       ('Jane Smith', 7200),
       ('Mike Johnson', 1800);
-- 更新 formatted_time 字段
UPDATE employees
SET formatted_time = EXTRACT(HOUR FROM timestamp 'epoch' + total_seconds * interval '1 second') || ':' || EXTRACT(MINUTE FROM timestamp 'epoch' + total_seconds * interval '1 second') || ':' || EXTRACT(SECOND FROM timestamp 'epoch' + total_seconds * interval '1 second');
-- 显示结果
SELECT * FROM employees;