PostgreSQL中直接设置timestamp指定时间部分值的实现方案咨询

最优实现方案 #

你可以直接计算目标值与原时间对应字段的差值，直接叠加对应间隔即可，不需要截断后拼接分秒等信息，所有原有精度都会完整保留，写法非常简洁：

SELECT 
  date + (15 - EXTRACT(hour FROM date)) * INTERVAL '1 hour' AS adjusted_date
FROM (VALUES 
  ('2021-10-23 13:14:43.001240'::timestamp), 
  ('2020-11-02 10:00:34.000001')
) AS dates (date);

运行后即可得到你预期的输出：

2021-10-23 15:14:43.001240
2020-11-02 15:00:34.000001

这个方案同时比你当前的写法性能更高，仅需要一次字段提取和算术运算，没有多余的字符串拼接、类型转换操作。逻辑也通用，修改其他时间字段也可以直接套用：

• 设置分钟为30：date + (30 - EXTRACT(minute FROM date)) * INTERVAL '1 minute'
• 设置日期为当月1号：date + (1 - EXTRACT(day FROM date)) * INTERVAL '1 day'

如果需要你示例中提到的通用date_set函数，可以自定义一个PL/pgSQL函数实现：

CREATE OR REPLACE FUNCTION date_set(part text, ts timestamp, val int)
RETURNS timestamp AS $$
BEGIN
  RETURN ts + (val - EXTRACT(part FROM ts)) * (('1 ' || part)::interval);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

定义完成后即可直接调用：

SELECT date_set('hour', date, 15)
FROM (VALUES 
  ('2021-10-23 13:14:43'::timestamp), 
  ('2020-11-02 10:00:34')
) AS dates (date);

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