Background information

In Oracle, the result is of the floating-point type. In PolarDB-O, the result is of the Interval type. Generally, this type of difference causes syntax errors in the time computing part of the business SQL.

New solution

In PolarDB-O, the syntax of Date-type subtraction can be adapted. The Transformation idea is to use the extract function to convert the Interval type to the floating point type, to make the result consistent with Oracle.Command

For more information, see https://www.postgresql.org/docs/11/functions-datetime.html.

Examples

The following example shows how to use a function for transformation:
CREATE OR REPLACE FUNCTION time_between(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE) 
RETURNS FLOAT8 AS 
$m$  
  SELECT EXTRACT(EPOCH FROM $1-$2)/86400;  
$m$ LANGUAGE SQL STRICT IMMUTABLE; 

-- select sysdate - date '2020-06-28' from dual; 
-- Transform to
select time_between(sysdate, date '2020-06-28'); 
1.29990540226852