Skip to content

Instantly share code, notes, and snippets.

@OllieJones

OllieJones/stored_procs

Last active Apr 16, 2020
Embed
What would you like to do?
SQL Reporting By Time Intervals
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 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.