Change part of a timestamp (not relatively) in PostgreSQL-CodePudding

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.

Possible solution

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?

CodePudding user response：

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

CodePudding user response：

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
        - date_part(unit, tstamp)* format('''1 %s''', unit)::interval
          format('''%s %s''', num, 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)

CodePudding user response：

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.