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)

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

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 ])

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)]

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";

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

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

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)

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

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.