CODEWITHSUNDEEP
sqlpostgresqldatetime
Daniel Magliola
Daniel Magliola

Reputation: 32402

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

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.

Upvotes: 84

Views: 111422

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658032

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.

Upvotes: 137

user330315
user330315

Reputation:

Another option is to use make_interval()

date_field + make_interval(hours => start_time)

Upvotes: 2

Rohil Patel
Rohil Patel

Reputation: 478

If anyone wants to add in time stamp then

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

Upvotes: 5

Willie Z
Willie Z

Reputation: 506

The one worked for me is

SELECT date_field + interval '1' HOUR * start_time

start_time can be any numerical value.

Upvotes: 7

Daniel Magliola
Daniel Magliola

Reputation: 32402

Found the answer, in another SO question:

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

Hope that helps anyone

Upvotes: 11

Related Questions