github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_weekday.test (about) 1 #SELECT, 嵌套 2 3 SELECT weekday(date('2007-02-03 03:42:24')); 4 5 6 #NULL 7 SELECT weekday(NULL); 8 9 10 #DATATYPE 11 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)); 12 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"); 13 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"); 14 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"); 15 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"); 16 select weekday(a),weekday(b),weekday(c),weekday(d),weekday(e),weekday(f) from t1; 17 select weekday(g),weekday(h),weekday(i),weekday(k),weekday(l),weekday(m),weekday(n) from t1; 18 drop table t1; 19 20 21 #0.5暂不支持time类型 22 #create table t1(a time) 23 #insert into t1 values("10:03:12"); 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 #select weekday(a) from t1; 28 #drop table t1; 29 30 31 32 SELECT weekday("2015-09-03") as weekday; 33 SELECT weekday('20150904') as weekday; 34 SELECT weekday(1340124) as weekday; 35 SELECT weekday(0.45133) as weekday; 36 SELECT weekday(10) as weekday; 37 SELECT weekday(1=1) as weekday; 38 SELECT weekday('2015-09-30') as weekday; 39 40 SELECT weekday('2015-0') as weekday; 41 42 SELECT weekday('2015-09.30 12') as weekday; 43 SELECT weekday('2015-09-26 08:09:22') AS weekday; 44 -- @bvt:issue 45 46 #EXTREME VALUE 47 48 select weekday("2020-08"); 49 select weekday("0000-00-00"); 50 select weekday("-0001-03-03"); 51 select weekday("10000-03-02"); 52 select weekday("2021-13-09"); 53 select weekday("2020-10-32"); 54 55 56 #算术操作 57 SELECT weekday("2015-09-10")- weekday("2016-04-24"); 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 62 #比较操作 63 SELECT weekday("2015-09-10")>weekday("2016-04-24"); 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 69 70 71 #SELECT, ORDER BY, GROUP BY 72 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)); 73 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' ) ; 74 SELECT weekday (Selling_Date) weekday, COUNT(Product_id) Product_Sold FROM Product GROUP BY weekday (Selling_Date) ORDER BY weekday (Selling_Date); 75 DROP TABLE Product; 76 77 78 #INSERT 79 80 drop table if exists t1; 81 create table t1(a INT, b date); 82 insert into t1 select weekday("2012-10-12"), "2012-10-12"; 83 insert into t1 select weekday("2004-04-24"), "2004-04-24"; 84 insert into t1 select weekday("2008-12-04"), "2008-12-04"; 85 select * from t1; 86 drop table t1; 87 88 89 90 #WHERE 91 drop table if exists t1; 92 create table t1(a INT, b date); 93 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 94 select * from t1 where weekday(b)<>0; 95 drop table t1; 96 97 98 #ON CONDITION 99 drop table if exists t1; 100 drop table if exists t2; 101 create table t1(a INT, b date); 102 create table t2(a INT, b date); 103 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 104 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 105 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (weekday(t1.b) <> weekday(t2.b)); 106 drop table t1; 107 drop table t2; 108 109 #HAVING 110 drop table if exists t1; 111 create table t1(a INT, b date); 112 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 113 select b from t1 group by b having weekday(b)>0; 114 drop table t1; 115 116 #distinct 117 drop table if exists t1; 118 create table t1(a INT, b date); 119 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"); 120 select distinct weekday(b) from t1; 121 drop table t1; 122 123 #Create table select 124 #create table t1 select if(1,'1','0'), weekday("2002-08-02"); 125 #select * from t1; 126 #drop table t1; 127 128 129 CREATE TABLE t3(c1 DATE NOT NULL); 130 INSERT INTO t3 VALUES('2000-01-01'); 131 INSERT INTO t3 VALUES('1999-12-31'); 132 INSERT INTO t3 VALUES('2000-01-01'); 133 INSERT INTO t3 VALUES('2006-12-25'); 134 INSERT INTO t3 VALUES('2008-02-29'); 135 SELECT WEEKDAY(c1) FROM t3; 136 DROP TABLE t3; 137 CREATE TABLE t3(c1 DATETIME NOT NULL); 138 INSERT INTO t3 VALUES('2000-01-01'); 139 INSERT INTO t3 VALUES('1999-12-31'); 140 INSERT INTO t3 VALUES('2000-01-01'); 141 INSERT INTO t3 VALUES('2006-12-25'); 142 INSERT INTO t3 VALUES('2008-02-29'); 143 SELECT WEEKDAY(c1) FROM t3; 144 DROP TABLE t3; 145 146 147 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 148 INSERT INTO t3 VALUES('2000-01-01'); 149 INSERT INTO t3 VALUES('1999-12-31'); 150 INSERT INTO t3 VALUES('2000-01-01'); 151 INSERT INTO t3 VALUES('2006-12-25'); 152 INSERT INTO t3 VALUES('2008-02-29'); 153 SELECT WEEKDAY(c1) FROM t3; 154 DROP TABLE t3;