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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers