pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
SELECT and DATE Function question
6+ messages / 4 participants
[nested] [flat]

* SELECT and DATE Function question
@ 2006-09-11 04:53  Mike C <smith.not.western@gmail.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Mike C @ 2006-09-11 04:53 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SELECT and DATE Function question
@ 2006-09-11 05:27  Aaron Bono <postgresql@aranya.com>
  parent: Mike C <smith.not.western@gmail.com>
  1 sibling, 2 replies; 6+ messages in thread

From: Aaron Bono @ 2006-09-11 05:27 UTC (permalink / raw)
  To: Mike C <smith.not.western@gmail.com>; +Cc: pgsql-admin

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

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SELECT and DATE Function question
@ 2006-09-11 05:34  Mike C <smith.not.western@gmail.com>
  parent: Aaron Bono <postgresql@aranya.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Mike C @ 2006-09-11 05:34 UTC (permalink / raw)
  To: pgsql-admin

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.htmlfor more
>
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
>    http://codeelixir.com
> ==================================================================
>

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SELECT and DATE Function question
@ 2006-09-11 07:29  Jason Topaz <topaz@panix.com>
  parent: Mike C <smith.not.western@gmail.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Jason Topaz @ 2006-09-11 07:29 UTC (permalink / raw)
  To: Mike C <smith.not.western@gmail.com>; +Cc: pgsql-admin

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.





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SELECT and DATE Function question
@ 2006-09-11 14:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Aaron Bono <postgresql@aranya.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2006-09-11 14:08 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: Mike C <smith.not.western@gmail.com>; pgsql-admin

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

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: SELECT and DATE Function question
@ 2006-09-11 15:03  Aaron Bono <postgresql@aranya.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Aaron Bono @ 2006-09-11 15:03 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mike C <smith.not.western@gmail.com>; pgsql-admin

On 9/11/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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).



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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2006-09-11 15:03 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-11 04:53 SELECT and DATE Function question Mike C <smith.not.western@gmail.com>
2006-09-11 05:27 ` Aaron Bono <postgresql@aranya.com>
2006-09-11 05:34   ` Mike C <smith.not.western@gmail.com>
2006-09-11 14:08   ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-11 15:03     ` Aaron Bono <postgresql@aranya.com>
2006-09-11 07:29 ` Jason Topaz <topaz@panix.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox