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:

  1. 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:

  1. -- Flush the table test --
  2. select flush_table("test");
  4. -- Schedule a compaction for table test --
  5. 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:

  1. 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:

  1. 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:

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

Output:

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

::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:

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

Output:

  1. +-----------------------------+
  2. | Utf8("2021-07-01 00:00:00") |
  3. +-----------------------------+
  4. | 2021-07-01 08:00:00         |
  5. +-----------------------------+
  6. 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:

  1. 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.