github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_dayofyear.test

#SELECT, 嵌套

SELECT dayofyear(date('2007-02-03 03:42:24'));


#NULL
SELECT dayofyear(NULL);


#DATATYPE


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));
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");
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");
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");
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");
insert into t1 values(1, 1, 2, 34, 5, 5.5, 31.133, 14.314, "2023-11-08", "2023-11-08 15:03", "2023-11-08 15:03", "abcads3c", "dcdff");
select dayofyear(a),dayofyear(b),dayofyear(c),dayofyear(d),dayofyear(e),dayofyear(f) from t1;
select dayofyear(g),dayofyear(h),dayofyear(i),dayofyear(k),dayofyear(l),dayofyear(m),dayofyear(n) from t1;
drop table t1;


#0.5暂不支持time类型
#create table t1(a time)
#insert into t1 values("10:03:12");
#insert into t1 values("10:03:12");
#insert into t1 values("10:03:12");
#insert into t1 values("10:03:12");
#select dayofyear(a) from t1;
#drop table t1;


SELECT dayofyear("2015-09-03") as dayofyear;
SELECT dayofyear(20150904) as dayofyear;
SELECT dayofyear(1340124) as dayofyear;
SELECT dayofyear(0.45133) as dayofyear;
SELECT dayofyear(10) as dayofyear;
SELECT dayofyear(1=1) as dayofyear;
SELECT dayofyear('2015-09-30') as dayofyear;

SELECT dayofyear('2015-0') as dayofyear;

SELECT dayofyear('2015-09.30 12') as dayofyear;
SELECT dayofyear('2015-09-26 08:09:22') AS dayofyear;

#EXTREME VALUE

select dayofyear("2020-08");
select dayofyear("0000-00-00");
select dayofyear("-0001-03-03");
select dayofyear("10000-03-02");
select dayofyear("2021-13-09");
select dayofyear("2020-10-32");


#算术操作
SELECT dayofyear("2015-09-10")- dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")+ dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")*dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")/dayofyear("2016-04-24");

#比较操作
SELECT dayofyear("2015-09-10")>dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")<dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")=dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")<>dayofyear("2016-04-24");
SELECT dayofyear("2015-09-10")!=dayofyear("2016-04-24");



#SELECT, ORDER BY, GROUP BY
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));
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' ) ;
SELECT  dayofyear (Selling_Date) dayofyear,  COUNT(Product_id) Product_Sold FROM Product GROUP BY dayofyear (Selling_Date) ORDER BY dayofyear (Selling_Date);
DROP TABLE Product;


#INSERT
drop table if exists t1;
create table t1(a INT,  b date);
insert into t1 select dayofyear("2012-10-12"), "2012-10-12";
insert into t1 select dayofyear("2004-04-24"), "2004-04-24";
insert into t1 select dayofyear("2008-12-04"), "2008-12-04";
select * from t1;
drop table t1;



#WHERE
drop table if exists t1;
create table t1(a INT,  b date);
insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
select * from t1 where dayofyear(b)<>0;
drop table t1;


#ON CONDITION
drop table if exists t1;
drop table if exists t2;
create table t1(a INT,  b date);
create table t2(a INT,  b date);
insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12");
SELECT t1.a, t2.a FROM t1 JOIN t2 ON (dayofyear(t1.b) <> dayofyear(t2.b));
drop table t1;
drop table t2;


#HAVING
drop table if exists t1;
create table t1(a INT,  b date);
insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
select b from t1 group by b having dayofyear(b)>0;
drop table t1;

#distinct
drop table if exists t1;
create table t1(a INT,  b date);
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");
select distinct dayofyear(b) from t1;
drop table t1;

#Create table select
#create table t1 select if(1,'1','0'), dayofyear("2002-08-02");
#select * from t1;
#drop table t1;



CREATE TABLE t3(c1 DATE NOT NULL);
INSERT INTO t3 VALUES('2000-01-01');
INSERT INTO t3 VALUES('1999-12-31');
INSERT INTO t3 VALUES('2000-01-01');
INSERT INTO t3 VALUES('2006-12-25');
INSERT INTO t3 VALUES('2008-02-29');
SELECT DAYOFYEAR(c1) FROM t3;
DROP TABLE t3;
CREATE TABLE t3(c1 DATETIME NOT NULL);
INSERT INTO t3 VALUES('2000-01-01');
INSERT INTO t3 VALUES('1999-12-31');
INSERT INTO t3 VALUES('2000-01-01');
INSERT INTO t3 VALUES('2006-12-25');
INSERT INTO t3 VALUES('2008-02-29');
SELECT DAYOFYEAR(c1) FROM t3;
DROP TABLE t3;
CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
INSERT INTO t3 VALUES('2000-01-01');
INSERT INTO t3 VALUES('1999-12-31');
INSERT INTO t3 VALUES('2000-01-01');
INSERT INTO t3 VALUES('2006-12-25');
INSERT INTO t3 VALUES('2008-02-29');
SELECT DAYOFYEAR(c1) FROM t3;
DROP TABLE t3;