Change part of a timestamp (not relatively) in PostgreSQL

Question

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?

klin · Accepted Answer · 2021-10-23 10:36:52Z

3

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.

edited Oct 23, 2021 at 10:36

answered Oct 23, 2021 at 2:12

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stefanov.sm Over a year ago

A true gem (with a few redundant single quotes :)).

klin Over a year ago

Yes, format() gives a text, extra quotes are redundant. Corrected, thanks.

Stefanov.sm Over a year ago

Well, they are very small and squeeze easily through tight cables, so no big deal :)

The Impaler · Accepted Answer · 2021-10-23 02:25:22Z

1

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.

answered Oct 23, 2021 at 2:25

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Comments

PrasadU · Accepted Answer · 2021-10-23 02:25:47Z

1

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

answered Oct 23, 2021 at 2:25

PrasadU

2,4361 gold badge12 silver badges12 bronze badges

1 Comment

The Impaler Over a year ago

That's kind of cheating, but heck it works. +1 I learned something new about PostgreSQL today.

Collectives™ on Stack Overflow

Change part of a timestamp (not relatively) in PostgreSQL

Possible solution

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Possible solution

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related