Dataphin:Other functions

AnalyticDB for PostgreSQL supports the following string functions:

acos

• Command format

acos(x)

• Command description: Calculates the arc cosine of a number.

• Parameter description:

  x: Required. The numeric value for which the arc cosine is calculated. It must be within the range -1 to 1, inclusive.

• Return value description: The function returns the arc cosine of the specified value in radians.

• Example:

  SELECT acos(-1);
  -- Returns 3.141592653589793

asin

• Command format

asin(x)

• Command description: Calculates the arc sine of a number.

• Parameter description:

  x: Required. The numeric value for which the arc sine is calculated. It must be within the range -1 to 1, inclusive.

• Return value description: The function returns the arc sine of the specified value in radians.

• Example:

  SELECT asin(-1);
  -- Returns -1.5707963267948966

atan

• Command format

atan(x)

• Command description: Calculates the arc tangent of a number.

• Parameter description:

  x: Required. The numeric value for which the arc tangent is calculated.

• Example:

  SELECT atan(-2);
  -- Returns -1.1071487177940904

atan2

• Command format

atan2(y, x)

• Command description: Calculates the arc tangent of the quotient of two numbers.

• Parameter description:

  y: Required. The numerator in the quotient.

  x: Required. The denominator in the quotient.

• Return value description: The function returns the arc tangent of the quotient of y and x in radians.

• Example:

  SELECT atan2(1, 0);
  -- Returns 1.5707963267948966

cos

• Command format

cos(x)

• Command description: Calculates the cosine of a number.

• Parameter description:

  x: Required. The numeric value for which the cosine is calculated, in radians.

• Return value description: The function returns the cosine of the specified radians.

• Example:

  SELECT cos(2.5);
  -- Returns -0.8011436155469337

cot

• Command format

cot(x)

• Command description: Calculates the cotangent of a number.

• Parameter description:

  x: Required. The numeric value for which the cotangent is calculated, in radians.

• Return value description: The function returns the cotangent of the specified radians.

• Example:

  SELECT cot(2.5);
  -- Returns -1.3386481283041516

random

• Command format

random()

• Command description: Generates a random number within the range of 0.0 (inclusive) to 1.0 (exclusive).

• Return value description: The function returns a random number between 0 (inclusive) and 1 (exclusive).

• Example:

  SELECT random();
  -- Returns a random number between 0 and 1

setseed

• Command format

setseed(dp)

• Command description: Sets the seed for generating random numbers within the range of -1.0 to 1.0, inclusive.

• Parameter description:

  dp: Required. The seed value for the random number generator. It must be between -1 and 1, inclusive.

• Return value description: This function does not return a value.

sin

• Command format

sin(x)

• Command description: Calculates the sine of a number.

• Parameter description:

  x: Required. The numeric value for which the sine is calculated, in radians.

• Return value description: The function returns the sine of the specified radians.

• Example:

  SELECT sin(2.5);
  -- Returns 0.5984721441039564

tan

• Command format

tan(x)

• Command description: Calculates the tangent of a number.

• Parameter description:

  x: Required. The numeric value for which the tangent is calculated, in radians.

• Return value description: The function returns the tangent of the specified radians.

• Example:

  SELECT tan(2.5);
  -- Returns -0.7470222972386602

to_char

• Command format

to_char(timestamp, text)

• Command description: Converts a timestamp to a string using a specified format.

• Parameter description:

  timestamp: The timestamp value to be formatted as a string.

  text: Required. The format template for the conversion.

• Return value description: The function returns a string formatted from the specified timestamp according to the provided template.

• Example:

  SELECT to_char(timestamp '2023-01-01 17:31:12.112', 'YYYY/MM/DD HH12:MI:SS');
  -- Returns '2023/01/01 05:31:12'

to_char

• Command format

to_char(interval, text)

• Command description: Converts an interval to a string using a specified format.

• Parameter description:

  interval: The interval value to be formatted as a string.

  text: Required. The format template for the conversion.

• Return value description: The function returns a string formatted from the specified interval according to the provided template.

• Example:

  SELECT to_char(interval '20 hours 20 minutes', 'HH24:MI:SS');
  -- Returns '20:20:00'

to_char

• Command format

to_char(int, text)

• Command description: Converts an integer to a string using a specified format.

• Parameter description:

  int: The integer value to be formatted as a string.

  text: Required. The format template for the conversion.

• Return value description: The function returns a string formatted from the specified integer according to the provided template.

• Example:

  SELECT to_char(12345678.9, '999G999G999G999G999.900');
  -- Returns '12,345,678.900'

to_char

• Command format

to_char(double precision, text)

• Command description: Converts a real number or double precision value to a string using a specified format.

• Parameter description:

  double precision: The numeric value to be formatted as a string.

  text: Required. The format template for the conversion.

• Return value description: The function returns a string formatted from the specified real number or double precision value according to the provided template.

• Example:

  SELECT to_char(12345678.9, 'FM999G999G999G999G999.900');
  -- Returns '12,345,678.900'

to_char

• Command format

to_char(numeric, text)

• Command description: Converts a numeric value to a string using a specified format.

• Parameter description:

  numeric: The numeric value to be formatted as a string.

  text: Required. The format template for the conversion.

• Return value description: The function returns a string formatted from the specified numeric value according to the provided template.

• Example:

  SELECT to_char(12345678.9, '999G999G999G999G999.900');
  -- Returns '12,345,678.900'

to_date

• Command format

to_date(text, text)

• Command description: Converts a string to a date using a specified format.

• Parameter description:

  text: The string representing a date.

  text: Required. The format template for the conversion.

• Return value description: The function returns a date value formatted from the specified string according to the provided template.

• Example:

  SELECT to_date('2023-01-01', 'YYYY-MM-DD');
  -- Returns '2023-01-01'

to_number

• Command format

to_number(text, text)

• Command description: Converts a string to a numeric value using a specified format.

• Parameter description:

  text: The string representing a numeric value.

  text: Required. The format template for the conversion.

• Return value description: The function returns a numeric value formatted from the specified string according to the provided template.

• Example:

  SELECT to_number('123,456,789.012-', '999G999G999D999S');
  -- Returns -123456789.012

to_timestamp

• Command format

to_timestamp(text, text)

• Command description: Converts a string to a timestamp using a specified format.

• Parameter description:

  text: The string representing a timestamp.

  text: Required. The format template for the conversion.

• Return value description: The function returns a timestamp value formatted from the specified string according to the provided template.

• Example:

  SELECT to_timestamp('2023-01-01', 'YYYY-MM-DD');
  -- Returns '2023-01-01 00:00:00+08'

to_timestamp

• Command format

to_timestamp(double precision)

• Command description: Converts Unix epoch time to a timestamp.

• Parameter description:

  double precision: The Unix epoch time in seconds since January 1, 1970, 00:00:00 UTC.

• Return value description: The function returns a timestamp value corresponding to the specified Unix epoch time.

• Example:

  SELECT to_timestamp(2152704873);
  -- Returns '2038-03-20 21:34:33.0'