Date split-up based on break-up

Given a From Date, To Date, Fiscal Year system. I want to get all the split-up duration within the given duration based on the breakup asked for.

Breakups are:

1) Weekly: This should be followed from Monday through Sunday.

2) Monthly: This should be 1st of a month through end of the month.

3) Quarterly Since the Fiscal Year system is Apr-Mar, quarterly breakups should be Apr-Jun开发者_如何学C, Jul-Sep, Oct-Dec, Jan-Mar

4) Half-yearly: Since the Fiscal Year system is Apr-Mar, half-yearly breakups should be Apr-Sep, Oct-Mar.

5) Annual: Since the Fiscal Year system is Apr-Mar, annual breakup should be Apr-Mar.

Explained below with examples.

Example 1:

From Date: Feb-10-2010

To Date: Feb-10-2010

Fiscal Year system: Apr to Mar

Weekly Breakup: Feb-08-2010 to Feb-14-2010

Monthly Breakup: Feb-01-2010 to Feb-28-2010

Quarterly Breakup: Jan-01-2010 to Mar-31-2010

Half-yearly Breakup: Oct-01-2009 to Mar-31-2010

Annual Breakup: Apr-01-2009 to Mar-31-2010

Am looking for approach/algorithm to solve this in PostgreSQL 8.2.

This post is similar to a question posted here: Date split-up based on Fiscal Year

---

Rather than trying to do this in code it may be worth using a calendar table keyed on date and with period attributes.
e.g.

CREATE TABLE calendar_day (
day_date DATE PRIMARY KEY,
week_number INTEGER NOT NULL,
month_number SMALLINT NOT NULL,
quarter_number SMALLINT NOT NULL,
demi_year_number SMALLINT NOT NULL,
fiscal_year SMALLINT NOT NULL);

This can be populated fairly easily using generate_series() and postgresql date functions and then used to join according to the date criteria you wish to use