Re: generate_series() Interpretation

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.

Cheers,
Steve