达梦数据库-兼容Oracle sys_extract_utc函数

如何将一个时区的本地时间，转换为另一个时区的本地时间？
达梦没有做oracle sys_extract_utc函数的兼容，那么我们可以自己写个函数来实现兼容

CREATE or REPLACE
        FUNCTION sys_extract_utc FOR CALCULATE
                (
                        v_time in datetime
                        (
                                6)
                with time zone)
                RETURN timestamp
        is
        begin
                return
                case substr(v_time, 28, 1)--通过正负号来判定东西时区
                when '+' then--截取本地时区，并转换为utc时间
                        to_date(substr(v_time, 0, 27))-to_number(substr(v_time, 29, 2))/24-to_number(substr(v_time, 32, 2))/24/60
                when '-' then
                        to_date(substr(v_time, 0, 27))+to_number(substr(v_time, 29, 2))/24+to_number(substr(v_time, 32, 2))/24/60
                end;
        end;

效果演示

还可以通过该函数实现做列的默认值

crEATE
        TABLE "TEST"."T_TIMEZONE"
        (
                "C1" TIMESTAMP(3)
WITH
        LOCAL TIME ZONE,
        "C2" TIMESTAMP(3) default sys_extract_utc(systimestamp)
        ) ;
insert into T_TIMEZONE
        (c1
        ) values
        (systimestamp
        );
select * from T_TIMEZONE

更多资讯请上达梦技术社区了解： https://eco.dameng.com