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@sss.pgh.pa.us]
Sent: Monday, August 08, 2005 5:09 PM
To: John DeSoi
Cc: Lane Van Ingen; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Date Arithmetic in PL/pgSql


John DeSoi <desoi@pgedit.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