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

Understanding data types

When using the + operator with interval data type, the resulting data type depends on your_date. If your_date is a timestamp, the result is a timestamp. If your_date is a date, the result will be a timestamp without the time zone. Always use numerical values for hours_to_add to avoid runtime errors.

Dealing with fractional hours

In case you need to add non-integer hours, say 1.5 hours, use either of these:

-- Who said time travel is not precise?
SELECT your_date + make_interval(hours => hours_to_add);
-- OR
SELECT your_date  + (interval '1 hour' * hours_to_add);


The latter approach assumes hours_to_add as a floating point number, thus permitting fractional hours.

Shorthand notation

To make your code shorter and more readable, use shorthand "1 hour" to "1h".

-- Doc, are we going back in time?
SELECT your_date + interval '1h' * hours_to_add;


This approach improves query readability when you have multiple interval calculations.

Handling complex cases

Working with non-standard intervals

Adding a variable interval which doesn't fit whole hours needs some creativity. Use make_interval function:

-- Tickets to odd times welcome!
SELECT your_date + make_interval(hours => FLOOR(start_time));


For fractional hours, convert to minutes:

SELECT your_date + interval '1' minute * FLOOR(start_time * 60);


Dealing with time zones

If you are adding hours to a timestamp with time zone, the output reflects the UTC equivalent. Convert the timezone appropriately before addition for local time operations.

Common traps and shortcuts

Evading timestamp overflow

Adding astronomical hours might hit PostgreSQL's timestamp limit. Always validate your input to prevent a journey to the end of time.

Ensuring precision

Floating point arithmetic can cause minute inaccuracies. Use integer values or cast to numeric if precision is important and hours can be fractional.

Working with DST

Manipulating timestamp with time zone might cause pitfalls during Daylight Saving Time shifts. Use AT TIME ZONE to normalize these changes.