pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT 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>
2006-09-11 05:27 ` Re: SELECT and DATE Function question Aaron Bono <postgresql@aranya.com>
2006-09-11 07:29 ` Re: SELECT and DATE Function question Jason Topaz <topaz@panix.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 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 ` Re: SELECT and DATE Function question Mike C <smith.not.western@gmail.com>
2006-09-11 14:08 ` Re: SELECT and DATE Function question Tom Lane <tgl@sss.pgh.pa.us>
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 04:53 SELECT and DATE Function question Mike C <smith.not.western@gmail.com>
2006-09-11 05:27 ` Re: SELECT and DATE Function question Aaron Bono <postgresql@aranya.com>
@ 2006-09-11 05:34 ` Mike C <smith.not.western@gmail.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 04:53 SELECT and DATE Function question Mike C <smith.not.western@gmail.com>
2006-09-11 05:27 ` Re: SELECT and DATE Function question Aaron Bono <postgresql@aranya.com>
@ 2006-09-11 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-11 15:03 ` Re: SELECT and DATE Function question 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 04:53 SELECT and DATE Function question Mike C <smith.not.western@gmail.com>
2006-09-11 05:27 ` Re: SELECT and DATE Function question Aaron Bono <postgresql@aranya.com>
2006-09-11 14:08 ` Re: SELECT and DATE Function question Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-11 15:03 ` Aaron Bono <postgresql@aranya.com>
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
* Re: SELECT and DATE Function question
2006-09-11 04:53 SELECT and DATE Function question Mike C <smith.not.western@gmail.com>
@ 2006-09-11 07:29 ` Jason Topaz <topaz@panix.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
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