GreptimeDB Functions

• String Functions
  • regexp_extract
  • locate
  • elt
  • field
  • insert
  • space
  • format
• Math Functions
  • clamp
  • mod
• Date and Time Functions
  • date_add
  • date_sub
  • date_format
  • to_unixtime
  • timezone
• System Functions
• Admin Functions

String Functions

DataFusion String Function.

GreptimeDB provides:

• matches_term(expression, term) for full text search. For details, read the Fulltext Search.
• regexp_extract(str, regexp) to extract the first substring in a string that matches a regular expression. Returns NULL if no match is found.

MySQL-Compatible String Functions:

GreptimeDB also provides the following MySQL-compatible string functions:

• locate(substr, str[, pos]) - Returns the position of the first occurrence of substring
• elt(N, str1, str2, ...) - Returns the Nth string from the list
• field(str, str1, str2, ...) - Returns the index of the first string that matches
• insert(str, pos, len, newstr) - Inserts a substring at a specified position
• space(N) - Returns a string of N space characters
• format(X, D) - Formats a number with thousand separators and D decimal places

regexp_extract

Extracts the first substring in a string that matches a regular expression. Returns NULL if no match is found.

regexp_extract(str, regexp)

Arguments:

• str: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
• regexp: Regular expression to match against. Can be a constant, column, or function.

Note on Escaping:

GreptimeDB's regex escape behavior differs between MySQL and PostgreSQL compatibility modes:

• MySQL mode: Requires double backslashes for escape sequences (e.g., \\d, \\s)
• PostgreSQL mode: Single backslashes work by default (e.g., \d, \s), or use E'' prefix for consistency with MySQL (e.g., E'\\d')

Examples:

SELECT regexp_extract('version 1.2.3', '\d+\.\d+\.\d+');
-- Returns: 1.2.3

SELECT regexp_extract('Phone: 123-456-7890', '\d{3}-\d{3}-\d{4}');
-- Returns: 123-456-7890

SELECT regexp_extract('no match here', '\d+\.\d+\.\d+');
-- Returns: NULL

locate

Returns the position of the first occurrence of substring substr in string str. Optionally, you can specify a starting position pos. Returns 0 if the substring is not found.

locate(substr, str[, pos])

Arguments:

• substr: The substring to search for.
• str: The string to search in.
• pos (optional): The position to start searching from (1-based). If omitted, searching starts from the beginning.

Examples:

SELECT locate('world', 'hello world');
-- Returns: 7

SELECT locate('o', 'hello world', 6);
-- Returns: 8 (finds the second 'o')

SELECT locate('xyz', 'hello world');
-- Returns: 0 (not found)

elt

Returns the Nth string from a list of strings. Returns NULL if N is less than 1, greater than the number of strings, or NULL.

elt(N, str1, str2, str3, ...)

Arguments:

• N: The index of the string to return (1-based).
• str1, str2, str3, ...: The list of strings.

Examples:

SELECT elt(2, 'apple', 'banana', 'cherry');
-- Returns: banana

SELECT elt(0, 'apple', 'banana', 'cherry');
-- Returns: NULL

field

Returns the index (1-based) of the first string that matches str in the list. Returns 0 if no match is found or if str is NULL.

field(str, str1, str2, str3, ...)

Arguments:

• str: The string to search for.
• str1, str2, str3, ...: The list of strings to search in.

Examples:

SELECT field('banana', 'apple', 'banana', 'cherry');
-- Returns: 2

SELECT field('grape', 'apple', 'banana', 'cherry');
-- Returns: 0 (not found)

insert

Inserts a substring into a string at a specified position, replacing a specified number of characters.

insert(str, pos, len, newstr)

Arguments:

• str: The original string.
• pos: The position to start inserting (1-based).
• len: The number of characters to replace.
• newstr: The string to insert.

Examples:

SELECT insert('Quadratic', 3, 4, 'What');
-- Returns: QuWhattic

SELECT insert('Quadratic', 3, 100, 'What');
-- Returns: QuWhat (replaces to end of string)

space

Returns a string consisting of N space characters.

space(N)

Arguments:

• N: The number of spaces to return. Returns empty string if N is negative.

Examples:

SELECT space(5);
-- Returns: '     ' (5 spaces)

SELECT concat('hello', space(3), 'world');
-- Returns: 'hello   world'

format

