Skip site navigation (1) Skip section navigation (2)

Re: dynamic interval in plpgsql

From: joseph speigle <joe(dot)speigle(at)jklh(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: dynamic interval in plpgsql
Date: 2004-03-30 03:57:19
Message-ID: 20040330035719.GB3229@www.sirfsup.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Mon, Mar 29, 2004 at 12:51:11PM +0200, Thilo Hille wrote:
> hi,
> i work on a stored procedure which does some timespecific calculations 
> in plpgsql.
> in a loop i want  to increase a timestamp by a changing interval. but i 
> found no way to assign a variable to INTERVAL .
> finally i used plpython for the function but i still wonder if it could 
> be done with plpgsql?
> 
> regards thilo

You can do that with some select statements, non?
run the following and hope it helps
------------------------------------
create sequence test_interval_id_seq;
create table test_interval (
id integer UNIQUE DEFAULT nextval('test_interval_id_seq'),
formulation varchar(100),
interval_col interval,
check (interval_col >= '0 day'::interval)
);
insert into test_interval (formulation,interval_col) values ('1 day','1 day');
insert into test_interval (formulation, interval_col) values ('timestamp ''today'' - timestamp ''tomorrow''',timestamp 'today'- timestamp 'tomorrow');
insert into test_interval (formulation, interval_col) values (
'timestamp ''today''- timestamp ''yesterday''',
timestamp 'today'- timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'timestamp ''tomorrow''- timestamp ''yesterday''',
timestamp 'tomorrow'- timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'now() - timestamp ''yesterday''',
now() - timestamp 'yesterday');
insert into test_interval (formulation, interval_col) values (
'timestamp ''today'' + interval ''1 month 04:01''',
timestamp 'today' + interval '1 month 04:01');
select * from test_interval;
drop table test_interval;
drop sequence test_interval_id_seq;





-- 
joe speigle
www.sirfsup.com

In response to

pgsql-novice by date

Next:From: Reshat SabiqDate: 2004-03-30 04:05:01
Subject: Re: Images in Database
Previous:From: joseph speigleDate: 2004-03-30 03:52:03
Subject: Re: ERROR: plpgsql: permission denied

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group