sql - 更改 PostgreSQL 中时间戳的一部分(不是相对)

Question

我在这里看到了类似的问题，例如。 G。 “我想将时间从 13:00 更改为 17:00”，答案始终是“使用 '13:00' + INTERVAL '4 小时'”

但是，我需要的是将 date_part 值设置为现有日期，而不知道确切的间隔大小，与 date_part 相反(extract) 函数。

例如:

-- NOT A REAL FUNCTION
SELECT date_set('hour', date, 15)
FROM (VALUES ('2021-10-23 13:14:43'::timestamp), ('2020-11-02 10:00:34')) as dates (date)

结果将是:

2021-10-23 15:14:43
2020-11-02 15:00:34

如您所见，这不能通过简单的 +/- INTERVAL 表达式来完成。

可能的解决方案

我已经在 SO 上发现的是:

SELECT date_trunc('day', date) + INTERVAL '15 hour'
FROM (VALUES ('2021-10-23 13:14:43'), ('2020-11-02 10:00:34')) as dates (date)

但是这个变体不保留分钟和秒。

不过，我可以解决这个问题，只需添加原始时间戳的分钟、秒和微秒即可:

SELECT date_trunc('day', date) + INTERVAL '15 hour'
  + (extract(minute from date) || ' minutes')::interval
  + (extract(microsecond from date) || ' microseconds')::interval
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

它解决了问题。

但说实话，我对这个解决方案不太满意。也许有人知道更好的变体？

Answer 1

以下函数背后的想法是清除单位(减去相应的间隔)并添加给定的间隔。

create or replace function timestamp_set(unit text, tstamp timestamp, num int)
returns timestamp language sql immutable as $$
    select tstamp+ (num- date_part(unit, tstamp))* format('1 %s', unit)::interval
$$;

检查:

select
    date,
    timestamp_set('hour', date, 15) as hour_15,
    timestamp_set('min', date, 33) as min_33,
    timestamp_set('year', date, 2022) as year_2022
from (
    values 
        ('2021-10-23 13:14:43'::timestamp), 
        ('2020-11-02 10:00:34')
    ) as dates (date)

        date         |       hour_15       |       min_33        |      year_2022
---------------------+---------------------+---------------------+---------------------
 2021-10-23 13:14:43 | 2021-10-23 15:14:43 | 2021-10-23 13:33:43 | 2022-10-23 13:14:43
 2020-11-02 10:00:34 | 2020-11-02 15:00:34 | 2020-11-02 10:33:34 | 2022-11-02 10:00:34
(2 rows)

尝试一下db<>fiddle.