I saw similar questions here, e. g. "I want to change time from 13:00 to 17:00" and always answers were "use '13:00' + INTERVAL '4 hours'"
BUT, what I need is to SET the date_part value to existing date, without knowing the exact interval size, something opposite to date_part (extract) function.
For example:
-- 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)
Which result will be:
2021-10-23 15:14:43
2020-11-02 15:00:34
As you can see, this cannot be done with simple +/- INTERVAL expression.
What I've already found on SO is:
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)
But this variant does not preserve minutes and seconds.
Although, I can fix this issue, simply adding back minutes, seconds and microseconds of original timestamp:
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)
This will output:
2021-10-23 15:14:43.001240
2020-11-02 15:00:34.000001
And it solves the problem.
But honestly, I'm not very pleased by this solution. Maybe someone knows better variants?
The idea behind the below function is to clear the unit (subtract the corresponding interval) and add a given interval.
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
$$;
Check:
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)
Try it in db<>fiddle.
There's no function to set a specific part of a timestamp, but you can use date/time arithmetic to produce the result you want. For example:
select d + (15 - extract(hour from d)) * interval '1 hour' from dates
Result:
?column?
------------------------
2021-10-23T15:14:43.000Z
2020-11-02T15:00:34.000Z
See running example at DB Fiddle.
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