I have a table with 3 columns - a timestamp, a real, and a string. The String columns holds various time frequency values (like Minutes, Hours, Days). I want to take the value in my timestamp column and add to it the values in the other columns. For example I'd like to do something like
select timestamp '2015-04-20 01:00' + interval '2 hours'
if I had a table with the following columns and values
action_start_time rate frequency
2015-04-20 01:00 2 hours
You can use following query to convert rate and frequency columns to interval
select format('%s %s',rate,frequency)::interval
from actions
and then get the exact timestamp just by adding the interval with action_start_time column
select action_start_time + format('%s %s',rate,frequency)::interval my_timestamp
from actions
Simply:
SELECT action_start_time + rate * ('1'|| frequency)::interval
The all trick is in a using multiplication of some interval value:
postgres=# select 3 * interval '1 hour';
┌──────────┐
│ ?column? │
╞══════════╡
│ 03:00:00 │
└──────────┘
(1 row)
You can simply create any interval value without necessity to manipulate with strings. Surely, should be better, if your column frequency is native PostgreSQL interval type.
select action_start_time + (rate::varchar||' '||frequency)::interval
Start with the action_start_time then take the rate (cast to a character) and append a space and then the frequency to achieve a string like '1 hours' or '3 days' etc.. then cast that string to an interval and add that to the original action_start_time
