How to add a variable number of hours to a date in PostgreSQL?

Question

Apparently, PostgreSQL doesn't have DATEADD, because you can just use the + or - operators.

I need to add a number of hours to a date, which is accomplished like this:

date_field + interval '8.25 hour'

Which is great, but I need the number of hours to come from a field in the table. Would be something like this:

date_field + interval start_time 'hour'

I can't find anywhere how to do this. Is this actually impossible?

I don't mind resorting to ugly hacks like taking the field value and dividing by 3600, multiplying by 86400. Whatever I need to do, but I haven't found any way to do that either.

Erwin Brandstetter · Accepted Answer · 2023-12-13 23:48:49Z

138

To add hours:

SELECT date_field + interval '1 hour' * start_time;

interval can be multiplied by a scalar.
start_time can be any numerical value.
'1 hour' can be shortened to '1h'.

If date_field is data type ...
... timestamp without time zone (= timestamp) or date, the result is timestamp.
... timestamp with time zone (= timestamptz), the result is timestamptz.

edited Dec 13, 2023 at 23:48

answered Mar 1, 2013 at 16:00

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

MC Hammerabi Over a year ago

Erwin Brandstetter strikes again :) Awesome answer

Community · Accepted Answer · 2017-05-23 12:34:39Z

11

Found the answer, in another SO question:

date + interval '1' minute * FLOOR(start_time * 60)

Hope that helps anyone

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Mar 1, 2013 at 14:40

Daniel Magliola

32.7k63 gold badges175 silver badges247 bronze badges

Comments

Willie Z · Accepted Answer · 2017-09-19 21:56:22Z

7

The one worked for me is

SELECT date_field + interval '1' HOUR * start_time

start_time can be any numerical value.

answered Sep 19, 2017 at 21:56

Willie Z

5067 silver badges12 bronze badges

Comments

oguz ismail · Accepted Answer · 2019-05-02 07:42:48Z

6

If anyone wants to add in time stamp then

select time '05:00' - interval '2 hours'

edited May 2, 2019 at 7:42

oguz ismail

51.8k16 gold badges64 silver badges83 bronze badges

answered May 2, 2019 at 7:22

Rohil Patel

4886 silver badges10 bronze badges

Comments

user330315 · Accepted Answer · 2022-07-26 10:10:21Z

2

Another option is to use make_interval()

date_field + make_interval(hours => start_time)

answered Jul 26, 2022 at 10:10

user330315

Collectives™ on Stack Overflow

How to add a variable number of hours to a date in PostgreSQL?

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related