Use the polar_utility extension - PolarDB

The polar_utility plug-in extends PolarDB for Oracle with additional interval and data-conversion functions. Install it once per database, then call any of its functions directly in SQL.

Install and remove the plug-in

Install the plug-in:

CREATE EXTENSION polar_utility;

Remove the plug-in:

DROP EXTENSION polar_utility;

Function reference

polar_utility provides two groups of functions:

Interval functions — convert an interval value to a numeric day count, then apply a transformation:

Function	Returns	Description	Quick example
`to_number(interval)`	`numeric`	Day count as a decimal number	`to_number(interval '2.1 day')` → `2.1`
`trunc(interval)`	`numeric`	Day count, integer part only	`trunc(interval '2.1 day')` → `2`
`abs(interval)`	`numeric`	Absolute day count	`abs(interval '-1 day')` → `1`
`to_char(interval)`	`text`	Day count as a string	`to_char(interval '2.1 day')` → `'2.1'`

Conversion functions — parse or re-encode values:

Function	Returns	Description	Quick example
`to_date(numeric, character varying)`	`date`	Parse a numeric date string using a format mask	`to_date('20200811','yyyymmddhh24miss')` → `Tue Aug 11 00:00:00 2020`
`to_date(timestamp without time zone)`	`date`	Cast a timestamp to a date value	`to_date('11-AUG-20 03:16:09.089308'::timestamp)` → `Tue Aug 11 03:16:09.089308 2020`
`mod(text, text)`	`numeric`	Modulo operation on text values (converts to numeric first)	`mod('10'::text, '2'::text)` → `0`
`convert(text, text)`	`text`	Re-encode a string to the specified encoding	`convert('RAM is the new disk.', 'utf8')` → `'RAM is the new disk.'`

Set up test data

The examples below share a common test table. Run the following statements once to create it:

CREATE TABLE polar_test_interval_func(a interval);

INSERT INTO polar_test_interval_func
VALUES (interval '1.1 hour'),
       (interval '2.1 day'),
       (interval '3.1 month'),
       (interval '4.3 year'),
       (('11-AUG-20 03:16:09.089308'::timestamp - 1) - '11-AUG-20 03:16:09.089308'::timestamp),
       (('11-AUG-20 03:16:09.089308'::timestamp + 3) - '11-AUG-20 03:16:09.089308'::timestamp);

Interval functions

to_number(interval)

Converts an interval value to the equivalent number of days, returned as numeric.

SELECT to_number(a) FROM polar_test_interval_func;

Result:

        to_number
-------------------------
  0.04583333333333333333
      2.1000000000000000
     93.0000000000000000
   1551.0000000000000000
 -1.00000000000000000000
      3.0000000000000000
(6 rows)

trunc(interval)

Converts an interval value to the equivalent number of days, then drops the fractional part and returns the integer portion as numeric.

SELECT trunc(a) FROM polar_test_interval_func;

Result:

 trunc
-------
     0
     2
    93
  1551
    -1
     3
(6 rows)

abs(interval)

Converts an interval value to the equivalent number of days, then returns the absolute value as numeric.

SELECT abs(a) FROM polar_test_interval_func;

Result:

          abs
------------------------
 0.04583333333333333333
     2.1000000000000000
    93.0000000000000000
  1551.0000000000000000
 1.00000000000000000000
     3.0000000000000000
(6 rows)

to_char(interval)

Converts an interval value to the equivalent number of days, then returns that number as a text string.

SELECT to_char(a) FROM polar_test_interval_func;

Result:

         to_char
-------------------------
 0.04583333333333333333
 2.1000000000000000
 93.0000000000000000
 1551.0000000000000000
 -1.00000000000000000000
 3.0000000000000000
(6 rows)

Conversion functions

to_date(numeric, character varying)

Converts a numeric date string to a date value using the specified format mask.

SELECT to_date('20200811','yyyymmddhh24miss');

Result:

         to_date
--------------------------
 Tue Aug 11 00:00:00 2020
(1 row)

to_date(timestamp without time zone)

Accepts a timestamp value and returns a date value.

SELECT to_date('11-AUG-20 03:16:09.089308'::timestamp);

Result:

             to_date
---------------------------------
 Tue Aug 11 03:16:09.089308 2020
(1 row)

mod(text, text)

Performs the modulo operation on two text values. The values are cast to numeric before the calculation.

SELECT mod('10'::text, '2'::text);

Result:

 mod
-----
   0
(1 row)

convert(text, text)

Re-encodes a string to the target encoding specified in the second argument.

SELECT convert('RAM is the new disk.', 'utf8');

Result:

       convert
----------------------
 RAM is the new disk.
(1 row)