Обсуждение: SELECT and DATE Function question

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?

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
==================================================================

"Aaron Bono" <postgresql@aranya.com> writes:
> Use:

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

This is a pretty awful way to do it, much better is

        select start_date + number_of_days * '1 day'::interval ...

which reduces to basically one multiplication instead of conversion to
text, text string append, parse interval string value (relying on a
couple of undocumented cast abilities).