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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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
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:
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?
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