X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 68EC69FB3B9 for ; Mon, 11 Sep 2006 02:43:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 35867-03-5 for ; Mon, 11 Sep 2006 05:43:15 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id 6A6879FB3D2 for ; Mon, 11 Sep 2006 02:34:04 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2008897pyd for ; Sun, 10 Sep 2006 22:34:03 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=a4gBR5VlY8hFIqt6PhrKSWZox0/yLOY8qu0ef19Nqpuv1468cxBTJTT3pwmbmaciKsHvBqiMqhlegKBaNGBSiVfPW/Lv10YyTSeVd4lk4ZJkKktDPMekzUA2lnTKDd9qndjBgD4WeGfepZvx/7+HtKAkkna6CHbFaw7zVHGS19o= Received: by 10.65.43.5 with SMTP id v5mr1206299qbj; Sun, 10 Sep 2006 22:34:03 -0700 (PDT) Received: by 10.65.54.11 with HTTP; Sun, 10 Sep 2006 22:34:03 -0700 (PDT) Message-ID: Date: Mon, 11 Sep 2006 17:34:03 +1200 From: "Mike C" To: pgsql-admin@postgresql.org Subject: Re: SELECT and DATE Function question In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_156801_10391035.1157952843330" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.334 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_50_60, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/79 X-Sequence-Number: 22993 ------=_Part_156801_10391035.1157952843330 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline That works great, thank you! Mike On 9/11/06, Aaron Bono wrote: > > On 9/10/06, Mike C wrote: > > > Hi, > > > > I'm trying to calculate an expiration date by adding the number of days > > onto the start date. i.e. select start_date + number_of_days from blah; > > > > create table blah (start_date timestamp, number_of_days integer); > > insert into blah values (current_timestamp, 25); > > select start_date + number_of_days from blah; > > > > The error I get is: > > > > 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. > > > > But according to http://www.postgresql.org/docs/8.1/static/functions-datetime.html > > the + operator should support integers and treat them as days ( date > > '2001-09-28' + integer '7'). Obviously typing a constant into the query is a > > lot different from using the value of a column, but I would have thought it > > would work. > > > > What is the correct way to make this calculation? > > > > > Use: > > select start_date + (cast(number_of_days as text) || ' days')::interval > from blah > > See http://www.postgresql.org/docs/8.1/interactive/functions-datetime.htmlfor more > > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > http://codeelixir.com > ================================================================== > ------=_Part_156801_10391035.1157952843330 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline That works great, thank you!

Mike

On 9/11/06, Aaron Bono <postgresql@aranya.com> wrote:
On 9/10/06, Mike C <smith.not.western@gmail.com > wrote:
Hi,

I'm trying to calculate an expiration date by adding the number of days onto the start date. i.e. select start_date + number_of_days from blah;

create table blah (start_date timestamp, number_of_days integer);
insert into blah values (current_timestamp, 25);
select start_date + number_of_days from blah;

The error I get is:

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.

But according to http://www.postgresql.org/docs/8.1/static/functions-datetime.html the + operator should support integers and treat them as days ( date '2001-09-28' + integer '7'). Obviously typing a constant into the query is a lot different from using the value of a column, but I would have thought it would work.

What is the correct way to make this calculation?

 
Use:

select start_date + (cast(number_of_days as text) || ' days')::interval from blah

See http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html for more

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

------=_Part_156801_10391035.1157952843330--