【前言】

    日期函数是数据库非常重要的函数类型，我们从TD迁移到GaussDB(DWS)时，GaussDB(DWS)有很多日期函数和日期类型的行为与TD是一致的，但仍然有很多TD特有的日期函数，GaussDB(DWS)没有的，这些日期函数需要在GaussDB(DWS)建一些自定义函数做迁移适配。

   值得注意的是，td的日期相关的内容中，有一个日历表sys_calendar.calendar，该日历表存放1900-01-01到2100-12-31的所有日期，以及该天在一周，一月，一年的第几天等信息。经验上看该表在批量作业中使用较多，建议建同名schema同名表存同样的数据在GaussDB(DWS)中。

【返回数字的日期函数】

1.【*number_of_*】

这是一类函数获取第几天、第几月，第几年的日期函数，在实践过程中，主要有以下几个函数：

*注意，td中这些函数最大输入是sys_calendar.calendar的最大日期，2100-12-31，超过该日期会报错，而GaussDB(DWS)使用本文方法建的函数，不会报错。

这些函数的实现方式比较简单，主要使用extract的方式获取天数，月数等信息。周数因为GaussDB(DWS)目前本身没有获取周数的函数（extract有获取iso年的周数，但没有获取自然年的周数），而且td使用一周的开始是周日，所以获取周数的时候会有点绕。

此处可以使用sys_calendar.calendar这个表的字段信息能轻松获取全部信息，比如select week_of_year from sys_calendar.calendar where calendar_date = 输入日期，能直接查询到weeknumber。但是因为与该表存在强关联，在灾难恢复的时候需要先恢复该表才能使用这类型的函数，在考虑后决定不使用该表。

以下是一些函数的例子，所有函数的sql定义会在文末附上

CREATE OR REPLACE FUNCTION public.daynumber_of_week(date)
RETURNS integer
LANGUAGE sql
IMMUTABLE NOT FENCED SHIPPABLE
AS $$
SELECT EXTRACT(dow from $1)::integer + 1; 
$$
;

CREATE OR REPLACE FUNCTION public.weeknumber_of_year(date)
RETURNS integer
LANGUAGE sql
IMMUTABLE NOT FENCED SHIPPABLE
AS $$
/*可做成依赖sys_calendar.calendar表的结果，直接select week_of_year from sys_calendar.calendar where calendar_date = $1;即可。
sys_calendar.calendar需做成复制表
但减少依赖问题可能少一些，比如备份恢复等，函数不需等待sys_calendar.calendar的恢复即可使用
*/
select ceil(
($1 - extract(dow from $1) + 1 -
/*td日历中，周日是一周的开始，部分年份的1-1是周日，weeknumber从1开始；部分年份的1-1不是周日，weeknumber从0开始
此处获取的是weeknumber为1的第一个周日*/
((extract(year from $1)||'-01-01')::date + (case when extract(dow from (extract(year from $1)||'-01-01')::date) <> 0 then 6 - extract(dow from ((extract(year from $1)||'-01-01')::date)) else 0 end))
)/7 /*日期相减后除7，使用ceil向上取整则为weeknumber*/
)::integer;
$$
;

2.【td_day_of*】

该类型函数与daynumber_of* 一样，只是名字不同，此处简单举例子：

CREATE OR REPLACE FUNCTION public.td_day_of_month(date)
RETURNS integer
LANGUAGE sql
IMMUTABLE NOT FENCED SHIPPABLE
AS $$
select extract(day from $1)::integer;
$$

【返回日期的日期函数】

返回日期的日期函数，是对日期做一定的运算后得出一个日期结果，比如trunc，oadd_months

trunc函数在teradata的一些输入说明如下

参考：https://docs.teradata.com/r/WX0vkeB8F3JQXZ0HTR~d0Q/f96l5JKF5A34cprktUWdzA

Date Formats for TRUNC

trunc日期函数的一些例子：

*附常用的oadd_months和trunc的sql代码

CREATE OR REPLACE FUNCTION public.oadd_months(date,integer)
RETURNS date
LANGUAGE sql
IMMUTABLE NOT FENCED SHIPPABLE
AS $$
select case when (extract(day from $1) = extract(day from (date_trunc('MONTH',$1) + interval '1 MONTH - 1 DAY'))) THEN
(DATE_TRUNC('MONTH',$1) + CAST($2 + 1 ||' MONTH - 1 DAY' AS INTERVAL))::DATE
else ($1 + CAST($2 || ' MONTH' AS INTERVAL))::DATE
end;
$$
;

