Re: Get interval in months

Dear Expert,

I have a function to getting time interval bellow :

create or replace function scmaster.pr_gettimeinterval(time without time zone, time without time zone, numeric(5,2)) returns char(10) As '
declare v_timein alias for $1;
v_timeout alias for $2;
v_timebreak alias for $3;
v_output char(10);
begin
raise notice ''-- BOF --'';
v_output := select ((v_timeout - v_timein) - interval ''v_timebreak minutes'');

raise notice ''-- EOF --'';
return v_output;
end;'
language plpgsql;

and when i compilled from pgAdmin, i got some error message bellow :

ERROR: syntax error at or near "select"
LINE 1: SELECT select (( $1 - $2 ) - interval 'v_timebreak minute...
^
QUERY: SELECT select (( $1 - $2 ) - interval 'v_timebreak minutes')
CONTEXT: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7

********** Error **********

ERROR: syntax error at or near "select"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "pr_gettimeinterval" near line 7

How can i to put my variable "v_timebreak" into function ? so i can send dynamic value for v_timebreak.

please help, thank you.

Alam Surya

----- Original Message -----
From: "Andreas Kretschmer" <akretschmer(at)spamfence(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Gerhard Heift" <ml-postgresql-20081012-3518(at)gheift(dot)de>
Sent: Saturday, November 08, 2008 19:59
Subject: Re: [GENERAL] Get interval in months

> Gerhard Heift <ml-postgresql-20081012-3518(at)gheift(dot)de> schrieb:
>
>> Hello,
>>
>> I want to get an interval in months from two dates:
>>
>> SELECT '2008-02-01'::timestamp - '2008-01-01'::timestamp AS '1 month'
>>
>> Here I want '1 month' and not '31 days' as answer.
>
> How long is a month? 28 days? 31 days? But okay, i will try:
>
> test=*# SELECT justify_days('2008-02-01'::timestamp - '2008-01-01'::timestamp) ;
> justify_days
> --------------
> 1 mon 1 day
> (1 row)
>
> Is this okay for you? PostgreSQL assume 30 days per month.
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>