在PostgreSQL中修改时间戳的一部分(不是相对的)

Question

我在这里看到了类似的问题，例如"我想将时间从13:00更改为17:00";而答案总是"使用'13:00' + INTERVAL '4 hours'">

但是，我需要的是将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中试试。

Answer 2

没有函数来设置时间戳的特定部分，但是您可以使用日期/时间算法来生成您想要的结果。例如:

select d + (15 - extract(hour from d)) * interval '1 hour' from dates

结果:

?column?
------------------------
2021-10-23T15:14:43.000Z
2020-11-02T15:00:34.000Z

参见DB Fiddle的运行示例。

Answer 3

SELECT to_timestamp(to_char(ts, 'YYYY-MM-DD 15:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') as fixed_time
FROM (VALUES ('2021-10-23 13:14:43'::timestamp), ('2020-11-02 10:00:34'::timestamp)) as dates (ts);

2021-10-23 15:14:43.000000 +00:00
2020-11-02 15:00:34.000000 +00:00