calculate_time_range.sql

DROP FUNCTION IF EXISTS calculate_time_range();
CREATE OR REPLACE FUNCTION calculate_time_range (uom varchar, qty integer, startTs timestamptz DEFAULT (current_timestamp at time zone 'utc')::timestamptz) RETURNS tstzrange AS $$
	BEGIN

	IF uom IS null THEN RETURN null; END IF;
	IF qty IS null THEN RETURN null; END IF;

	CASE UPPER(uom)
		WHEN 'TIME_MS' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' milliseconds')::INTERVAL, '[)');
		WHEN 'TIME_S' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' second')::INTERVAL, '[)');
		WHEN 'TIME_MIN' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' minute')::INTERVAL, '[)');
		WHEN 'TIME_HR' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' hour')::INTERVAL, '[)');
		WHEN 'TIME_DAY' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' day')::INTERVAL, '[)');
		WHEN 'TIME_WK' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' week')::INTERVAL, '[)');
		WHEN 'TIME_MON' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' month')::INTERVAL, '[)');
		WHEN 'TIME_YR' THEN
			RETURN TSTZRANGE(startTs, startTs + (qty || ' year')::INTERVAL, '[)');
		WHEN 'TIME_CALYR' THEN
			RETURN TSTZRANGE(startTs, date_trunc('year', startTs + (qty || ' year')::INTERVAL), '[)');
		WHEN 'TIME_INF' THEN
			RETURN TSTZRANGE(startTs, null, '[)');
	ELSE
		RAISE EXCEPTION 'Invalid UOM: %', uom USING HINT = 'Possible options for UOM are: TIME_ms, TIME_s, TIME_min, TIME_hr, TIME_day, TIME_wk, TIME_mon, TIME_yr, TIME_calYr, TIME_inf';
		END CASE;
	END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

SELECT calculate_time_range('TIME_calYr', 1);
SELECT calculate_time_range('TIME_calYr', 2);
SELECT calculate_time_range('TIME_calYr', 1, '2016-01-01');
SELECT calculate_time_range('TIME_calYr', 1, '2016-09-01');
SELECT calculate_time_range('TIME_inf', 2);
SELECT calculate_time_range('TIME_ms', 100);
SELECT calculate_time_range('TIME_s', 100);
SELECT calculate_time_range('TIME_min', 30);
SELECT calculate_time_range('TIME_hr', 1);
SELECT calculate_time_range('TIME_day', 1);
SELECT calculate_time_range('TIME_wk', 1);
SELECT calculate_time_range('TIME_mon', 1);
SELECT calculate_time_range('TIME_yr', 1);
SELECT calculate_time_range('TIME_x', 1);
SELECT calculate_time_range('TIME_calYr', null);
SELECT calculate_time_range(NULL, 1);
SELECT calculate_time_range('TIME_yr', 1, null);