GBase 8s使用unix_timestamp()函数

注：以下语法根据GBase 8s v8.7 2.0.1a2_2版本进行过更新。

-- 输入参数类型是datetime year to second
drop function if exists UNIX_TIMESTAMP(datetime year to second);
create function UNIX_TIMESTAMP(datestr datetime year to second) returning  bigint with (not variant);
  define rc_char varchar(30);
  if datestr < datetime(1970-01-01 08:00:00) year to second then
    let rc_char = datetime(1970-01-01 08:00:00) year to second - datestr;
    return 0 - ((replace(substr(rc_char,1,9),'-')*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
  else
    let rc_char = datestr - datetime(1970-01-01 08:00:00) year to second;
    return ((substr(rc_char,1,9)*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
  end if;
end function ;

-- 输入参数类型是varchar, 格式是yyyy-mm-dd hh24:mi:ss
drop function if exists UNIX_TIMESTAMP(varchar);
create function UNIX_TIMESTAMP(datestr varchar(30)) returning  bigint with (not variant);
  define rc_char varchar(30);
  if datestr < '1970-01-01 08:00:00' then
    let rc_char = datetime(1970-01-01 08:00:00) year to second - to_date(datestr,'yyyy-mm-dd hh24:mi:ss');
    return 0 - ((replace(substr(rc_char,1,9),'-')*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
  else
    let rc_char = to_date(datestr,'yyyy-mm-dd hh24:mi:ss') - datetime(1970-01-01 08:00:00) year to second;
    return ((substr(rc_char,1,9)*86400)::bigint + substr(rc_char,17,2)*1 + substr(rc_char,14,2)*60 + substr(rc_char,11,2)*3600);
  end if;
end function ;

unix_timestamp的基准时间是1970-01-01 08:00:00 示例： 获取'2020-05-14 00:00:00'对应的时间值自1970-01-01的秒数。

select UNIX_TIMESTAMP('2020-05-14 00:00:00') from dual;

结果： 1589385600

from_unixtime()函数

对于兼容mysql的from_unixtime()函数，可以直接使用dbinfo('utc_to_dateime', UTC_VALUE)来获取，如：

> select dbinfo('utc_to_datetime',0) from dual;

(constant)  1970-01-01 08:00:00

> select dbinfo('utc_to_datetime',1577808000) from dual;

(constant)  2020-01-01 00:00:00

对于超出integer范围的间隔值，可以使用以下自定义函数代替。

drop function if exists from_unixtime;
create function "gbasedbt".from_unixtime(p bigint) returns varchar(20) with (not variant);
define v_day int;
define v_hour int;
define v_min int;
define v_sec int;
define v_dt datetime year to second;
on exception
  return null;
end exception;

let v_day = p / 86400;
let v_dt = datetime(1970-01-01 08:00:00) year to second + v_day units day;

let v_hour = mod(p,86400) / 3600;
let v_dt = v_dt + v_hour units hour;

let v_min = mod(p,3600) / 60;
let v_dt = v_dt + v_min units minute;

let v_sec = mod(p,60);
let v_dt = v_dt + v_sec units second;

return to_char(v_dt,'yyyy-mm-dd hh24:mi:ss');
end function;