github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_dayofyear.test (about) 1 #SELECT, 嵌套 2 3 SELECT dayofyear(date('2007-02-03 03:42:24')); 4 5 6 #NULL 7 SELECT dayofyear(NULL); 8 9 10 #DATATYPE 11 12 13 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)); 14 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"); 15 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"); 16 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"); 17 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"); 18 select dayofyear(a),dayofyear(b),dayofyear(c),dayofyear(d),dayofyear(e),dayofyear(f) from t1; 19 select dayofyear(g),dayofyear(h),dayofyear(i),dayofyear(k),dayofyear(l),dayofyear(m),dayofyear(n) from t1; 20 drop table t1; 21 22 23 #0.5暂不支持time类型 24 #create table t1(a time) 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 #insert into t1 values("10:03:12"); 29 #select dayofyear(a) from t1; 30 #drop table t1; 31 32 33 SELECT dayofyear("2015-09-03") as dayofyear; 34 SELECT dayofyear(20150904) as dayofyear; 35 SELECT dayofyear(1340124) as dayofyear; 36 SELECT dayofyear(0.45133) as dayofyear; 37 SELECT dayofyear(10) as dayofyear; 38 SELECT dayofyear(1=1) as dayofyear; 39 SELECT dayofyear('2015-09-30') as dayofyear; 40 41 SELECT dayofyear('2015-0') as dayofyear; 42 43 SELECT dayofyear('2015-09.30 12') as dayofyear; 44 SELECT dayofyear('2015-09-26 08:09:22') AS dayofyear; 45 46 #EXTREME VALUE 47 48 select dayofyear("2020-08"); 49 select dayofyear("0000-00-00"); 50 select dayofyear("-0001-03-03"); 51 select dayofyear("10000-03-02"); 52 select dayofyear("2021-13-09"); 53 select dayofyear("2020-10-32"); 54 55 56 #算术操作 57 SELECT dayofyear("2015-09-10")- dayofyear("2016-04-24"); 58 SELECT dayofyear("2015-09-10")+ dayofyear("2016-04-24"); 59 SELECT dayofyear("2015-09-10")*dayofyear("2016-04-24"); 60 SELECT dayofyear("2015-09-10")/dayofyear("2016-04-24"); 61 62 #比较操作 63 SELECT dayofyear("2015-09-10")>dayofyear("2016-04-24"); 64 SELECT dayofyear("2015-09-10")<dayofyear("2016-04-24"); 65 SELECT dayofyear("2015-09-10")=dayofyear("2016-04-24"); 66 SELECT dayofyear("2015-09-10")<>dayofyear("2016-04-24"); 67 SELECT dayofyear("2015-09-10")!=dayofyear("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 dayofyear (Selling_Date) dayofyear, COUNT(Product_id) Product_Sold FROM Product GROUP BY dayofyear (Selling_Date) ORDER BY dayofyear (Selling_Date); 75 DROP TABLE Product; 76 77 78 #INSERT 79 drop table if exists t1; 80 create table t1(a INT, b date); 81 insert into t1 select dayofyear("2012-10-12"), "2012-10-12"; 82 insert into t1 select dayofyear("2004-04-24"), "2004-04-24"; 83 insert into t1 select dayofyear("2008-12-04"), "2008-12-04"; 84 select * from t1; 85 drop table t1; 86 87 88 89 #WHERE 90 drop table if exists t1; 91 create table t1(a INT, b date); 92 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 93 select * from t1 where dayofyear(b)<>0; 94 drop table t1; 95 96 97 #ON CONDITION 98 drop table if exists t1; 99 drop table if exists t2; 100 create table t1(a INT, b date); 101 create table t2(a INT, b date); 102 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 103 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 104 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (dayofyear(t1.b) <> dayofyear(t2.b)); 105 drop table t1; 106 drop table t2; 107 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 dayofyear(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 dayofyear(b) from t1; 121 drop table t1; 122 123 #Create table select 124 #create table t1 select if(1,'1','0'), dayofyear("2002-08-02"); 125 #select * from t1; 126 #drop table t1; 127 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 DAYOFYEAR(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 DAYOFYEAR(c1) FROM t3; 145 DROP TABLE t3; 146 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 147 INSERT INTO t3 VALUES('2000-01-01'); 148 INSERT INTO t3 VALUES('1999-12-31'); 149 INSERT INTO t3 VALUES('2000-01-01'); 150 INSERT INTO t3 VALUES('2006-12-25'); 151 INSERT INTO t3 VALUES('2008-02-29'); 152 SELECT DAYOFYEAR(c1) FROM t3; 153 DROP TABLE t3;