CREATE OR REPLACE FUNCTION public.trunc(date,varchar default 'DD'::varchar)
RETURNS date
LANGUAGE plpgsql
IMMUTABLE STRICT NOT FENCED SHIPPABLE
AS $$
declare 
    v_timestamp date := null;
    v_format varchar := upper($2);

begin

   IF v_format in ('YYYY','YEAR','YYY','YY','Y','SYYYY','SYEAR') THEN
           v_timestamp := date_trunc('YEAR',$1);
   ELSIF v_format in ('IYYY','IYY','IY','I') THEN --ISO日历的第一天
           v_timestamp := (extract('YEAR' from $1)||'-01-01')::date - (date_part('dow',(extract('YEAR' from $1)||'-01-01')::date) - 1);
   ELSIF v_format in ('MONTH','MON','MM','RM') THEN
           v_timestamp := date_trunc('MONTH',$1);
   ELSIF v_format in ('DD','DDD','J') THEN
           v_timestamp := date_trunc('DAY',$1);
   ELSIF v_format in ('D','DY','DAY') THEN
          IF date_part('dow',$1) = 0 THEN
                  v_timestamp := $1;
          ELSE 
                  v_timestamp := date_trunc('WEEK',$1) -1;
          END IF;
   ELSIF v_format in ('WW') THEN --最近一个周几（一年的第一天的周几）,假如一年第一天是周一，那返回最近一个周一
          v_timestamp := $1 - (
                         CASE WHEN date_part('isodow',$1) >= date_part('isodow',(extract('YEAR' from $1)||'-01-01')::date)
                         THEN date_part('isodow',$1) - date_part('isodow',(extract('YEAR' from $1)||'-01-01')::date)
                         ELSE 7 + date_part('isodow',$1) - date_part('isodow',(extract('YEAR' from $1)||'-01-01')::date)
                       end);
   ELSIF v_format in ('IW') THEN --最近一个周几(iso年的第一天的周几)
          v_timestamp := $1 - (date_part('isodow',$1) - 1);
   ELSIF v_format in ('W') THEN --最近一个周几（本月的第一天的周几）
          v_timestamp := $1 - (
                          CASE WHEN date_part('isodow',$1) >= date_part('isodow',(SUBSTR($1::TEXT,1,7)||'-01')::DATE)
                          THEN date_part('isodow',$1)  - date_part('isodow',(SUBSTR($1::TEXT,1,7)||'-01')::DATE)
                          ELSE 7 + date_part('isodow',$1) - date_part('isodow',(SUBSTR($1::TEXT,1,7)||'-01')::date) 
                        end
                         );
   ELSIF v_format  = 'Q' THEN
           v_timestamp := CASE WHEN EXTRACT('QUARTER' FROM $1) = 1 THEN (extract('YEAR' FROM $1)||'-01-01')::date
           WHEN EXTRACT('QUARTER' FROM $1) = 2 THEN (extract('YEAR' FROM $1)||'-04-01')::date
           WHEN EXTRACT('QUARTER' FROM $1) = 3 THEN (extract('YEAR' FROM $1)||'-07-01')::date
           WHEN EXTRACT('QUARTER' FROM $1) = 4 THEN (extract('YEAR' FROM $1)||'-10-01')::date
         END;
   ELSIF v_format in ('HH','HH24') THEN
           v_timestamp := date_trunc('HOUR',$1);
   ELSIF v_format in ('HH12') THEN
           v_timestamp := to_char($1,'YYYY-MM-DD HH12:00:00')::DATE;
   ELSIF v_format in ('MINUTE','MI') THEN
           v_timestamp := date_trunc('MINUTE',$1);
           
    ELSE
         raise exception 'U-2001 [%] is not recognize. you can try [help]',$2;
    END IF;
RETURN v_timestamp;
end;
$$
;

具体的代码可以查看附件

【总结】

在迁移TD到GaussDB(DWS)过程中，肯定有很多日期相关的函数，本文针对常见的，GaussDB(DWS)无法支持的日期函数，进行了一些简单描述和代码展示。因teradata日期函数众多，本文肯定没有覆盖全，此处可以做一个引子，若出现本文附件的代码没有覆盖的场景，读者可以自行根据本文的例子，相对应新增日期udf函数使用。