This topic describes how to use the polar_utility plug-in.

Create or delete the polar_utility plug-in

Use the following syntax to create the polar_utility plug-in:

CREATE extension polar_utility;

Use the following syntax to delete the polar_utility plug-in:

DROP extension polar_utility;

Prepare test data

Create a table named polar_test_interval_func and insert data into the table. The following code provides an example:

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);

to_number(interval)

This function converts an interval value to a numeric value that represents the corresponding number of days.

The following code provides an example:

SELECT to_number(a) FROM polar_test_interval_func;

In the example, the following result is returned:

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

trunc(interval)

This function converts an interval value to a numeric value that represents the corresponding number of days, and retains only the integer part of the returned number.

The following code provides an example:

SELECT trunc(a) FROM polar_test_interval_func;

In the example, the following result is returned:

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

abs(interval)

This function converts an interval value to a numeric value that represents the corresponding number of days, and then returns the absolute value of the returned number.

The following code provides an example:

SELECT abs(a) FROM polar_test_interval_func;

In the example, the following result is returned:

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

to_char(interval)

This function converts an interval value to a numeric value that represents the corresponding number of days, and then converts the returned number to a string value.

The following code provides an example:

SELECT to_char(a) FROM polar_test_interval_func;

In the example, the following result is returned:

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

to_date(numeric, character varying)

This function converts a numeric value to a date value that is in the specified format.

The following code provides an example:

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

In the example, the following result is returned:

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

to_date(timestamp without time zone)

This function returns a date value.

The following code provides an example:

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

In the example, the following result is returned:

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

mod(text, text)

This function performs the modulo operation on text values. By default, this function converts text values to numeric values before it performs the modulo operation.

The following code provides an example:

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

In the example, the following result is returned:

 mod
-----
   0
(1 row)

convert(text, text)

This function converts a string value to a string that uses the specified encoding.

The following code provides an example:

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

In the example, the following result is returned:

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