which function is databend supported for date?

[elderbig]

mysql> select today();
+------------+
| today()    |
+------------+
| 2022-11-12 |
+------------+
1 row in set (0.06 sec)
Read 1 rows, 1.00 B in 0.031 sec., 31.9 rows/sec., 31.90 B/sec.

mysql> select strftime(today());
ERROR 1105 (HY000): Code: 1065, displayText = error: 
  --> SQL:1:8
  |
1 | select strftime(today())
  |        ^^^^^^^^^^^^^^^^^ No function matches the given name: strftime

.
mysql> select isinf(created_at) from gh_log.github_data limit 1;
ERROR 1105 (HY000): Code: 1065, displayText = error: 
  --> SQL:1:8
  |
1 | select isinf(created_at) from gh_log.github_data limit 1
  |        ^^^^^^^^^^^^^^^^^ No function matches the given name: isinf

.
mysql> select datepart(created_at) from gh_log.github_data limit 1;
ERROR 1105 (HY000): Code: 1065, displayText = error: 
  --> SQL:1:8
  |
1 | select datepart(created_at) from gh_log.github_data limit 1
  |        ^^^^^^^^^^^^^^^^^^^^ No function matches the given name: datepart

Some function is seems not supported that is described in https://duckdb.org/docs/sql/functions/date

[elderbig]

I found functions at
https://databend.rs/doc/reference/functions/datetime-functions/datesub
but when I query sql:

SELECT date_sub(YEAR, 1, created_at) from github_data limit 1;

both curl put request and sql query request blocked until I kill mysql client.

[elderbig]

mysql> SELECT to_year(now()) from github_data limit 1;
^C^C -- query aborted

[sundy-li]

That might be a bug. Which version are you using? I can't reproduce it in the latest version.

MySQL [(none)]> CREATE TABLE `github_data`
    ->              (
    ->                           `id`   VARCHAR,
    ->                           `type` VARCHAR,
    ->                           `actor` JSON,
    ->                           `repo` JSON,
    ->                           `payload` JSON,
    ->                           `public` BOOLEAN,
    ->                           `created_at` timestamp,
    ->                           `org` json
    ->              );
Query OK, 0 rows affected (0.004 sec)

MySQL [(none)]> COPY INTO github_data
    -> FROM 'https://data.gharchive.org/2022-09-10-10.json.gz'
    -> FILE_FORMAT = (
    ->   compression = auto
    ->   type = NDJSON
    -> );
Query OK, 0 rows affected (27.814 sec)

MySQL [(none)]> select count() from github_data;
+---------+
| count() |
+---------+
|  117456 |
+---------+
1 row in set (0.006 sec)

MySQL [(none)]> SELECT date_sub(YEAR, 1, created_at) from github_data limit 1;
+----------------------------------------+
| date_sub(year, interval 1, created_at) |
+----------------------------------------+
| 2021-09-10 10:00:00.000000             |
+----------------------------------------+
1 row in set (0.022 sec)

MySQL [(none)]>  SELECT to_year(now()) from github_data limit 1;
+----------------+
| to_year(now()) |
+----------------+
|           2022 |
+----------------+
1 row in set (0.014 sec)

[elderbig]

databend server information, but it is updated from v0.8.83

mysql> status
--------------
mysql  Ver 8.0.31-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:		43
Current database:	gh_log
Current user:		'root'@'127.0.0.1'
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.26-v0.8.107-nightly-9b25785(rust-1.67.0-nightly-2022-11-10T16:28:51.106788692Z) 0
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	0
Db     characterset:	0
Client characterset:	0
Conn.  characterset:	0
TCP port:		3307
Binary data as:		Hexadecimal

[elderbig]

function to_year() do it's work.

mysql> select to_year(created_at),count(*) from (SELECT created_at from github_data limit 10) group by to_year(created_at);
+---------------------+----------+
| to_year(created_at) | count(*) |
+---------------------+----------+
|                2022 |       10 |
+---------------------+----------+
1 row in set (0.34 sec)
Read 9187 rows, 71.77 KiB in 0.299 sec., 30.77 thousand rows/sec., 240.40 KiB/sec.