pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblems with date and interval queries.
4+ messages / 3 participants
[nested] [flat]
* problems with date and interval queries.
@ 2003-02-10 18:05 john@flowlabs.com
2003-02-10 18:14 ` Re: problems with date and interval queries. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: john@flowlabs.com @ 2003-02-10 18:05 UTC (permalink / raw)
To: pgsql-sql
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_count
from pa_mailblast_message
where send_state = 'sent' and
number_reattempt_failures > resend_count) a
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problems with date and interval queries.
2003-02-10 18:05 problems with date and interval queries. john@flowlabs.com
@ 2003-02-10 18:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-10 18:21 ` Re: problems with date and interval queries. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2003-02-10 18:14 UTC (permalink / raw)
To: john@flowlabs.com; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problems with date and interval queries.
2003-02-10 18:05 problems with date and interval queries. john@flowlabs.com
2003-02-10 18:14 ` Re: problems with date and interval queries. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-02-10 18:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-10 19:03 ` Re: problems with date and interval queries. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2003-02-10 18:21 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: john@flowlabs.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: problems with date and interval queries.
2003-02-10 18:05 problems with date and interval queries. john@flowlabs.com
2003-02-10 18:14 ` Re: problems with date and interval queries. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-10 18:21 ` Re: problems with date and interval queries. Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-10 19:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-02-10 19:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: john@flowlabs.com; pgsql-sql
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 :)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-02-10 19:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-10 18:05 problems with date and interval queries. john@flowlabs.com
2003-02-10 18:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-02-10 18:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-10 19:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.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