MySQL内置函数：year()、 week()兼容

CREATE OR REPLACE FUNCTION 模式.year(indate timestamp with time zone)


 RETURNS integer


 LANGUAGE plpgsql


AS $function$ 


BEGIN 


return date_part('year',inDate); 


END; 


$function$;






CREATE OR REPLACE FUNCTION week(anyelement, integer)


 RETURNS integer


 LANGUAGE plpgsql


 IMMUTABLE STRICT


AS $function$


BEGIN


  IF is_datetime ( $1 ) THEN


    RETURN (_calc_week($1, _week_mode($2)))[1];


  END IF;


  RAISE EXCEPTION 'Invalid date  time value --> %', $1;


END;


$function$


;






CREATE OR REPLACE FUNCTION is_datetime(anyelement)


 RETURNS boolean


 LANGUAGE plpgsql


 IMMUTABLE STRICT


AS $function$


DECLARE d date;


        t TIMESTAMP;


        tz TIMESTAMPTZ;


BEGIN


   d = $1::DATE;


   RETURN TRUE;


EXCEPTION WHEN others THEN


        BEGIN


           t = $1::TIMESTAMP;


                RETURN TRUE;


        EXCEPTION WHEN others THEN


                BEGIN


                        tz = $1::TIMESTAMPTZ;


                        RETURN TRUE;


                EXCEPTION WHEN others THEN


                   RETURN FALSE;


                END;


        END;


END;


$function$


;






CREATE OR REPLACE FUNCTION _calc_week(qdate anyelement, behavior integer)


 RETURNS integer[]


 LANGUAGE plpgsql


 IMMUTABLE STRICT


AS $function$


  DECLARE


    _WEEK_MONDAY_FIRST  CONSTANT integer := 1;


    _WEEK_YEAR          CONSTANT integer := 2;


    _WEEK_FIRST_WEEKDAY CONSTANT integer := 4;


    qyear         integer := EXTRACT(YEAR FROM qdate);


    qmonth        integer := EXTRACT(MONTH FROM qdate);


    qday          integer := EXTRACT(DAY FROM qdate);


    daynr         integer := EXTRACT(DOY FROM qdate);


    yday1         date    := pg_catalog.date_trunc('year', qdate);


    first_daynr   integer := 1;


    monday_first  boolean := (behavior & _WEEK_MONDAY_FIRST) <> 0;


    week_year     boolean := (behavior & _WEEK_YEAR) <> 0;


    first_weekday boolean := (behavior & _WEEK_FIRST_WEEKDAY) <> 0;


    weekday       integer := _calc_weekday(yday1, NOT monday_first);


    days          integer;


  BEGIN


    IF qmonth = 1 AND qday <= 7 - weekday THEN


      IF (NOT week_year) AND ((first_weekday AND weekday <> 0) OR (NOT first_weekday AND weekday >= 4))        THEN


        RETURN array[0, qyear];


      END IF;


      week_year := true;


      qyear := qyear - 1;


      days := _calc_days_in_year(qyear);


      first_daynr := first_daynr - days;


      weekday := (weekday + 53 * 7 - days) % 7;


    END IF;


    IF (first_weekday AND weekday <> 0) OR (NOT first_weekday AND weekday >= 4) THEN


      days := daynr - (first_daynr + (7 - weekday));


    ELSE


      days := daynr - (first_daynr - weekday);


    END IF;


    IF week_year AND days >= 52 * 7 THEN


      weekday := (weekday + _calc_days_in_year(qyear)) % 7;


      IF (NOT first_weekday AND weekday < 4) OR (first_weekday AND weekday = 0) THEN


  qyear := qyear + 1;


        RETURN array[1, qyear];


      END IF;


    END IF;


    RETURN array[days  7 + 1, qyear];


  END;


$function$


;






CREATE OR REPLACE FUNCTION _calc_weekday(qdate date, sundayfirst boolean)


 RETURNS integer


 LANGUAGE plpgsql


 IMMUTABLE STRICT


AS $function$


  BEGIN


    RETURN (EXTRACT(DOW FROM qdate)::integer + CASE WHEN sundayfirst THEN 0 ELSE 6 END) % 7;


  END;


$function$


;






CREATE OR REPLACE FUNCTION _calc_days_in_year(year integer)


RETURNS integer AS $$


  BEGIN


    IF (year & 3) = 0 AND ((year % 100) <> 0 OR (year % 400) = 0 AND year <> 0) THEN


      RETURN 366;


    ELSE


      RETURN 365;


    END IF;


  END;


$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;