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

环境

系统平台：中标麒麟 (x86-64) 6,中标麒麟（龙芯）7
版本：4.5.2

症状

从MySQL数据库进行瀚高数据库国产化替代时报错：year()、 week()不存在。

问题原因

瀚高数据库内核未兼容MySQL内置函数year()、 week()。

解决方案

通过瀚高工程师编写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;