github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_date.sql (about)

     1  -- @suite
     2  -- @setup
     3  drop table if exists t1;
     4  create table t1 (id int,d date, dt datetime,c char(10),vc varchar(20));
     5  insert into t1 values (1,"2021-01-13", "2021-01-13 13:00:00", "2021-12-15", "2021-12-16");
     6  insert into t1 values (1,"2021-01-31", "2021-01-31 13:00:00", "2021-12-15", "2021-12-16");
     7  insert into t1 values (2,"2022-02-15", "2022-02-15 18:54:29", "2021-02-15", "2021-02-15");
     8  insert into t1 values (2,"2022-02-28", "2022-02-28 18:54:29", "2021-02-15", "2021-02-15");
     9  insert into t1 values (3,"2000-02-29", "2000-02-29 18:54:29", "2021-02-15", "2021-02-15");
    10  insert into t1 values (4,"2023-03-17", "2021-02-17 23:54:59", "2021-03-17", "2021-03-17");
    11  insert into t1 values (5,"1985-04-18", "1985-04-18 00:00:01", "1985-04-18", "1985-04-18");
    12  insert into t1 values (6,"1987-05-20", "1987-05-20 22:59:59", "1987-05-20", "1987-05-20");
    13  insert into t1 values (7,"1989-06-22", "1989-06-22 15:00:30", "1989-06-22", "1989-06-22");
    14  insert into t1 values (8,"1993-07-25", "1987-07-25 03:04:59", "1993-07-25", "1993-07-25");
    15  insert into t1 values (9,"1995-08-27", "1987-08-27 04:32:33", "1995-08-27", "1995-08-27");
    16  insert into t1 values (10,"1999-09-30", "1999-09-30 10:11:12", "1999-09-30", "1999-09-30");
    17  insert into t1 values (11,"2005-10-30", "2005-10-30 18:18:59", "2005-10-30", "2005-10-30");
    18  insert into t1 values (12,"2008-11-30", "2008-11-30 22:59:59", "2008-11-30", "2008-11-30");
    19  insert into t1 values (13,"2013-12-01", "2013-12-01 22:59:59", "2013-12-01", "2013-12-01");
    20  insert into t1 values (14,"2016-11-01", "2016-11-01 10:59", "2016-11-01", "2016-11-01");
    21  insert into t1 values (15,"2023-11-08", "2023-11-08 10:59:", "2023-11-08", "2023-11-08");
    22  insert into t1 values (16,null, null, null, null);
    23  
    24  -- @case
    25  -- @desc:test for month func
    26  -- @label:bvt
    27  select month(d),month(dt) from t1;
    28  select month(c),month(vc) from t1;
    29  
    30  -- @case
    31  -- @desc:test for weekday func
    32  -- @label:bvt
    33  select weekday(d),weekday(dt) from t1;
    34  
    35  
    36  select weekday(c),weekday(vc) from t1;
    37  
    38  -- select week(d),week(dt) from t1;
    39  -- select day(d),day(dt) from t1;
    40  -- select dayofmonth(d),dayofmonth(dt) from t1;
    41  -- select date(c),date(vc) from t1;
    42  -- select dayofyear(d),dayofyear(dt) from t1;
    43  -- select hour(d),hour(dt) from t1;
    44  -- select minute(d),minute(dt) from t1;
    45  -- select second(d),second(dt) from t1;
    46  
    47  -- @case
    48  -- @desc: test for month with func max,min,etc
    49  -- @label:bvt
    50  select max(month(d)),max(month(dt)) from t1;
    51  select min(month(d)),min(month(d)) from t1;
    52  select avg(month(d)),avg(month(d)) from t1;
    53  
    54  select sum(month(d)),sum(month(d)) from t1;
    55  
    56  -- @case
    57  -- @desc: test for weekday with func max,min,etc
    58  -- @label:bvt
    59  select max(weekday(d)),max(weekday(dt)) from t1;
    60  select min(weekday(d)),min(weekday(d)) from t1;
    61  select avg(weekday(d)),avg(weekday(d)) from t1;
    62  select sum(weekday(d)),sum(weekday(d)) from t1;
    63  
    64  -- @case
    65  -- @desc: test for month with distinct
    66  -- @label:bvt
    67  select distinct(month(d)) from t1;
    68  
    69  -- @case
    70  -- @desc: test for month with operators
    71  -- @label:bvt
    72  select id,c,vc from t1 where month(d) > 2;
    73  select id,c,vc from t1 where month(d) < 3;
    74  select id,c,vc from t1 where month(d) <> 4;
    75  
    76  drop table t1;