Re: Get interval in months - Mailing list pgsql-general
| From | dbalinglung |
|---|---|
| Subject | Re: Get interval in months |
| Date | |
| Msg-id | 9F7D0D300C5946828BC303BD63A27C88@alam Whole thread Raw |
| In response to | Get interval in months (Gerhard Heift <ml-postgresql-20081012-3518@gheift.de>) |
| Responses |
Re: Get interval in months
Re: Get interval in months |
| List | pgsql-general |
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'');
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;
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
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
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@spamfence.net>
Cc: "Gerhard Heift" <ml-postgresql-20081012-3518@gheift.de>
Sent: Saturday, November 08, 2008 19:59
Subject: Re: [GENERAL] Get interval in months
>
>> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
pgsql-general by date: