25

Is there an equivalent to this T-SQL command in PostgreSQL?

select dateadd(hh,duration_in_hours,start_date) as end_date

I have found only interval keyword with subsequent string, but this terrible construction returns syntax error:

select start_date + interval cast(duration_in_hours as varchar) || ' hours'

It allows only string constant after "interval " keyword. I am sure there must be some similar function in pgsql, but I cannot find it.

Hink
  • 1,054
  • 1
  • 15
  • 31
  • True, it is the same. I couldn't find it using my keywords. – Hink Nov 23 '14 at 23:28
  • 1
    Actually `INTERVAL` is a type, so you can cast a string as such. Your statement could be rewritten as `select start_date + cast(duration_in_hours || ' hours' as INTERVAL)`; I got irritated and wrapped the whole thing into a simple function: `create or replace function dateadd(date,float,char) returns date as $$ begin return $1 + cast($2||' '||$3 as interval); end; $$ language plpgsql;`. Excuse the spacing. – Manngo Mar 13 '16 at 05:37
  • 1
    You can try a UDF. I wrote one here. https://gist.github.com/danielleevandenbosch/1fbe392fd9e2d91952a1b029351e4fcb – Daniel L. VanDenBosch Aug 20 '18 at 19:48

1 Answers1

30

You can do it like this:

select start_date + (duration_in_hours * interval '1 hour') from your_table

See this sample SQL Fiddle

jpw
  • 44,361
  • 6
  • 66
  • 86