Formats a number with thousand separators and a specified number of decimal places.

format(X, D)

Arguments:

• X: The number to format.
• D: The number of decimal places (0-30).

Examples:

SELECT format(1234567.891, 2);
-- Returns: 1,234,567.89

SELECT format(1234567.891, 0);
-- Returns: 1,234,568

Math Functions

DataFusion Math Function.

GreptimeDB provides:

clamp

• clamp(value, lower, upper) to restrict a given value between a lower and upper bound:

SELECT CLAMP(10, 0, 1);

+------------------------------------+
| clamp(Int64(10),Int64(0),Int64(1)) |
+------------------------------------+
|                                  1 |
+------------------------------------+

SELECT CLAMP(0.5, 0, 1)

+---------------------------------------+
| clamp(Float64(0.5),Int64(0),Int64(1)) |
+---------------------------------------+
|                                   0.5 |
+---------------------------------------+

mod

• mod(x, y) to get the remainder of a number divided by another number:

SELECT mod(18, 4);

+-------------------------+
| mod(Int64(18),Int64(4)) |
+-------------------------+
|                       2 |
+-------------------------+

Date and Time Functions

DataFusion Time and Date Function. GreptimeDB provides:

• date_add
• date_sub
• date_format
• to_unixtime
• timezone

date_add

• date_add(expression, interval) to add an interval value to Timestamp, Date, or DateTime

SELECT date_add('2023-12-06'::DATE, '3 month 5 day');

+----------------------------------------------------+
| date_add(Utf8("2023-12-06"),Utf8("3 month 5 day")) |
+----------------------------------------------------+
| 2024-03-11                                         |
+----------------------------------------------------+

date_sub

• date_sub(expression, interval) to subtract an interval value to Timestamp, Date, or DateTime

SELECT date_sub('2023-12-06 07:39:46.222'::TIMESTAMP_MS, '5 day'::INTERVAL);

+-----------------------------------------------------------------------------------------------------------------------------------------+
| date_sub(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),IntervalMonthDayNano("92233720368547758080")) |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| 2023-12-01 07:39:46.222000                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------+

date_format

• date_format(expression, fmt) to format Timestamp, Date, or DateTime into string by the format:

Supports Date32, Date64, and all Timestamp types.

SELECT date_format('2023-12-06 07:39:46.222'::TIMESTAMP, '%Y-%m-%d %H:%M:%S:%3f');

+-----------------------------------------------------------------------------------------------------------------------------+
| date_format(arrow_cast(Utf8("2023-12-06 07:39:46.222"),Utf8("Timestamp(Millisecond, None)")),Utf8("%Y-%m-%d %H:%M:%S:%3f")) |
+-----------------------------------------------------------------------------------------------------------------------------+
| 2023-12-06 07:39:46:222                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------+

Supported specifiers refer to the chrono::format::strftime module.

to_unixtime

• to_unixtime(expression) to convert the expression into the Unix timestamp in seconds. The argument can be integers (Unix timestamp in milliseconds), Timestamp, Date, DateTime, or String. If the argument is the string type, the function will first try to convert it into a DateTime, Timestamp, or Date.

select to_unixtime('2023-03-01T06:35:02Z');

+-------------------------------------------+
| to_unixtime(Utf8("2023-03-01T06:35:02Z")) |
+-------------------------------------------+
|                                1677652502 |
+-------------------------------------------+

select to_unixtime('2023-03-01'::date);

+---------------------------------+
| to_unixtime(Utf8("2023-03-01")) |
+---------------------------------+
|                      1677628800 |
+---------------------------------+

timezone

• timezone() to retrieve the current session timezone:

select timezone();

+------------+
| timezone() |
+------------+
| UTC        |
+------------+

System Functions

• isnull(expression) to check whether an expression is NULL:

 SELECT isnull(1);

 +------------------+
| isnull(Int64(1)) |
+------------------+
|                0 |
+------------------+

SELECT isnull(NULL);

+--------------+
| isnull(NULL) |
+--------------+
|            1 |
+--------------+

• build() retrieves the GreptimeDB build info.
• version() retrieves the GreptimeDB version.
• database() retrieves the current session database:

select database();

+------------+
| database() |
+------------+
| public     |
+------------+

Admin Functions

GreptimeDB provides ADMIN statement to run the administration functions, please refer to ADMIN reference.