活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

GBase 8s数据库中，两个日期时间的值相减，得到的是interval类型的值（interval day to second），而oracle中直接就是数值了。我们可以通过自定义函数的方式，把interval类型转换成数值型（decimal(20,8)）。  





```sql
drop function if exists intval2number(interval day to second);
create function intval2number(v_intval interval day to second) returns decimal(20,8)
  define v_numsec bigint;
  define v_decday decimal(20,8);
  define v_intlen int;
  define v_tmpsec int;
  define v_tmpmin int;
  define v_tmphour int;
  define v_tmpday int;
  on exception
    return null;
  end exception;
  -- 对于interval day to second，将其转换为字符型，才能取长度。
  let v_intlen = length(v_intval::varchar(40));
  let v_tmpsec = substr(v_intval, v_intlen - 1, 2);
  let v_tmpmin = substr(v_intval, v_intlen - 4, 2);
  let v_tmphour = substr(v_intval, v_intlen - 7, 2);
  let v_tmpday  = substr(v_intval, 1, v_intlen - 9);
  let v_numsec = v_tmpsec + v_tmpmin * 60 + v_tmphour * 3600 + abs(v_tmpday)::bigint * 86400;
  let v_decday = v_numsec / 86400;
  -- 对于负值的处理
  if v_tmpday < 0 then
    return 0 - v_decday;
  else
    return v_decday;
  end if;  
end function;
```
相应的，如果输入的是字符串类型，可以写成如下：  
```sql
drop function if exists intval2number(varchar(40));
create function intval2number(p varchar(40)) returns decimal(20,8)
  define v_intval varchar(40);
  define v_numsec bigint;
  define v_decday decimal(20,8);
  define v_intlen int;
  define v_tmpsec int;
  define v_tmpmin int;
  define v_tmphour int;
  define v_tmpday int;
  on exception
    return null;
  end exception;
  let v_intval = to_dsinterval(p);
  let v_intlen = length(v_intval);
  let v_tmpsec = substr(v_intval, v_intlen - 1, 2);
  let v_tmpmin = substr(v_intval, v_intlen - 4, 2);
  let v_tmphour = substr(v_intval, v_intlen - 7, 2);
  let v_tmpday  = substr(v_intval, 1, v_intlen - 9);
  let v_numsec = v_tmpsec + v_tmpmin * 60 + v_tmphour * 3600 + abs(v_tmpday)::bigint * 86400;
  let v_decday = v_numsec / 86400;
  -- 对于负值的处理
  if v_tmpday < 0 then
    return 0 - v_decday;
  else
    return v_decday;
  end if;  
end function;
```
示例：  
输入参数为字符串  
```sql
select intval2number('-111111111 00:00:01') from dual;
```
的结果为：  
```text
(expression)        |
--------------------|
-111111111.00001157 |
```
输入参数为interval类型时  
```sql
select intval2number(current year to second - today) from dual;
```
的结果为：  
```text
(expression) |
-------------|
0.54226852   |
```

GBase 8s自定义interval类型转number类型函数

GBase 8s自定义interval类型转number类型函数

评论

热门帖子