Re: 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?

Cheers,

Mike

On 9/10/06, Mike C <smith(dot)not(dot)western(at)gmail(dot)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.htmlfor
more

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

That works great, thank you!

Mike

On 9/11/06, Aaron Bono <postgresql(at)aranya(dot)com> wrote:
>
> On 9/10/06, Mike C <smith(dot)not(dot)western(at)gmail(dot)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.htmlfor more
>
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> http://codeelixir.com
> ==================================================================
>

On Mon, 2006-09-11 at 16:53 +1200, Mike C wrote:

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

That's because your data type was "timestamp", not "date". I don't
believe the documentation claims the + operator works on a timestamp and
an integer. But it does claim (correctly) that it works on a date and
an integer. The following example does work:

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

Hope that helps a little, at least to explain the apparent disconnect
from the documentation.

"Aaron Bono" <postgresql(at)aranya(dot)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).

But the real question here is whether you actually want sub-day
precision in your result. Should the column have been 'date' rather
than 'timestamp' to begin with? If not, what behavior are you expecting
at DST boundaries?

regards, tom lane

On 9/11/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Aaron Bono" <postgresql(at)aranya(dot)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).

I knew there was a better way but I forgot what it was and couldn't find it
in the documentation.

Could the documentation be updated with this example? This question appears
every month or so and I know it is very useful to many people.

Thanks!

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