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


2017-07-01 00:00:00


2017-07-01 17:00:00

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

  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?

  • 1
    date_trunc('day', mydate) + interval '17hr' gives more freedom. – LoMaPh Jan 17 '20 at 21:58

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.