Postgres Pro
Downloads
Home   >   mailing lists

Thread: How do I specify intervals in functions?

How do I specify intervals in functions?

From
"Rob Richardson"
Date:
Greetings!
 
In the help file under date and time functions, I see that intervals can be specified as "interval '3 hours' ".  In a PgAdmin SQL window, I can enter "select interval '3 hours' ", and it will return me "03:00:00", as expected.  I can also enter "select '3 hours'::interval", and get the same result.  Yet neither syntax works inside a function. 
 
declare
     ThreeHours interval;
begin
     ThreeHours = interval '3 hours';  -- throws a syntax error
     ThreeHours = '3 hours'::interval; -- also throws a syntax error
end;
 
So how do I specify an interval in a function?
 
Specifically, I'm trying to do something like the following:
 
    if NewRevisionTime < PredictedEndTime - '08:00:00'::interval then
 
Since both of the shown forms give syntax errors, how do I subtract eight hours from a time???
 
Thank you very much.
 
RobR, who posted this on the novice list but got no answers.
 

Re: How do I specify intervals in functions?

From
Michael Glaesemann
Date:
On 2008-07-31, at 8:36 AM, Rob Richardson wrote:

> declare
>     ThreeHours interval;
> begin
>     ThreeHours = interval '3 hours';  -- throws a syntax error
>     ThreeHours = '3 hours'::interval; -- also throws a syntax error
> end;
>
> So how do I specify an interval in a function?

Works for me:

CREATE FUNCTION
three_hours()
RETURNS interval
STABLE
STRICT
LANGUAGE plpgsql AS $body$
declare
     ThreeHours interval;
begin
     ThreeHours = interval '3 hours';  -- throws a syntax error
     ThreeHours = '3 hours'::interval; -- also throws a syntax error
   RETURN ThreeHours;
end
$body$;
CREATE FUNCTION

test=# select three_hours();
  three_hours
-------------
  03:00:00
(1 row)

test=# select version();
                                                version
-----------------------------------------------------------------------------------------------------
  PostgreSQL 8.2.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
(1 row)

I can't tell as you haven't provided a complete example (always
helpful when debugging), but are you sure you're specifying the
correct language type (plpgsql in your case)?

Michael Glaesemann
michael.glaesemann@myyearbook.com

Re: How do I specify intervals in functions?

From
Volkan YAZICI
Date:
"Rob Richardson" <Rob.Richardson@rad-con.com> writes:
> So how do I specify an interval in a function?

Does this help?

  (vy@[local]:5432/test) [2008-07-31 15:49:54]
  # CREATE OR REPLACE FUNCTION time_cmp_with_1w_offset
      (_l_ts timestamp, _r_ts timestamp)
      RETURNS boolean AS $$
  DECLARE
      _offset interval;
  BEGIN
      _offset = '1 week';
      RETURN (_l_ts < _r_ts - _offset);
  END;
      $$ LANGUAGE plpgsql;

  (vy@[local]:5432/test) [2008-07-31 15:49:28]
  # SELECT time_cmp_with_1w_offset(CAST('2008-07-10' AS timestamp),
  ]                                CAST('2008-07-31' AS timestamp));
   time_cmp_with_1w_offset
  -------------------------
   t
  (1 row)


Regards.

Re: How do I specify intervals in functions?

From
"Rob Richardson"
Date:
My thanks to all for the quick replies.  Now I can't get it not to work! 
 
I guess the computer gremlins that all of us are paid to deny they exist were playing games with me.
 
RobR
 

Re: How do I specify intervals in functions?

From
"Rob Richardson"
Date:
I found my problem.  I trimmed down the function I was having trouble with to the following:
 
CREATE OR REPLACE FUNCTION recalculate_heating_time(int4)
  RETURNS int4 AS
$BODY$
declare
    ChargeNum   ALIAS for $1;
    ChargeRec    charge%rowtype;
    HeatingTime  int4;
    IntervalMinutes float4;
    NewRevisionTime timestamp;
    PredictedEndTime timestamp;
    Interval  interval;
    PredictedSpan interval;
    Message  varchar;
    EightHours  interval;
 
begin
    Message = '07:00:00'::varchar;
    EightHours = '08:00:00'::interval;
    return 1;
end;
This gave me the following error message:
ERROR:  syntax error at or near "$1" at character 22
QUERY:  SELECT  '08:00:00':: $1
CONTEXT:  SQL statement in PL/PgSQL function "recalculate_heating_time" near line 15
I stripped out all the declarations before Message, and the function loaded successfully. 
 
I'm primarily a C++/C# developer, and in those languages, there's no problem differentiating between "Interval" and "interval".  In the Postgres SQL dialect (and probably in all other SQL variants), the two words are treated identically.  The line where I declared a variable named "Interval" of type "interval" screwed everything up.
 
RobR
Using PostGreSQL 8.1 under Windows XP Pro

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rob Richardson
Sent: Thursday, July 31, 2008 8:37 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How do I specify intervals in functions?

Greetings!
 
In the help file under date and time functions, I see that intervals can be specified as "interval '3 hours' ".  In a PgAdmin SQL window, I can enter "select interval '3 hours' ", and it will return me "03:00:00", as expected.  I can also enter "select '3 hours'::interval", and get the same result.  Yet neither syntax works inside a function. 
 
declare
     ThreeHours interval;
begin
     ThreeHours = interval '3 hours';  -- throws a syntax error
     ThreeHours = '3 hours'::interval; -- also throws a syntax error
end;
 
So how do I specify an interval in a function?
 
Specifically, I'm trying to do something like the following:
 
    if NewRevisionTime < PredictedEndTime - '08:00:00'::interval then
 
Since both of the shown forms give syntax errors, how do I subtract eight hours from a time???
 
Thank you very much.
 
RobR, who posted this on the novice list but got no answers.
 

Re: How do I specify intervals in functions?

From
"Rob Richardson"
Date:
One thing I left out of my last post:
 
Thanks to all of you for your assitance.
 
RobR