CODEWITHSUNDEEP
sqlpostgresqlintervals
IanAlburp
IanAlburp

Reputation: 23

Postgresql using date interval and SELECT statement

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

Upvotes: 2

Views: 1527

Answers (3)

Vivek S.
Vivek S.

Reputation: 21885

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

demo-sqlfiddle

Upvotes: 2

Joe Love
Joe Love

Reputation: 5932

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

Upvotes: 1

Pavel Stehule
Pavel Stehule

Reputation: 45750

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.

Upvotes: 2

Related Questions