github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_weekday.test (about) 1 #SELECT, 嵌套 2 3 SELECT weekday(date('2007-02-03 03:42:24')); 4 SELECT weekday(date('2023-11-08 13:42')); 5 6 7 #NULL 8 SELECT weekday(NULL); 9 10 11 #DATATYPE 12 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 13 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 14 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 15 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 16 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 17 select weekday(a),weekday(b),weekday(c),weekday(d),weekday(e),weekday(f) from t1; 18 select weekday(g),weekday(h),weekday(i),weekday(k),weekday(l),weekday(m),weekday(n) from t1; 19 drop table t1; 20 21 22 #0.5暂不支持time类型 23 #create table t1(a time) 24 #insert into t1 values("10:03:12"); 25 #insert into t1 values("10:03:12"); 26 #insert into t1 values("10:03:12"); 27 #insert into t1 values("10:03:12"); 28 #select weekday(a) from t1; 29 #drop table t1; 30 31 32 33 SELECT weekday("2015-09-03") as weekday; 34 SELECT weekday('20150904') as weekday; 35 SELECT weekday(1340124) as weekday; 36 SELECT weekday(0.45133) as weekday; 37 SELECT weekday(10) as weekday; 38 SELECT weekday(1=1) as weekday; 39 SELECT weekday('2015-09-30') as weekday; 40 41 SELECT weekday('2015-0') as weekday; 42 43 SELECT weekday('2015-09.30 12') as weekday; 44 SELECT weekday('2015-09-26 08:09:22') AS weekday; 45 -- @bvt:issue 46 47 #EXTREME VALUE 48 49 select weekday("2020-08"); 50 select weekday("0000-00-00"); 51 select weekday("-0001-03-03"); 52 select weekday("10000-03-02"); 53 select weekday("2021-13-09"); 54 select weekday("2020-10-32"); 55 56 57 #算术操作 58 SELECT weekday("2015-09-10")- weekday("2016-04-24"); 59 SELECT weekday("2015-09-10")+ weekday("2016-04-24"); 60 SELECT weekday("2015-09-10")*weekday("2016-04-24"); 61 SELECT weekday("2015-09-10")/weekday("2016-04-24"); 62 63 #比较操作 64 SELECT weekday("2015-09-10")>weekday("2016-04-24"); 65 SELECT weekday("2015-09-10")<weekday("2016-04-24"); 66 SELECT weekday("2015-09-10")=weekday("2016-04-24"); 67 SELECT weekday("2015-09-10")<>weekday("2016-04-24"); 68 SELECT weekday("2015-09-10")!=weekday("2016-04-24"); 69 70 71 72 #SELECT, ORDER BY, GROUP BY 73 CREATE TABLE Product(Product_id INT, Product_name VARCHAR(100) NOT NULL,Buying_price DECIMAL(13, 2) NOT NULL,Selling_price DECIMAL(13, 2) NOT NULL,Selling_Date Date NOT NULL,PRIMARY KEY(Product_id)); 74 INSERT INTO Product(Product_name, Buying_price, Selling_price, Selling_Date) VALUES ('Audi Q8', 10000000.00, 15000000.00, '2018-01-26' ),('Volvo XC40', 2000000.00, 3000000.00, '2018-04-20' ),('Audi A6', 4000000.00, 5000000.00, '2018-07-25' ),('BMW X5', 5000500.00, 7006500.00, '2018-10-18' ),('Jaguar XF', 5000000, 7507000.00, '2019-01-27' ),('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2019-04-01' ),('Jaguar F-PACE', 5000000.00, 7000000.00, '2019-12-26' ),('Porsche Macan', 6500000.00, 8000000.00, '2020-04-16' ) ; 75 SELECT weekday (Selling_Date) weekday, COUNT(Product_id) Product_Sold FROM Product GROUP BY weekday (Selling_Date) ORDER BY weekday (Selling_Date); 76 DROP TABLE Product; 77 78 79 #INSERT 80 81 drop table if exists t1; 82 create table t1(a INT, b date); 83 insert into t1 select weekday("2012-10-12"), "2012-10-12"; 84 insert into t1 select weekday("2004-04-24"), "2004-04-24"; 85 insert into t1 select weekday("2008-12-04"), "2008-12-04"; 86 select * from t1; 87 drop table t1; 88 89 90 91 #WHERE 92 drop table if exists t1; 93 create table t1(a INT, b date); 94 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 95 select * from t1 where weekday(b)<>0; 96 drop table t1; 97 98 99 #ON CONDITION 100 drop table if exists t1; 101 drop table if exists t2; 102 create table t1(a INT, b date); 103 create table t2(a INT, b date); 104 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 105 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 106 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (weekday(t1.b) <> weekday(t2.b)); 107 drop table t1; 108 drop table t2; 109 110 #HAVING 111 drop table if exists t1; 112 create table t1(a INT, b date); 113 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 114 select b from t1 group by b having weekday(b)>0; 115 drop table t1; 116 117 #distinct 118 drop table if exists t1; 119 create table t1(a INT, b date); 120 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 121 select distinct weekday(b) from t1; 122 drop table t1; 123 124 #Create table select 125 #create table t1 select if(1,'1','0'), weekday("2002-08-02"); 126 #select * from t1; 127 #drop table t1; 128 129 130 CREATE TABLE t3(c1 DATE NOT NULL); 131 INSERT INTO t3 VALUES('2000-01-01'); 132 INSERT INTO t3 VALUES('1999-12-31'); 133 INSERT INTO t3 VALUES('2000-01-01'); 134 INSERT INTO t3 VALUES('2006-12-25'); 135 INSERT INTO t3 VALUES('2008-02-29'); 136 SELECT WEEKDAY(c1) FROM t3; 137 DROP TABLE t3; 138 CREATE TABLE t3(c1 DATETIME NOT NULL); 139 INSERT INTO t3 VALUES('2000-01-01'); 140 INSERT INTO t3 VALUES('1999-12-31'); 141 INSERT INTO t3 VALUES('2000-01-01'); 142 INSERT INTO t3 VALUES('2006-12-25'); 143 INSERT INTO t3 VALUES('2008-02-29'); 144 SELECT WEEKDAY(c1) FROM t3; 145 DROP TABLE t3; 146 147 148 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 149 INSERT INTO t3 VALUES('2000-01-01'); 150 INSERT INTO t3 VALUES('1999-12-31'); 151 INSERT INTO t3 VALUES('2000-01-01'); 152 INSERT INTO t3 VALUES('2006-12-25'); 153 INSERT INTO t3 VALUES('2008-02-29'); 154 SELECT WEEKDAY(c1) FROM t3; 155 DROP TABLE t3;