GreptimeDB Does Not Support MySQL Date and Time Functions

[atul-r]

What type of bug is this?

Unexpected error

What subsystems are affected?

Standalone mode

Minimal reproduce step

When using MySQL functions for date and time manipulation with GreptimeDB, the queries fail with errors indicating that the functions are not recognized.
(I am using mysql shell to issue this queries)

The table schema I'm working with is as follows:

CREATE TABLE IF NOT EXISTS `grpc_latencies` (
  `ts` TIMESTAMP(3) NOT NULL,
  `host` STRING NULL,
  `method_name` STRING NULL,
  `latency` DOUBLE NULL,
  TIME INDEX (`ts`),
  PRIMARY KEY (`host`, `method_name`)
)
ENGINE=mito
WITH(
  append_mode = 'true'
);

Problematic Queries:

SELECT DATE(ts) AS ts, AVG(latency) AS `AVG(latency)` FROM mydb.grpc_latencies GROUP BY DATE(ts) ORDER BY `AVG(latency)` DESC LIMIT 10000;

ERROR: 1815 (HY000): (PlanQuery): Failed to plan SQL: Error during planning: Invalid function 'date'.
Did you mean 'MAX'?

SELECT HOUR(ts) AS ts, AVG(latency) AS `AVG(latency)` FROM mydb.grpc_latencies GROUP BY HOUR(ts) ORDER BY `AVG(latency)` DESC LIMIT 1000;

ERROR: 1815 (HY000): (PlanQuery): Failed to plan SQL: Error during planning: Invalid function 'hour'.
Did you mean 'CORR'?

SELECT MINUTE(ts) AS ts, AVG(latency) AS `AVG(latency)` FROM mydb.grpc_latencies GROUP BY MINUTE(ts) ORDER BY `AVG(latency)` DESC LIMIT 1000;

ERROR: 1815 (HY000): (PlanQuery): Failed to plan SQL: Error during planning: Invalid function 'minute'.
Did you mean 'MIN'?

SELECT SECOND(ts) AS ts, AVG(latency) AS `AVG(latency)` FROM mydb.grpc_latencies GROUP BY SECOND(ts) ORDER BY `AVG(latency)` DESC LIMIT 1000;

ERROR: 1815 (HY000): (PlanQuery): Failed to plan SQL: Error during planning: Invalid function 'second'.
Did you mean 'COUNT'?

SELECT MAKEDATE(YEAR(ts), 1) + INTERVAL QUARTER(ts) QUARTER - INTERVAL 1 QUARTER AS ts, AVG(latency) AS `AVG(latency)`  FROM mydb.grpc_latencies  GROUP BY MAKEDATE(YEAR(ts), 1) + INTERVAL QUARTER(ts) QUARTER - INTERVAL 1 QUARTER  ORDER BY `AVG(latency)` DESC  LIMIT 1000

Failed to plan SQL: Error during planning: Invalid function 'makedate'.
Did you mean 'MAX'?

I assume other date and time function might not work as well. A complete list can be seen here

What did you expect to see?

These queries should correctly execute and return aggregated results based on date and time manipulations if the functions are supported.

What did you see instead?

Errors like

Failed to plan SQL: Error during planning: Invalid function 'makedate'. Did you mean 'MAX'?

ERROR: 1815 (HY000): (PlanQuery): Failed to plan SQL: Error during planning: Invalid function 'second'. Did you mean 'COUNT'?

What operating system did you use?

mysql-shell-9.0.1-windows, greptime/greptimedb:v0.9.1

What version of GreptimeDB did you use?

0.9.1

Relevant log output and stack trace

No response