How do I get the first date of a non-standard quarter in MySQL?-CodePudding

I'm trying to get the start/end dates of a non-standard quarter using MySQL. This question/answer works great to get the start/end dates of standard quarters (Jan-Mar, Apr-Jun, etc.).

Any suggestions on how to modify the query if the quarter does not start on a typical fiscal year's quarters (Jan-Mar, Apr-Jun, etc.)? I have a case where I need the quarter to begin on a February (Feb-Apr, May-Jul, etc.) as well as beginning in March (Mar-May, Jun-Aug, etc.).

I have tried adjusting intervals to accommodate the shift in the months by 1 or 2, but because MySQL returns a set 1,2,3,4 for QUARTER() using a standard year's quarters, it's not working correctly.

Here are the queries from the previously linked question that return a start/end on a standard calendar year's quarters (passing in a date I'm interested in getting the start/end of the quarter it is in):

SELECT MAKEDATE(YEAR('2022-11-02'), 1)   INTERVAL QUARTER('2022-11-02') QUARTER - INTERVAL 1 QUARTER AS quarter_start;
 --------------- 
| quarter_start |
 --------------- 
| 2022-10-01    |
 --------------- 

SELECT MAKEDATE(YEAR('2022-11-02'), 1)   INTERVAL QUARTER('2022-11-02') QUARTER - INTERVAL 1 DAY AS quarter_end;
 --------------- 
| quarter_end   |
 --------------- 
| 2022-12-31    |
 ---------------

How can I adjust this to return a quarter_start of 2022-11-01 and quarter_end of 2023-01-31 if the quarter should be shifted forward by 1 month (quarters: Feb-Apr, May-Jul, Aug-Oct, Nov-Jan)?

CodePudding user response：

You'll have to calculate it yourself from the month.

SELECT CASE 
        WHEN MONTH(date) = 1 THEN FORMAT_DATE(DATE_SUB(date, INTERVAL 1 YEAR), '%Y-11-01'))
        WHEN MONTH(date) IN (2, 3, 4) THEN FORMAT_DATE(date, '%Y-02-01'))
        WHEN MONTH(date) IN (5, 6, 7) THEN FORMAT_DATE(date, '%Y-05-01'))
        WHEN MONTH(date) IN (8, 9, 10) THEN FORMAT_DATE(date, '%Y-08-01'))
        ELSE FORMAT_DATE(date, '%Y-11-01'))
       END AS quarter_start,
     CASE 
        WHEN MONTH(date) = 1 THEN FORMAT_DATE(date, '%Y-01-31'))
        WHEN MONTH(date) IN (2, 3, 4) THEN FORMAT_DATE(date, '%Y-04-30'))
        WHEN MONTH(date) IN (5, 6, 7) THEN FORMAT_DATE(date, '%Y-07-31'))
        WHEN MONTH(date) IN (8, 9, 10) THEN FORMAT_DATE(date, '%Y-10-31'))
        ELSE FORMAT_DATE(DATE_ADD(date, INTERVAL 1 YEAR), '%Y-01-31'))
       END AS quarter_end

CodePudding user response：

This is simple date math.

select
    date,
    date - interval day(date)-1 day - interval (month(date)   offset) mod 3 month as quarter_start,
    date - interval day(date)-1 day - interval (month(date)   offset) mod 3 - 3 month - interval 1 day as quarter_end

where offset is 2 for calendar quarters, 1 for quarters beginning in feb/may/aug/nov, 0 for quarters beginning in mar/jun/sep/dec.