github.com/matrixorigin/matrixone@v0.7.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,null, null, null, null);
    21  
    22  -- @case
    23  -- @desc:test for month func
    24  -- @label:bvt
    25  select month(d),month(dt) from t1;
    26  select month(c),month(vc) from t1;
    27  
    28  -- @case
    29  -- @desc:test for weekday func
    30  -- @label:bvt
    31  select weekday(d),weekday(dt) from t1;
    32  
    33  
    34  select weekday(c),weekday(vc) from t1;
    35  
    36  -- select week(d),week(dt) from t1;
    37  -- select day(d),day(dt) from t1;
    38  -- select dayofmonth(d),dayofmonth(dt) from t1;
    39  -- select date(c),date(vc) from t1;
    40  -- select dayofyear(d),dayofyear(dt) from t1;
    41  -- select hour(d),hour(dt) from t1;
    42  -- select minute(d),minute(dt) from t1;
    43  -- select second(d),second(dt) from t1;
    44  
    45  -- @case
    46  -- @desc: test for month with func max,min,etc
    47  -- @label:bvt
    48  select max(month(d)),max(month(dt)) from t1;
    49  select min(month(d)),min(month(d)) from t1;
    50  select avg(month(d)),avg(month(d)) from t1;
    51  
    52  select sum(month(d)),sum(month(d)) from t1;
    53  
    54  -- @case
    55  -- @desc: test for weekday with func max,min,etc
    56  -- @label:bvt
    57  select max(weekday(d)),max(weekday(dt)) from t1;
    58  select min(weekday(d)),min(weekday(d)) from t1;
    59  select avg(weekday(d)),avg(weekday(d)) from t1;
    60  select sum(weekday(d)),sum(weekday(d)) from t1;
    61  
    62  -- @case
    63  -- @desc: test for month with distinct
    64  -- @label:bvt
    65  select distinct(month(d)) from t1;
    66  
    67  -- @case
    68  -- @desc: test for month with operators
    69  -- @label:bvt
    70  select id,c,vc from t1 where month(d) > 2;
    71  select id,c,vc from t1 where month(d) < 3;
    72  select id,c,vc from t1 where month(d) <> 4;
    73  
    74  drop table t1;