SQL Reporting By Time Intervals

stored_procs

DELIMITER $$

DROP FUNCTION IF EXISTS TRUNC_HOUR$$
CREATE
  FUNCTION TRUNC_HOUR(datestamp DATETIME)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'returns current hour'
  RETURN DATE_FORMAT(datestamp, '%Y-%m-%d %H:00')$$

DROP FUNCTION IF EXISTS TRUNC_DAY$$
CREATE
  FUNCTION TRUNC_DAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns current date (preceding midnight)'
  RETURN DATE(datestamp)$$

DROP FUNCTION IF EXISTS TRUNC_DAYOFWEEK$$
CREATE
  FUNCTION TRUNC_DAYOFWEEK(datestamp DATETIME, dayofweek INT)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'preceding weekday, where dayofweek: 1=sunday, 2=monday, etc'
  RETURN FROM_DAYS(TO_DAYS(datestamp) -MOD(TO_DAYS(datestamp) -dayofweek, 7))$$

DROP FUNCTION IF EXISTS TRUNC_SUNDAY$$
CREATE
  FUNCTION TRUNC_SUNDAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'preceding Sunday'
  RETURN TRUNC_DAYOFWEEK(datestamp, 1)$$

DROP FUNCTION IF EXISTS TRUNC_MONDAY$$
CREATE
  FUNCTION TRUNC_MONDAY(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'preceding Monday'
  RETURN TRUNC_DAYOFWEEK(datestamp, 2)$$

DROP FUNCTION IF EXISTS FIRST_DAYOFWEEK_OF$$
CREATE
  FUNCTION FIRST_DAYOFWEEK_OF(datestamp DATETIME, dayofweek INT)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'first weekday of a month, where dayofweek: 1=sunday, 2=monday, etc'
  RETURN TRUNC_DAYOFWEEK((LAST_DAY(datestamp) + INTERVAL 1 DAY - INTERVAL 1 MONTH + INTERVAL 6 DAY), dayofweek)$$

DROP FUNCTION IF EXISTS FIRST_MONDAY_OF$$
CREATE
  FUNCTION FIRST_MONDAY_OF(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'first monday of the month'
  RETURN FIRST_DAYOFWEEK_OF(datestamp, 2)$$

DROP FUNCTION IF EXISTS LAST_DAYOFWEEK_OF$$
CREATE
  FUNCTION LAST_DAYOFWEEK_OF(datestamp DATETIME, dayofweek INT)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'last weekday of a month, where dayofweek: 1=sunday, 2=monday, etc'
  RETURN TRUNC_DAYOFWEEK(LAST_DAY(datestamp), dayofweek)$$

DROP FUNCTION IF EXISTS LAST_MONDAY_OF$$
CREATE
  FUNCTION LAST_MONDAY_OF(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'last monday of the month'
  RETURN LAST_DAYOFWEEK_OF(datestamp, 2)$$

DROP FUNCTION IF EXISTS TRUNC_MONTH$$
CREATE
  FUNCTION TRUNC_MONTH(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding first of the month'
  RETURN DATE(DATE_FORMAT(datestamp, '%Y-%m-01'))$$

DROP FUNCTION IF EXISTS TRUNC_QUARTER$$
CREATE
  FUNCTION TRUNC_QUARTER(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding first of the quarter'
  RETURN DATE(CONCAT(YEAR(datestamp),'-', 1 + 3*(QUARTER(datestamp)-1),'-01'))$$

DROP FUNCTION IF EXISTS TRUNC_YEAR$$
CREATE
  FUNCTION TRUNC_YEAR(datestamp DATETIME)
  RETURNS DATE DETERMINISTIC NO SQL
  COMMENT 'returns preceding first of the year'
  RETURN DATE(DATE_FORMAT(datestamp, '%Y-01-01'))$$

DROP FUNCTION IF EXISTS TRUNC_N_MINUTES$$
CREATE
  FUNCTION TRUNC_N_MINUTES(datestamp DATETIME, n INT)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'truncate to N minute boundary. For example,
           TRUNCATE_N_MINUTES(sometime, 15) gives the nearest
           preceding quarter hour'
  RETURN DATE_FORMAT(datestamp,'%Y-%m-%d %H:00') +
                INTERVAL (MINUTE(datestamp) -
                          MINUTE(datestamp) MOD n) MINUTE$$

DROP FUNCTION IF EXISTS TRUNC_N_HOURS$$
CREATE
  FUNCTION TRUNC_N_HOURS(datestamp DATETIME, n INT)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'truncate to N hour boundary. For example,
           TRUNCATE_N_HOURS(sometime, 12) gives the nearest
           preceding half-day (noon, or midnight'
  RETURN DATE(datestamp) +
                INTERVAL (HOUR(datestamp) -
                          HOUR(datestamp) MOD n) HOUR$$

DROP FUNCTION IF EXISTS ROUND_HOUR$$
CREATE
  FUNCTION ROUND_HOUR(datestamp DATETIME)
  RETURNS DATETIME DETERMINISTIC NO SQL
  COMMENT 'returns nearest hour'
  RETURN DATE_FORMAT(datestamp + INTERVAL 30 MINUTE, '%Y-%m-%d %H:00')$$

DELIMITER ;