Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.greptime.com/reference/sql/functions/overview/ below:

Functions | GreptimeDB Documentation

Since GreptimeDB's query engine is built based on Apache Arrow DataFusion, GreptimeDB inherits all built-in functions in DataFusion. These functions include:

• Aggregate functions: such as COUNT, SUM, MIN, MAX, etc. For a detailed list, please refer to Aggregate Functions
• Scalar functions: such as ABS, COS, FLOOR, etc. For a detailed list, please refer to Scalar Functions
• Window functions: performs a calculation across a set of table rows that are somehow related to the current row. For a detailed list, please refer to Window Functions

To find all the DataFusion functions, please refer to DataFusion Functions.

arrow_cast function is from DataFusion's arrow_cast. It's illustrated as:

arrow_cast(expression, datatype)

Where the datatype can be any valid Arrow data type in this list. The four timestamp types are:

• Timestamp(Second, None)
• Timestamp(Millisecond, None)
• Timestamp(Microsecond, None)
• Timestamp(Nanosecond, None)

(Notice that the None means the timestamp is timezone naive)

DataFusion String Function.

GreptimeDB provides:

• matches_term(expression, term) for full text search.

For details, read the Query Logs.

DataFusion Math Function.

GreptimeDB provides:

• 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(x, y) to get the remainder of a number divided by another number:

SELECT mod(18, 4);

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

DataFusion Time and Date Function. GreptimeDB provides:

• date_add
• date_sub
• date_format
• to_unixtime
• timezone

• 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(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(expression, fmt) to format Timestamp, Date, or DateTime into string by the format:

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(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() to retrieve the current session timezone:

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

• 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     |
+

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

GreptimeDB provide functions for jsons. Learn more about these functions

GreptimeDB provide functions for geo-index, trajectory analytics. Learn more about these functions

GreptimeDB supports vector functions for vector operations, such as distance calculation, similarity measurement, etc. Learn more about these functions

GreptimeDB supports some approximate functions for data analysis, such as approximate count distinct(hll), approximate quantile(uddsketch), etc. Learn more about these functions

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4