github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_date.result (about) 1 drop table if exists t1; 2 create table t1 (id int,d date, dt datetime,c char(10),vc varchar(20)); 3 insert into t1 values (1,"2021-01-13", "2021-01-13 13:00:00", "2021-12-15", "2021-12-16"); 4 insert into t1 values (1,"2021-01-31", "2021-01-31 13:00:00", "2021-12-15", "2021-12-16"); 5 insert into t1 values (2,"2022-02-15", "2022-02-15 18:54:29", "2021-02-15", "2021-02-15"); 6 insert into t1 values (2,"2022-02-28", "2022-02-28 18:54:29", "2021-02-15", "2021-02-15"); 7 insert into t1 values (3,"2000-02-29", "2000-02-29 18:54:29", "2021-02-15", "2021-02-15"); 8 insert into t1 values (4,"2023-03-17", "2021-02-17 23:54:59", "2021-03-17", "2021-03-17"); 9 insert into t1 values (5,"1985-04-18", "1985-04-18 00:00:01", "1985-04-18", "1985-04-18"); 10 insert into t1 values (6,"1987-05-20", "1987-05-20 22:59:59", "1987-05-20", "1987-05-20"); 11 insert into t1 values (7,"1989-06-22", "1989-06-22 15:00:30", "1989-06-22", "1989-06-22"); 12 insert into t1 values (8,"1993-07-25", "1987-07-25 03:04:59", "1993-07-25", "1993-07-25"); 13 insert into t1 values (9,"1995-08-27", "1987-08-27 04:32:33", "1995-08-27", "1995-08-27"); 14 insert into t1 values (10,"1999-09-30", "1999-09-30 10:11:12", "1999-09-30", "1999-09-30"); 15 insert into t1 values (11,"2005-10-30", "2005-10-30 18:18:59", "2005-10-30", "2005-10-30"); 16 insert into t1 values (12,"2008-11-30", "2008-11-30 22:59:59", "2008-11-30", "2008-11-30"); 17 insert into t1 values (13,"2013-12-01", "2013-12-01 22:59:59", "2013-12-01", "2013-12-01"); 18 insert into t1 values (14,null, null, null, null); 19 select month(d),month(dt) from t1; 20 month(d) month(dt) 21 1 1 22 1 1 23 2 2 24 2 2 25 2 2 26 3 2 27 4 4 28 5 5 29 6 6 30 7 7 31 8 8 32 9 9 33 10 10 34 11 11 35 12 12 36 null null 37 select month(c),month(vc) from t1; 38 month(c) month(vc) 39 12 12 40 12 12 41 2 2 42 2 2 43 2 2 44 3 3 45 4 4 46 5 5 47 6 6 48 7 7 49 8 8 50 9 9 51 10 10 52 11 11 53 12 12 54 null null 55 select weekday(d),weekday(dt) from t1; 56 weekday(d) weekday(dt) 57 2 2 58 6 6 59 1 1 60 0 0 61 1 1 62 4 2 63 3 3 64 2 2 65 3 3 66 6 5 67 6 3 68 3 3 69 6 6 70 6 6 71 6 6 72 null null 73 select weekday(c),weekday(vc) from t1; 74 weekday(c) weekday(vc) 75 2 3 76 2 3 77 0 0 78 0 0 79 0 0 80 2 2 81 3 3 82 2 2 83 3 3 84 6 6 85 6 6 86 3 3 87 6 6 88 6 6 89 6 6 90 null null 91 select max(month(d)),max(month(dt)) from t1; 92 max(month(d)) max(month(dt)) 93 12 12 94 select min(month(d)),min(month(d)) from t1; 95 min(month(d)) min(month(d)) 96 1 1 97 select avg(month(d)),avg(month(d)) from t1; 98 avg(month(d)) avg(month(d)) 99 5.5333 5.5333 100 select sum(month(d)),sum(month(d)) from t1; 101 sum(month(d)) sum(month(d)) 102 83 83 103 select max(weekday(d)),max(weekday(dt)) from t1; 104 max(weekday(d)) max(weekday(dt)) 105 6 6 106 select min(weekday(d)),min(weekday(d)) from t1; 107 min(weekday(d)) min(weekday(d)) 108 0 0 109 select avg(weekday(d)),avg(weekday(d)) from t1; 110 avg(weekday(d)) avg(weekday(d)) 111 3.6667 3.6667 112 select sum(weekday(d)),sum(weekday(d)) from t1; 113 sum(weekday(d)) sum(weekday(d)) 114 55 55 115 select distinct(month(d)) from t1; 116 (month(d)) 117 1 118 2 119 3 120 4 121 5 122 6 123 7 124 8 125 9 126 10 127 11 128 12 129 null 130 select id,c,vc from t1 where month(d) > 2; 131 id c vc 132 4 2021-03-17 2021-03-17 133 5 1985-04-18 1985-04-18 134 6 1987-05-20 1987-05-20 135 7 1989-06-22 1989-06-22 136 8 1993-07-25 1993-07-25 137 9 1995-08-27 1995-08-27 138 10 1999-09-30 1999-09-30 139 11 2005-10-30 2005-10-30 140 12 2008-11-30 2008-11-30 141 13 2013-12-01 2013-12-01 142 select id,c,vc from t1 where month(d) < 3; 143 id c vc 144 1 2021-12-15 2021-12-16 145 1 2021-12-15 2021-12-16 146 2 2021-02-15 2021-02-15 147 2 2021-02-15 2021-02-15 148 3 2021-02-15 2021-02-15 149 select id,c,vc from t1 where month(d) <> 4; 150 id c vc 151 1 2021-12-15 2021-12-16 152 1 2021-12-15 2021-12-16 153 2 2021-02-15 2021-02-15 154 2 2021-02-15 2021-02-15 155 3 2021-02-15 2021-02-15 156 4 2021-03-17 2021-03-17 157 6 1987-05-20 1987-05-20 158 7 1989-06-22 1989-06-22 159 8 1993-07-25 1993-07-25 160 9 1995-08-27 1995-08-27 161 10 1999-09-30 1999-09-30 162 11 2005-10-30 2005-10-30 163 12 2008-11-30 2008-11-30 164 13 2013-12-01 2013-12-01 165 drop table t1;