Postgres Interval Using Value from Table

Postgres INTERVAL using value from table

Simply multiply the value with an interval:

select create_ts + num_of_day * interval '1' day 
from abc_company;

Since Postgres 9.4 this is easier done using the make_interval() function:

select create_ts + make_interval(days => num_of_day)
from abc_company;

Dynamic (Column Based) Interval

I usually multiply the number by interval '1 day' or similar, e.g.:

select now() + interval '1 day' * a.number_of_days from a;

add time interval with value from selected column in postgres

Thanks for all the answers. Wonderful to be part of community.

I was able to solve this as below.

select start_time - ((tkc + tct)|| ' seconds')::interval + interval '3 hour' as start_time from table

Create interval using a column value postgresql

Unfortunately the "number" for an interval can't be an arbitrary expression, it has to be a string constant (which is a strange choice). You need to use a little workaround:

select days, date(date_event) + (days * interval '1' day) 
from tbl_user_marketing_program as programtable 

But date + integer is also directly supported and the unit is days in that case. So you can als write:

select days, date(date_event) + days 
from tbl_user_marketing_program as programtable 

SQL (postgresql) update a date column using an interval from data in a join table

You can join the tables and multiply the value of the column months by INTERVAL '1 month' in the UPDATE statement:

UPDATE users AS u
SET new_date = u.date + INTERVAL '1 month' * p.months
FROM plans AS p
WHERE p.id = u.plan_id;

See the demo.

Interval field in Postgresql

The added fields specification determines how strings with unmarked quantities are interpreted.

The manual:

When writing an interval constant with a fields specification, or when
assigning a string to an interval column that was defined with a
fields specification, the interpretation of unmarked quantities
depends on the fields. For example INTERVAL '1' YEAR is read as 1
year, whereas INTERVAL '1' means 1 second.

I never use these modifiers for the type definition. Better stick to default intervals and avoid confusion. Always spell out quantity names in string literals (interval '13 seconds' or interval '13 sec'), never interval '13' - the only case where the added type modifier would kick in. I wouldn't want to depend on the table definition to interpret my input. Especially not for such an infrequently used feature.

The optional precision modifier (0) .. (6) only applies to seconds and is only allowed together with that field specification. The manual:

Note that if both fields and p are specified, the
fields must include SECOND, since the precision applies only to the seconds.

It defines how many decimal digits are stored for sub-seconds. If you use it (which I would not), be aware that values are rounded, not truncated (like one might expect).

test=> SELECT '13.555555 sec'::interval(3);
   interval   
--------------
 00:00:13.556    -- !
(1 row)

test=> SELECT '13.555555 sec'::interval(0);
 interval 
----------
 00:00:14        -- !!!
(1 row)

Demo:

db<>fiddle here

Is there added benefit if I set it to second?

No, that's the default anyway. (And I would strongly suggest to stick with the default to avoid confusion.)

Will it still be 16 bytes or will it be less?

The size of an interval value is always 16 bytes.

How to get all the records from a table with 1 minute or 1 hourly interval in postgreSQL

Fetching data every minute from my_table based on the column time :

    SELECT DISTINCT ON (date_trunc('minute', "time" :: timestamp )) *
      FROM my_table
     ORDER BY date_trunc('minute', "time" :: timestamp ), "time"

Fetching data every 30 seconds from my_table based on the column time :

SELECT DISTINCT ON (d.date_interval) *
  FROM my_table AS t
 INNER JOIN 
     ( SELECT generate_series(date_trunc('minute', min(time)), max(time), interval '30 seconds') AS date_interval
         FROM my_table
     ) AS d
    ON d.date_interval <= t.time
   AND d.date_interval + interval '30 seconds' > t.time
 GROUP BY d.date_interval
 ORDER BY d.date_interval, t.time

---

Related Topics