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 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