Re: generate_series() Interpretation

On Mon, Jun 27, 2011 at 2:36 PM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:
> On 06/27/2011 10:56 AM, David E. Wheeler wrote:
>>
>> On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:
>>
>>> That's just how intervals that represent varying periods of time work.
>>> You would need to write your own. But a series of end-of-month dates is
>>> pretty easy:
>>> select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp,
>>> '1 month') - '1 day'::interval;
>>
>> Yeah, but it's trickier if you have a calendaring app and don't know that
>> date a user has chosen for a monthly recurring event. They might have
>> selected June 30, in which case only February would ever need to be
>> different than the default.
>>
>> Best,
>>
>> David
>>
>>
>>
> The query is marginally trickier. But the better calendaring apps give a
> variety of options when selecting "repeat": A user who selects June 30, 2011
> and wants a monthly repeat might want:
>
> 30th of every month - skip months without a 30th
> 30th of every month - move to end-of-month if 30th doesn't exist
> Last day of every month
> Last Thursday of every month
>
> Typical payday repeats are "the 15th and last -day-of-month if a workday or
> the closest preceding workday if not", "second and last Friday", "every
> other Friday"...
>
> No matter how '1 month' is interpreted in generate_series, the application
> programmer will still need to write the queries required to handle whatever
> calendar-repeat features are deemed necessary.

If you look up David Skoll's "remind" application
<http://www.roaringpenguin.com/products/remind>, you'll find something
that does this kind of pattern matching quite, quite well, at a rather
sophisticated level.

I find that I don't want to go through the struggle of figuring out
how to correctly describe those recurrences.

The other way of doing this sort of thing, which seems to be generally
more intuitive, is to treat these calendars as sets, which are a
structure that SQL is rather will designed to manipulate, and use
inclusions/exclusions/intersections to determine which days are of
interest.

I wrote something on this on pgsql-general about 5 years ago that
still seems pretty relevant.

http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"