Re: Date Arithmetic in PL/pgSql

I am closer, but still not there. By computed, I mean that the timestamp
and the interval (shown below as '2005-08-08 16:15:30' and '310 seconds'
need to be computed from PL/pgSQL functions and variables in a function
named getNeighborState(integer).

The fields I am using are:

DECLARE
work_timestamp timestamp without time zone; (the '2005-08-08 16:15:30')
neighbor_seconds integer; (contains the '310' [seconds]

My code says:
select date_trunc('seconds',localtimestamp)::timestamp -
neighbor_seconds::integer;

Looks like it wants me to cast, but not sure how to cast this:
ERROR: operator does not exist: timestamp without time zone - integer
HINT: No operator matches the given name and argument type(s). You may need
to add explicit type casts.
CONTEXT: SQL statement "SELECT
date_trunc('seconds',localtimestamp)::timestamp - $1 ::integer"

Haven't done casts before; if that is what is needed, still not sure how to
create the cast properly from the instructions in the Reference Manual.

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, August 08, 2005 5:09 PM
To: John DeSoi
Cc: Lane Van Ingen; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Date Arithmetic in PL/pgSql

John DeSoi <desoi(at)pgedit(dot)com> writes:
> On Aug 8, 2005, at 4:23 PM, Lane Van Ingen wrote:
>> I am trying to do some simple date arithmetic.

> select '2005-08-08 16:15:30'::timestamp - '310 seconds'::interval;

I think the "computed" part is the bit that's missing. Maybe he wants

select '2005-08-08 16:15:30'::timestamp - (expression) * '1 sec'::interval;

This should work for pretty much any numeric expression.

regards, tom lane