Skip to main content
Version: 0.8

Functions

Datafusion Functions​

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

In summary, GreptimeDB supports all SQL aggregate functions and scalar functions in DataFusion. Users can safely use these rich built-in functions in GreptimeDB to manipulate and analyze data.

arrow_cast​

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)

GreptimeDB Functions​

Please refer to API documentation

Admin Functions​

GreptimeDB provides some administration functions to manage the database and data:

  • flush_table(table_name) to flush a table's memtables into SST file by table name.
  • flush_region(region_id) to flush a region's memtables into SST file by region id. Find the region id through PARTITIONS table.
  • compact_table(table_name) to schedule a compaction task for a table by table name.
  • compact_region(region_id) to schedule a compaction task for a region by region id.
  • migrate_region(region_id, from_peer, to_peer, [timeout]) to migrate regions between datanodes, please read the Region Migration.
  • procedure_state(procedure_id) to query a procedure state by its id.

For example:

-- Flush the table test --
select flush_table("test");

-- Schedule a compaction for table test --
select compact_table("test");

Time and Date​

date_trunc​

date_trunc function follows the same API with PostgreSQL's date_trunc. It's illustrated as:

date_trunc(precision, source [, time_zone ])

Valid precisions are:

  • microseconds
  • milliseconds
  • second
  • minute
  • hour
  • day
  • week
  • month
  • quarter
  • year
  • decade
  • century
  • millennium

INTERVAL​

The Interval data type allows you to store and manipulate a period of time in years, months, days, hours etc. It's illustrated as:

INTERVAL [fields] [(p)]

Valid types are:

  • YEAR
  • MONTH
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • YEAR TO MONTH
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE TO SECOND

The optional precision p is the number of fraction digits retained in the second field.

For example:

SELECT
 now(),
 now() - INTERVAL '1 year 3 hours 20 minutes'
             AS "3 hours 20 minutes ago of last year";

Output:

+----------------------------+-------------------------------------+
| now()                      | 3 hours 20 minutes ago of last year |
+----------------------------+-------------------------------------+
| 2023-07-05 11:43:37.861340 | 2022-07-05 08:23:37.861340          |
+----------------------------+-------------------------------------+

::timestamp​

The ::timestamp grammar casts the string literal to the timestamp type. All the SQL types are valid to be in the position of timestamp.

Example:

MySQL [(none)]> select '2021-07-01 00:00:00'::timestamp;

Output:

+-----------------------------+
| Utf8("2021-07-01 00:00:00") |
+-----------------------------+
| 2021-07-01 08:00:00         |
+-----------------------------+
1 row in set (0.000 sec)

date_part​

date_part function follows the same API with PostgreSQL's date_part. It's illustrated as:

date_part(field, source)

Some commonly used fields are:

  • century
  • decade
  • year
  • quarter
  • month
  • day
  • dow (day of week)
  • doy (day of year)
  • hour
  • minute
  • second
  • milliseconds
  • microseconds
  • nanoseconds

More Functions​

For more functions related to time and date, please refer to the Time and Date Functions section of the DataFusion documentation.