Received: from mail.disney.com (IDENT:0@mail.disney.com [204.128.192.15]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA05679 for ; Mon, 26 Jul 1999 19:01:09 -0400 (EDT) (envelope-from drose@not.rd.wdi.disney.com) Received: from pain10.corp.disney.com (root@pain10.corp.disney.com [153.7.110.100]) by mail.disney.com (8.9.1/8.9.1) with SMTP id QAA22854 for ; Mon, 26 Jul 1999 16:01:06 -0700 (PDT) Received: from ford.wdi.disney.com by pain.corp.disney.com with ESMTP for pgsql-sql@postgresql.org; Mon, 26 Jul 1999 16:00:35 -0700 Received: from nufat.rd.wdi.disney.com (nufat [206.16.11.70]) by ford.wdi.disney.com (8.9.1/8.9.1/D1) with ESMTP id QAA09179 for ; Mon, 26 Jul 1999 16:00:46 -0700 (PDT) Received: from not.rd.wdi.disney.com (not [255.255.255.255]) by nufat.rd.wdi.disney.com (8.9.1/8.9.0) with SMTP id QAA215828 for <@nufat.rd.wdi.disney.com:pgsql-sql@postgresql.org>; Mon, 26 Jul 1999 16:00:45 -0700 (PDT) Received: (from drose@localhost) by not.rd.wdi.disney.com (950413.SGI.8.6.12/950213.SGI.AUTOCF) id QAA23482; Mon, 26 Jul 1999 16:00:45 -0700 Date: Mon, 26 Jul 1999 16:00:45 -0700 Message-Id: <199907262300.QAA23482@not.rd.wdi.disney.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit From: David Rose To: pgsql-sql@postgresql.org Subject: timespan arithmetic X-Mailer: VM 6.33 under Emacs 19.34.2 Hello. I recently posted this question on pgsql-novice and received no answer. Undaunted, I'm trying again on pgsql-sql. Perhaps someone knows at least where to start looking for an answer to this problem. It's not in the FAQ, and I couldn't find an answer in the mailing list archives. I have a table that includes a column of type timestamp. I'd like to formulate a query that returns, say, all rows no more than 24 hours old, based on the timestamp. Trial and error got me this far (and a perusal of the mailing list archives confirmed it): SELECT * FROM table WHERE loaded_time >= 'now'::timestamp - '1 days'::timespan ORDER BY loaded_time DESC; This works fine in PostgreSQL 6.4.2. However, another user of my software, who has PostgreSQL 6.3.2, reports that he gets the following error message: ERROR: There is no operator '-' for types 'timestamp' and 'timespan' You will either have to retype this query using an explicit cast, or you will have to define the operator using CREATE OPERATOR So. Is there a correct solution to this problem that works for all versions of PostgreSQL--or at least, PostgreSQL versions 6.3.2 and higher? Barring that, is there at least a solution at all for 6.3.2, which may or may not be different from the solution for 6.4.2 and higher? Many thanks. David