Postgre using parameter in interval

PostgreSQL how to use parameter in interval

PostgreSQL how to concat interval value ‘2 days’

Angka 2 tersebut ingin kita jadikan variable parameter, maka caranya sbb:

select current_date, current_date + interval '2' || ' days';
--
2012-02-21   2012-02-21 00:00:02 days

atau

-- 0 to 365 is 366 days; 2012 is a leap year.
select ('2012-01-01'::date + (n || ' days')::interval)::date calendar_date
from generate_series(0, 365) n;

Ternyata interval bisa untuk casting ya😀

atau cara lain,

Please try this syntax:

select current_timestamp + ( 2 || ' days')::interval;

or even this one:

select current_timestamp + 2 * interval '1 day';



contoh dalam function,

CREATE OR REPLACE FUNCTION test_param(num_months integer)
  RETURNS SETOF calendar AS
$BODY$

    select * from calendar
    where cal_date <= '2008-12-31 00:00:00'
    and cal_date > date '2008-12-31' - ($1 || ' month')::interval;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;


Reference:
http://stackoverflow.com/questions/9376350/postgresql-how-to-concat-interval-value-2-days
http://stackoverflow.com/questions/7796657/using-a-variable-period-in-an-interval-in-postgres

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s