Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem to add a delay to a date

Problem to add a delay to a date

From
Thierry Missimilly
Date:
Hi,

I try to create a function in charge of adding a delay to a timestamp.
Here are the stange results on Postgres 7.3.1.
working with psql :

test=# select now() + '60' as time;
                time
--------------------------------------
2003-03-24 16:04:53.8680551+01
(1 row)


and when i want to create a function :

test=# create function addtime() returns timestamp as 'select now() +
''60'' ' language 'sql';
ERROR: return type mismatch in function: declared to return timestamp
without time zone, returns timestamp with time zone


Where is the error .

    Thierry
Attachment

Re: Problem to add a delay to a date

From
Richard Huxton
Date:
On Thursday 03 Apr 2003 12:28 pm, Thierry Missimilly wrote:
> Hi,
>
> I try to create a function in charge of adding a delay to a timestamp.
> Here are the stange results on Postgres 7.3.1.
> working with psql :
>
> test=# select now() + '60' as time;
>                 time
> --------------------------------------
> 2003-03-24 16:04:53.8680551+01
> (1 row)
>
>
> and when i want to create a function :
>
> test=# create function addtime() returns timestamp as 'select now() +
> ''60'' ' language 'sql';
> ERROR: return type mismatch in function: declared to return timestamp
> without time zone, returns timestamp with time zone

The error is precisely as stated. The function is defined as returning
"timestamp" (without time zone) and now() returns "timestamp with time zone"
- hence the difficulty.

You'll either need to change the function's return type or cast the output of
now().
--
  Richard Huxton

Re: Problem to add a delay to a date

From
Stephan Szabo
Date:
On Thu, 3 Apr 2003, Thierry Missimilly wrote:

> I try to create a function in charge of adding a delay to a timestamp.
> Here are the stange results on Postgres 7.3.1.
> working with psql :
>
> test=# select now() + '60' as time;
>                 time
> --------------------------------------
> 2003-03-24 16:04:53.8680551+01
> (1 row)
>
>
> and when i want to create a function :
>
> test=# create function addtime() returns timestamp as 'select now() +
> ''60'' ' language 'sql';
> ERROR: return type mismatch in function: declared to return timestamp
> without time zone, returns timestamp with time zone

The addition works fine in both cases, but the type of now() + '60' isn't
the same type you defined the function to return.  now() returns a
"timestamp with time zone" not a "timestamp without time zone" (which is
what timestamp is).  You should be able to just change the return type to
match.