> Ah. Well, with something like that, you really can't speak in terms of
> modulo, because months aren't all the same length. I'd be inclined to
> solve this with a small plpgsql function, along the lines of
> curdate := start_date;
> while curdate < test_date loop
> curdate := curdate + repeat_interval;
> end loop;
> return (curdate = test_date);
> Kinda grotty but there probably isn't any cleaner solution that really
> works for arbitrary intervals.
>
But that will not scale well - it is horribly inefficient. In case
anybody else needs the same code, here is my optimized version, in using
an SQL function rather than a procedural language:
CREATE OR REPLACE FUNCTION isrepeatdate(date,date,interval) RETURNS bool
AS $$
-- dateToBeConsidered, firstOccurence, repeatfrequency
-- Calculates whether an event first occuring on $2, repeating with
interval $3
-- occurs on $1
SELECT CASE WHEN extract(month FROM $3)=0 THEN
-- interval does not use months, calculate using modulo
( ($1-$2) % extract(days FROM $3)::integer ) = 0
ELSE EXISTS(
-- interval uses months, unable to calculate using modulo
-- implement it manually as follows:
-- given that month lengths are between 28 and 31, try
-- multiplying the repeatfrequency by the date difference integer-
-- divided by the interval assuming the above number of days in a
-- month, and all values in between
SELECT true
FROM
generate_series( ($1-$2) / (extract(months FROM
$3)*31+extract(days FROM $3))::integer,
($1-$2) / (extract(months FROM $3)*
CASE WHEN ($1-$2)/28 <= 12 THEN 28
ELSE 30.2
END
+extract(days FROM $3))::integer
)
WHERE ($2 + generate_series*$3) = $1
)
END
$$ LANGUAGE sql IMMUTABLE;
Benchmark: Set to 'volatile' and run
explain analyze select
isrepeatdate('24/7/9008'::date,'24/6/2000'::date,'1 month'::interval)
FROM generate_series(1,40);
explain analyze select
isrepeatdate('24/7/2001'::date,'24/6/2000'::date,'1 month'::interval)
FROM generate_series(1,4000);
explain analyze select
isrepeatdate('24/7/2001'::date,'24/6/2001'::date,'1 month'::interval)
FROM generate_series(1,4000);
Test While loop 'Smart' code
7008 y 15800ms 117.5ms (40 repetitions)
13 mo 297ms 233.7ms (4000 repetitions)
1 mo 55ms 232.0ms (4000 repetitions)
The above code is faster than a simple loop for anything above one year,
and its benefits increase with the size of the time difference. The
above benchmark fails to take into account the potential inlining
advantages of a pure SQL function which would further increase its
advantage. As for a 'cleaner' solution, Tom may well be right....
Netzach
--
Sent via pgsql-novice mailing list (pgsql-novice@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
|
