github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimizer/datetime.result (about)

     1  drop database if exists d1;
     2  create database d1;
     3  use d1;
     4  drop table if exists t1;
     5  create table t1(c1 int primary key, c2 datetime);
     6  explain select * from t1 where date_format(c2,'%Y-%m-%d')='2024-04-01';
     7  QUERY PLAN
     8  Project
     9    ->  Table Scan on d1.t1
    10          Filter Cond: t1.c2 BETWEEN 2024-04-01 00:00:00 AND 2024-04-01 23:59:59
    11  explain select * from t1 where date_format(c2,'%Y%m%d')='20240401';
    12  QUERY PLAN
    13  Project
    14    ->  Table Scan on d1.t1
    15          Filter Cond: t1.c2 BETWEEN 2024-04-01 00:00:00 AND 2024-04-01 23:59:59
    16  explain select * from t1 where date_format(c2,'%Y:%m:%d')='2024:04:01';
    17  QUERY PLAN
    18  Project
    19    ->  Table Scan on d1.t1
    20          Filter Cond: (date_format(t1.c2, '%Y:%m:%d') = '2024:04:01')
    21  explain select * from t1 where date_format(c2,'%Y-%m-%d')='aa';
    22  QUERY PLAN
    23  Project
    24    ->  Table Scan on d1.t1
    25          Filter Cond: (date_format(t1.c2, '%Y-%m-%d') = 'aa')
    26  explain select * from t1 where date_format(c2,'%d')='2024:04:01';
    27  QUERY PLAN
    28  Project
    29    ->  Table Scan on d1.t1
    30          Filter Cond: (date_format(t1.c2, '%d') = '2024:04:01')
    31  explain select * from t1 where date_format(c2,'%Y:%m:%d')='2024-04-01';
    32  QUERY PLAN
    33  Project
    34    ->  Table Scan on d1.t1
    35          Filter Cond: (date_format(t1.c2, '%Y:%m:%d') = '2024-04-01')
    36  explain select * from t1 where date_format(c2,'%Y')='2024';
    37  QUERY PLAN
    38  Project
    39    ->  Table Scan on d1.t1
    40          Filter Cond: t1.c2 BETWEEN 2024-01-01 00:00:00 AND 2024-12-31 23:59:59
    41  drop database if exists d1;