Обсуждение: Calculating repeating events - functionality lost with the demise of tinterval ?
<meta content="text/html;charset=ISO-8859-8-I" http-equiv="Content-Type"> <body alink="#ee0000" bgcolor="#ffffff" link="#0000ee" text="#33ff33" vlink="#551a8b"> Hi list, I need to know whether a repeating event occurs or not on a particular day. I currently have the following information: date of first occurrence (date) repeat frequency (interval) date to check (date) My instinct says that I should be using a mod here to calculate whether the remainder is zero when the difference between the two times is divided by the repeat frequency. Unfortunately this is not possible since as far as I am aware intervals only specify a time length and not two dates. When searching I discovered tinterval which appears to do what I would want, and was surprised to discover that it is not supported in my postgres install because it is too _new_ !?! (PG 8.1). I then considered trying to extract the units from the repeat_frequency interval and then calculating the difference between date_to_check and first_occurrence in those units (eg weeks, months). But I was unable to find a reasonable way to find out the units of an interval. I could try an approximation using <code class="FUNCTION">justify_interval() and friends and then use a short loop to get an accurate result. This is also a hack. This has to be a very standard requirement. How does one go about calculating whether a difference between two dates is a multiple of a particular interval in an elegant fashion ? Thanks, Netzach
Netzach <psql-novice@netzach.co.il> writes:
> I need to know whether a repeating event occurs or not on a particular
> day. I currently have the following information:<br>
> <br>
> date of first occurrence (date)<br>
> repeat frequency (interval)<br>
> date to check (date)<br>
> <br>
> My instinct says that I should be using a mod here to calculate whether
> the remainder is zero when the difference between the two times is
> divided by the repeat frequency. Unfortunately this is not possible
> since as far as I am aware intervals only specify a time length and not
> two dates.
Huh? If you only need this good to day precision, as your reference to
dates suggests, then just subtract the two dates (which will give an
integer number of days) and modulo by the repeat frequency (also given
as an integer number of days).
If that doesn't satisfy your requirements, you need to be more clear
about what they are.
> When searching I discovered tinterval which appears to do
> what I would want, and was surprised to discover that it is not
> supported in my postgres install because it is too _new_ !?! (PG 8.1).<br>
Actually tinterval is ancient and deprecated. But I don't see how
it would apply to your problem as stated.
regards, tom lane
>> I need to know whether a repeating event occurs or not on a particular >> day. I currently have the following information: >> >> date of first occurrence (date) >> repeat frequency (interval) >> date to check (date) >> >> My instinct says that I should be using a mod here to calculate whether >> the remainder is zero when the difference between the two times is >> divided by the repeat frequency. Unfortunately this is not possible >> since as far as I am aware intervals only specify a time length and not >> two dates. > Huh? If you only need this good to day precision, as your reference to > dates suggests, then just subtract the two dates (which will give an > integer number of days) and modulo by the repeat frequency (also given > as an integer number of days). > > If that doesn't satisfy your requirements, you need to be more clear > about what they are. Apologies for the lack of clarity. Try repeat_frequency='1 month' Netzach
Daniel <psql-novice@netzach.co.il> writes:
>> If that doesn't satisfy your requirements, you need to be more clear
>> about what they are.
> Apologies for the lack of clarity.
> Try repeat_frequency='1 month'
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.
regards, tom lane
> 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