Skip to content

Instantly share code, notes, and snippets.

@GregPK
Last active December 10, 2015 23:28
Show Gist options
  • Save GregPK/4509179 to your computer and use it in GitHub Desktop.
Save GregPK/4509179 to your computer and use it in GitHub Desktop.
Two functions that return the date of the start and end of the quarter of which the given date is in. (+some "unit tests").
DELIMITER $$
/** qStart (DATE date)
* Get the date of the start of a quarter given a date in that quarter.
*/
DROP FUNCTION IF EXISTS `qStart`$$
CREATE FUNCTION `qStart`(d DATE) RETURNS DATE
DETERMINISTIC
BEGIN
RETURN d - INTERVAL MOD(MONTH(d)-1,3) MONTH - INTERVAL DAY(d)-1 DAY;
END$$
/** qEnd (DATE date)
* Get the date of the end of a quarter given a date in that quarter.
* Please note that this could be simplified if you use have the qStart function
*/
DROP FUNCTION IF EXISTS `qEnd`$$
CREATE FUNCTION `qEnd`(d DATE) RETURNS DATE
DETERMINISTIC
BEGIN
DECLARE quarterDateStart DATE DEFAULT d+INTERVAL 3 MONTH;
RETURN (quarterDateStart - INTERVAL MOD(MONTH(quarterDateStart)-1,3) MONTH - INTERVAL DAY(quarterDateStart)-1 DAY) - INTERVAL 1 DAY;
/** Please note that this could be simplified if you use have the qStart function like so:
* RETURN qStart(d+INTERVAL 3 MONTH) - INTERVAL 1 DAY
*/
END$$
DELIMITER ;
SELECT
IF(qStart(DATE('2013-01-01')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-01-01'))," != ",DATE('2013-01-01'))) as qs_a,
IF(qStart(DATE('2013-02-01')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-02-01'))," != ",DATE('2013-01-01'))) as qs_b,
IF(qStart(DATE('2013-03-01')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-03-01'))," != ",DATE('2013-01-01'))) as qs_c,
IF(qStart(DATE('2013-04-01')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-04-01'))," != ",DATE('2013-04-01'))) as qs_d,
IF(qStart(DATE('2013-05-01')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-05-01'))," != ",DATE('2013-04-01'))) as qs_e,
IF(qStart(DATE('2013-06-01')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-06-01'))," != ",DATE('2013-04-01'))) as qs_f,
IF(qStart(DATE('2013-07-01')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-07-01'))," != ",DATE('2013-07-01'))) as qs_g,
IF(qStart(DATE('2013-08-01')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-08-01'))," != ",DATE('2013-07-01'))) as qs_h,
IF(qStart(DATE('2013-09-01')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-09-01'))," != ",DATE('2013-07-01'))) as qs_i,
IF(qStart(DATE('2013-10-01')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-10-01'))," != ",DATE('2013-10-01'))) as qs_j,
IF(qStart(DATE('2013-11-01')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-11-01'))," != ",DATE('2013-10-01'))) as qs_k,
IF(qStart(DATE('2013-12-01')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-12-01'))," != ",DATE('2013-10-01'))) as qs_l,
IF(qStart(DATE('2013-01-18')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-01-18'))," != ",DATE('2013-01-01'))) as qs_ab,
IF(qStart(DATE('2013-02-18')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-02-18'))," != ",DATE('2013-01-01'))) as qs_bb,
IF(qStart(DATE('2013-03-18')) = DATE('2013-01-01'),"OK",CONCAT(qStart(DATE('2013-03-18'))," != ",DATE('2013-01-01'))) as qs_cb,
IF(qStart(DATE('2013-04-18')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-04-18'))," != ",DATE('2013-04-01'))) as qs_db,
IF(qStart(DATE('2013-05-18')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-05-18'))," != ",DATE('2013-04-01'))) as qs_eb,
IF(qStart(DATE('2013-06-18')) = DATE('2013-04-01'),"OK",CONCAT(qStart(DATE('2013-06-18'))," != ",DATE('2013-04-01'))) as qs_fb,
IF(qStart(DATE('2013-07-18')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-07-18'))," != ",DATE('2013-07-01'))) as qs_gb,
IF(qStart(DATE('2013-08-18')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-08-18'))," != ",DATE('2013-07-01'))) as qs_hb,
IF(qStart(DATE('2013-09-18')) = DATE('2013-07-01'),"OK",CONCAT(qStart(DATE('2013-09-18'))," != ",DATE('2013-07-01'))) as qs_ib,
IF(qStart(DATE('2013-10-18')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-10-18'))," != ",DATE('2013-10-01'))) as qs_jb,
IF(qStart(DATE('2013-11-18')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-11-18'))," != ",DATE('2013-10-01'))) as qs_kb,
IF(qStart(DATE('2013-12-18')) = DATE('2013-10-01'),"OK",CONCAT(qStart(DATE('2013-12-18'))," != ",DATE('2013-10-01'))) as qs_lb,
IF(qEnd(DATE('2013-01-01')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-01-01'))," != ",DATE('2013-03-31'))) as qe_a,
IF(qEnd(DATE('2013-02-01')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-02-01'))," != ",DATE('2013-03-31'))) as qe_b,
IF(qEnd(DATE('2013-03-01')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-03-01'))," != ",DATE('2013-03-31'))) as qe_c,
IF(qEnd(DATE('2013-04-01')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-04-01'))," != ",DATE('2013-06-30'))) as qe_d,
IF(qEnd(DATE('2013-05-01')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-05-01'))," != ",DATE('2013-06-30'))) as qe_e,
IF(qEnd(DATE('2013-06-01')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-06-01'))," != ",DATE('2013-06-30'))) as qe_f,
IF(qEnd(DATE('2013-07-01')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-07-01'))," != ",DATE('2013-09-30'))) as qe_g,
IF(qEnd(DATE('2013-08-01')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-08-01'))," != ",DATE('2013-09-30'))) as qe_h,
IF(qEnd(DATE('2013-09-01')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-09-01'))," != ",DATE('2013-09-30'))) as qe_i,
IF(qEnd(DATE('2013-10-01')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-10-01'))," != ",DATE('2013-12-31'))) as qe_j,
IF(qEnd(DATE('2013-11-01')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-11-01'))," != ",DATE('2013-12-31'))) as qe_k,
IF(qEnd(DATE('2013-12-01')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-12-01'))," != ",DATE('2013-12-31'))) as qe_l,
IF(qEnd(DATE('2013-01-19')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-01-19'))," != ",DATE('2013-01-01'))) as qe_ab,
IF(qEnd(DATE('2013-02-19')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-02-19'))," != ",DATE('2013-03-31'))) as qe_bb,
IF(qEnd(DATE('2013-03-19')) = DATE('2013-03-31'),"OK",CONCAT(qEnd(DATE('2013-03-19'))," != ",DATE('2013-03-31'))) as qe_cb,
IF(qEnd(DATE('2013-04-19')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-04-19'))," != ",DATE('2013-06-30'))) as qe_db,
IF(qEnd(DATE('2013-05-19')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-05-19'))," != ",DATE('2013-06-30'))) as qe_eb,
IF(qEnd(DATE('2013-06-19')) = DATE('2013-06-30'),"OK",CONCAT(qEnd(DATE('2013-06-19'))," != ",DATE('2013-06-30'))) as qe_fb,
IF(qEnd(DATE('2013-07-19')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-07-19'))," != ",DATE('2013-09-30'))) as qe_gb,
IF(qEnd(DATE('2013-08-19')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-08-19'))," != ",DATE('2013-09-30'))) as qe_hb,
IF(qEnd(DATE('2013-09-19')) = DATE('2013-09-30'),"OK",CONCAT(qEnd(DATE('2013-09-19'))," != ",DATE('2013-09-30'))) as qe_ib,
IF(qEnd(DATE('2013-10-19')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-10-19'))," != ",DATE('2013-12-31'))) as qe_jb,
IF(qEnd(DATE('2013-11-19')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-11-19'))," != ",DATE('2013-12-31'))) as qe_kb,
IF(qEnd(DATE('2013-12-19')) = DATE('2013-12-31'),"OK",CONCAT(qEnd(DATE('2013-12-19'))," != ",DATE('2013-12-31'))) as qe_kl
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment