X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D28C79FB3AE for ; Mon, 11 Sep 2006 02:43:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 29492-05-2 for ; Mon, 11 Sep 2006 02:42:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 81BB39FB3B9 for ; Mon, 11 Sep 2006 02:27:47 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d80so2006449pyd for ; Sun, 10 Sep 2006 22:27:46 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=iXT1xlwRLyK9e53vt40kRltOIfqANrinqnj96BTZTMymucukdXQdt8v0MsD8M4dGYOMVAdqavByjAM6LaFfSGb9wZwX/IPgdbHIcafryTajjZPjv7jfrp9Hz8whbqtsPc1sfPckRcVfcoM7sqC8trbUTLJWPN/uuNsMQrXnKlgo= Received: by 10.35.78.13 with SMTP id f13mr8770480pyl; Sun, 10 Sep 2006 22:27:46 -0700 (PDT) Received: by 10.35.80.9 with HTTP; Sun, 10 Sep 2006 22:27:46 -0700 (PDT) Message-ID: Date: Mon, 11 Sep 2006 00:27:46 -0500 From: "Aaron Bono" To: "Mike C" Subject: Re: SELECT and DATE Function question Cc: pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_66501_15097589.1157952466264" References: X-Google-Sender-Auth: 0615f705b4b94285 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.636 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/78 X-Sequence-Number: 22992 ------=_Part_66501_15097589.1157952466264 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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_66501_15097589.1157952466264 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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_66501_15097589.1157952466264--