/* Functions for Year, Quarter, and Month */
--Year
CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 
      SELECT EXTRACT(YEAR FROM $1)::INTEGER; 
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(YEAR FROM $1)::INTEGER;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION YEAR(DATE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(YEAR FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;

--Quarter
CREATE OR REPLACE FUNCTION QUARTER(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 
      SELECT EXTRACT(QUARTER FROM $1)::INTEGER; 
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION QUARTER(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(QUARTER FROM $1)::INTEGER;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION QUARTER(DATE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(QUARTER FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;

--Month
CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 
      SELECT EXTRACT(MONTH FROM $1)::INTEGER; 
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(MONTH FROM $1)::INTEGER;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION MONTH(DATE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(MONTH FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;


/* DATEDIFF Functions for Year and Day */
--Year
CREATE OR REPLACE FUNCTION DATEDIFF_YEAR(TIMESTAMP without TIME ZONE, TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 
      SELECT (DATE_PART('year', $2) - DATE_PART('year', $1))::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION DATEDIFF_YEAR(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT (DATE_PART('year', $2) - DATE_PART('year', $1))::INTEGER;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION DATEDIFF_YEAR(DATE, DATE) RETURNS INTEGER
AS $$
      SELECT (DATE_PART('year', $2) - DATE_PART('year', $1))::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;

--Day
CREATE OR REPLACE FUNCTION DATEDIFF_DAY(TIMESTAMP without TIME ZONE, TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$ 
      SELECT DATE_PART('day', $2 - $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION DATEDIFF_DAY(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT DATE_PART('day', $2 - $1)::INTEGER;
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION DATEDIFF_DAY(DATE, DATE) RETURNS INTEGER
AS $$
      SELECT ($2::DATE - $1::DATE)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;

/* Datename functions for Quarter*/
--Quarter
CREATE OR REPLACE FUNCTION DATENAME_Q(TIMESTAMP without TIME ZONE) RETURNS TEXT
AS $$ 
      SELECT to_char($1, 'Q');
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION DATENAME_Q(TIMESTAMP WITH TIME ZONE) RETURNS TEXT
AS $$
      SELECT to_char($1, 'Q');
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION DATENAME_Q(DATE) RETURNS TEXT
AS $$
      SELECT to_char($1, 'Q');
$$ LANGUAGE SQL IMMUTABLE;

/* End of Month */
CREATE OR REPLACE FUNCTION EOMONTH(TIMESTAMP without TIME ZONE) RETURNS TIMESTAMP without TIME ZONE
AS $$ 
      SELECT date_trunc('month', $1) + interval '1 month' - interval '1 day';
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION EOMONTH(TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP with TIME ZONE
AS $$
      SELECT date_trunc('month', $1) + interval '1 month' - interval '1 day';
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION EOMONTH(DATE) RETURNS TIMESTAMP with TIME ZONE
AS $$
      SELECT date_trunc('month', $1) + interval '1 month' - interval '1 day';
$$ LANGUAGE SQL IMMUTABLE;

/* Dateadd functions for the other OpenIMIS functions*/
--Year
CREATE OR REPLACE FUNCTION DATEADD_YEAR(INTEGER, TIMESTAMP without TIME ZONE) RETURNS TIMESTAMP without TIME ZONE
AS $$ 
      SELECT $2 + ($1 * INTERVAL '1 year');
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION DATEADD_YEAR(INTEGER, TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP with TIME ZONE
AS $$
      SELECT $2 + ($1 * INTERVAL '1 year');
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION DATEADD_YEAR(INTEGER, DATE) RETURNS TIMESTAMP without TIME ZONE
AS $$
      SELECT $2 + ($1 * INTERVAL '1 year');
$$ LANGUAGE SQL IMMUTABLE;

--Month
CREATE OR REPLACE FUNCTION DATEADD_MONTH(INTEGER, TIMESTAMP without TIME ZONE) RETURNS TIMESTAMP without TIME ZONE
AS $$ 
      SELECT $2 + ($1 * INTERVAL '1 month');
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION DATEADD_MONTH(INTEGER, TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP with TIME ZONE
AS $$
      SELECT $2 + ($1 * INTERVAL '1 month');
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION DATEADD_MONTH(INTEGER, DATE) RETURNS TIMESTAMP without TIME ZONE
AS $$
      SELECT $2 + ($1 * INTERVAL '1 month');
$$ LANGUAGE SQL IMMUTABLE;

--DAY
CREATE OR REPLACE FUNCTION DATEADD_DAY(INTEGER, TIMESTAMP without TIME ZONE) RETURNS TIMESTAMP without TIME ZONE
AS $$ 
      SELECT $2 + $1 * INTERVAL '1 day';
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION DATEADD_DAY(INTEGER, TIMESTAMP WITH TIME ZONE) RETURNS TIMESTAMP with TIME ZONE
AS $$
      SELECT $2 + $1 * INTERVAL '1 day';
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION DATEADD_DAY(INTEGER, DATE) RETURNS TIMESTAMP without TIME ZONE
AS $$
      SELECT $2 + $1 * INTERVAL '1 day';
$$ LANGUAGE SQL IMMUTABLE;