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