/************************************************
 * FUNCTION: function for datetime minus or plus
 * 
 * WRITE BY: liaosnet@gbasedbt.com 2023-10-07
 ************************************************/
-- create function minus
UPDATE sysprocedures SET mode = 'D' WHERE procname = 'minus';
drop function if exists minus(datetime year to fraction(5), float); 
create function gbasedbt.minus(in_date datetime year to fraction(5), in_day float)
  returns datetime year to fraction(5) with (not variant);
  DEFINE v_days int;
  DEFINE v_sec_fraction decimal(10,5);
  DEFINE v_datetime datetime year to fraction(5);

  on exception
    return null;
  end exception;
  
  LET v_days = in_day;
  LET v_sec_fraction = ((in_day - v_days) * 86400)::decimal(10,5);
  LET v_datetime = in_date - v_days units day;
  LET v_datetime = v_datetime - v_sec_fraction units fraction(5);
  return v_datetime;
end function;

-- modify function mode to 'o' which will not display in dbschema/dbexport.
UPDATE sysprocedures SET mode = 'o' WHERE procname = 'minus';

-- create function plus
UPDATE sysprocedures SET mode = 'D' WHERE procname = 'plus';
drop function if exists plus(datetime year to fraction(5), float); 
create function gbasedbt.plus(in_date datetime year to fraction(5), in_day float)
  returns datetime year to fraction(5) with (not variant);
  DEFINE v_days int;
  DEFINE v_sec_fraction decimal(10,5);
  DEFINE v_datetime datetime year to fraction(5);

  on exception
    return null;
  end exception;

  LET v_days = in_day;
  LET v_sec_fraction = ((in_day - v_days) * 86400)::decimal(10,5);
  LET v_datetime = in_date + v_days units day;
  LET v_datetime = v_datetime + v_sec_fraction units fraction(5);
  return v_datetime;
end function;

-- modify function mode to 'o' which will not display in dbschema/dbexport.
UPDATE sysprocedures SET mode = 'o' WHERE procname = 'plus';