How to change only time while keeping the date same in timestamp type

Question

I have timestamp column used for scheduling calendar events. I would like to change only the time part while retaining the date part untouched.

How can I possibly do it? I'm on postgresql

Current:

2017-07-01 00:00:00

To:

2017-07-01 17:00:00

Answer 1

You can't meaningfully change the time relatively in either direction (forward, or backwards) without potentially impacting the date. Timestamp is a hybrid of both date and time realizing that the two are inseparable in any real world instance. You can not go from 2017-07-01 00:00:00 to 2017-07-01 17:00:00 in a safe fashion through incrementing the time. If you try it, you're making an assumption about what the date was. However, you can set the time portion of a timestamp, dropping the date portion entirely with date_trunc. In this method, you ensure what the time portion is set as (or at least with far more certainty). So long as you don't specifically force a explicit rollover (like adding 25 hours) when you're setting it, you should be good. I see two questions here:

• How do I relatively increment a timestamp, irrespective of rollover?
• How do I set a time portion of a timestamp?

Here is some code showing both approaches

SELECT
  mydate + '7 hours'  AS "+7 hours",
  mydate + '17 hours' AS "+17 hours",
  date_trunc('day', mydate) + '17:00:00' AS "setting to 17 hours",
  date_trunc('day', mydate) + '25:00:00' AS "setting to 25 hours"
FROM (VALUES ('2017-07-02 10:00:00'::timestamp))
  AS t(mydate);

      +7 hours       |      +17 hours      | setting to 17 hours | setting to 25 hours 
---------------------+---------------------+---------------------+---------------------
 2017-07-02 17:00:00 | 2017-07-03 03:00:00 | 2017-07-02 17:00:00 | 2017-07-03 01:00:00
(1 row)

Aside from datetime math, think $time=17, vs $datetime+=17. One of them is far safer and less likely to create roll over. Do you want to increment the current column at whatever it may be by 17 hours, or set it to 17:00:00 on that day?

Answer 2

Add the desired INTERVAL

select date + interval '5 hour'

Have a look at: Date/Time Functions and Operators on Postgres documentation.

select a.MyDate + interval '5 hour' as Result_Date
from (select '2017-07-01 00:00:00'::date myDate) a

|result_date        |
|:------------------|
|2017-07-01 05:00:00|

dbfiddle here