Thread: problems with date and interval queries.

I am having problems adding an interval to a date in a select statement
where the interval is specified as a selected column. Is this possible?
Below is an example of the query that I'm executing. The query gives and
error at the num_min column in the outer query. It works fine if that is
replaced with the text '90 minutes'. Thank you in advance for all help
provided.

-John

select message_id,
to_char((now() + ((interval num_min) *
(2 ^ (num_fail - (num_fail - fail_count))))),'mon DD YYYY HH24:MI:SS') as
abs_send_date,
fork_count
from (select message_id, number_reattempt_failures as num_fail, resend_count as
fail_count,(to_char(coalesce(minutes_between_reattempt, 90), '999') || ' minutes')
as num_min, last_completion_date as last_date, fork_countfrom pa_mailblast_messagewhere send_state = 'sent'
andnumber_reattempt_failures> resend_count) a 

On Mon, 10 Feb 2003 john@flowlabs.com wrote:

> I am having problems adding an interval to a date in a select statement
> where the interval is specified as a selected column. Is this possible?
> Below is an example of the query that I'm executing. The query gives and
> error at the num_min column in the outer query. It works fine if that is
> replaced with the text '90 minutes'. Thank you in advance for all help
> provided.

interval <stuff> is the form for an interval literal.  If the column is
already an interval, you probably don't need it at all.  If you need to
cast it you should do a cast CAST (num_min AS INTERVAL)

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> interval <stuff> is the form for an interval literal.  If the column is
> already an interval, you probably don't need it at all.  If you need to
> cast it you should do a cast CAST (num_min AS INTERVAL)

I don't believe there is a cast from any numeric type to interval.

I'd recommend the interval-times-float operator.  It should work to
write
num_min * interval '1 min'

This approach has the advantage that it trivially adapts to whatever
unit you happen to have the column stated in (seconds, minutes, days, ...)
        regards, tom lane

On Mon, 10 Feb 2003, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > interval <stuff> is the form for an interval literal.  If the column is
> > already an interval, you probably don't need it at all.  If you need to
> > cast it you should do a cast CAST (num_min AS INTERVAL)
>
> I don't believe there is a cast from any numeric type to interval.

Yeah, but I think he was making an interval string in the subselect,
something like '27 minutes'.

> I'd recommend the interval-times-float operator.  It should work to
> write
>
>     num_min * interval '1 min'
>
> This approach has the advantage that it trivially adapts to whatever
> unit you happen to have the column stated in (seconds, minutes, days, ...)

That's better in any case though :)