START TRANSACTION;

-- version

DROP FUNCTION IF EXISTS gd_version;
CREATE FUNCTION gd_version()
  RETURNS VARCHAR(255) DETERMINISTIC
  RETURN '2.2.1';


-- default week start

DROP FUNCTION IF EXISTS gd_week_start;
CREATE FUNCTION gd_week_start()
  RETURNS INT DETERMINISTIC
  RETURN 6; -- mon=0, tue=1, wed=2, thu=3, fri=4, sat=5, sun=6


-- second

DROP FUNCTION IF EXISTS gd_second;
CREATE FUNCTION gd_second(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS TIMESTAMP NO SQL
  RETURN CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-%m-%d %H:%i:%S'), time_zone, '+00:00');


-- minute

DROP FUNCTION IF EXISTS gd_minute;
CREATE FUNCTION gd_minute(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS TIMESTAMP NO SQL
  RETURN CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-%m-%d %H:%i:00'), time_zone, '+00:00');


-- hour

DROP FUNCTION IF EXISTS gd_hour;
CREATE FUNCTION gd_hour(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS TIMESTAMP NO SQL
  RETURN CONVERT_TZ(DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-%m-%d %H:00:00'), time_zone, '+00:00');


-- day

DROP FUNCTION IF EXISTS gd_day;
CREATE FUNCTION gd_day(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS DATE NO SQL
  RETURN DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-%m-%d 00:00:00');


-- week

DROP FUNCTION IF EXISTS gd_week;
CREATE FUNCTION gd_week(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS DATE NO SQL
  RETURN DATE_FORMAT(CONVERT_TZ(DATE_SUB(ts, INTERVAL ((7 - gd_week_start() + WEEKDAY(CONVERT_TZ(ts, '+00:00', time_zone))) % 7) DAY), '+00:00', time_zone), '%Y-%m-%d 00:00:00');


-- month

DROP FUNCTION IF EXISTS gd_month;
CREATE FUNCTION gd_month(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS DATE NO SQL
  RETURN DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-%m-01 00:00:00');


-- quarter

DROP FUNCTION IF EXISTS gd_quarter;
CREATE FUNCTION gd_quarter(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS DATE NO SQL
  RETURN DATE_FORMAT(DATE(CONCAT(YEAR(CONVERT_TZ(ts, '+00:00', time_zone)), '-', LPAD(1 + 3 * (QUARTER(CONVERT_TZ(ts, '+00:00', time_zone)) - 1), 2, '00'), '-01')), '%Y-%m-%d %H:%i:%S');

-- year

DROP FUNCTION IF EXISTS gd_year;
CREATE FUNCTION gd_year(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS DATE NO SQL
  RETURN DATE_FORMAT(CONVERT_TZ(ts, '+00:00', time_zone), '%Y-01-01 00:00:00');


-- hour of day

DROP FUNCTION IF EXISTS gd_hour_of_day;
CREATE FUNCTION gd_hour_of_day(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS INT NO SQL
  RETURN EXTRACT(HOUR FROM CONVERT_TZ(ts, '+00:00', time_zone));


-- day of week

DROP FUNCTION IF EXISTS gd_day_of_week;
CREATE FUNCTION gd_day_of_week(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS INT NO SQL
  RETURN DAYOFWEEK(CONVERT_TZ(ts, '+00:00', time_zone)) - 1;


-- day of month

DROP FUNCTION IF EXISTS gd_day_of_month;
CREATE FUNCTION gd_day_of_month(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS INT NO SQL
  RETURN DAYOFMONTH(CONVERT_TZ(ts, '+00:00', time_zone));


-- day of year

DROP FUNCTION IF EXISTS gd_day_of_year;
CREATE FUNCTION gd_day_of_year(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS INT NO SQL
  RETURN DAYOFYEAR(CONVERT_TZ(ts, '+00:00', time_zone));


-- month of year

DROP FUNCTION IF EXISTS gd_month_of_year;
CREATE FUNCTION gd_month_of_year(ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS INT NO SQL
  RETURN MONTH(CONVERT_TZ(ts, '+00:00', time_zone));


-- period

DROP FUNCTION IF EXISTS gd_period;
CREATE FUNCTION gd_period(period VARCHAR(255), ts TIMESTAMP, time_zone VARCHAR(255))
  RETURNS DATE NO SQL
  RETURN CASE
  WHEN period = 'day' THEN
    gd_day(ts, time_zone)
  WHEN period = 'week' THEN
    gd_week(ts, time_zone)
  WHEN period = 'month' THEN
    gd_month(ts, time_zone)
  WHEN period = 'quarter' THEN
    gd_quarter(ts, time_zone)
  WHEN period = 'year' THEN
    gd_year(ts, time_zone)
  ELSE
    NULL
  END;


COMMIT;