github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_year.test (about)

     1  # 嵌套
     2  # SELECT YEAR(NOW()) AS Current_Year;
     3  SELECT YEAR(date_add("2021-09-30", interval 200 day)) AS year;
     4  
     5  
     6  # EXTREME VALUE
     7  SELECT YEAR("10000-12-21") as year;
     8  SELECT YEAR("-1234-12-21") as year;
     9  SELECT YEAR("12415141412-12-21") as year;
    10  SELECT YEAR("0000-00-00") as year;
    11  SELECT YEAR("2049-14-34") as year;
    12  
    13  
    14  
    15  # 多种类型
    16  SELECT YEAR("2015-09") as year;
    17  
    18  
    19  SELECT YEAR(2015) as year;
    20  
    21  SELECT YEAR(1340124) as year;
    22  SELECT YEAR(0.45133) as year;
    23  
    24  SELECT YEAR(1=1) as year;
    25  
    26  
    27  SELECT YEAR('2015-09.30') as year;
    28  
    29  
    30  SELECT YEAR('2015-0') as year;
    31  
    32  
    33  SELECT YEAR('2015-09.30 12') as year;
    34  
    35  
    36  SELECT YEAR('2015-09-26 08:09:22') AS Year ;
    37  
    38  SELECT YEAR('2023-11-08 15:00') AS Year ;
    39  
    40  #算术操作
    41  
    42  SELECT YEAR("2015-09-10")- YEAR("2016-04-24");
    43  
    44  SELECT YEAR("2015-09-10")+ YEAR("2016-04-24");
    45  
    46  SELECT YEAR("2015-09-10")*YEAR("2016-04-24");
    47  
    48  SELECT YEAR("2015-09-10")/YEAR("2016-04-24");
    49  
    50  #比较操作
    51  SELECT YEAR("2015-09-10")>YEAR("2016-04-24");
    52  SELECT YEAR("2015-09-10")<YEAR("2016-04-24");
    53  SELECT YEAR("2015-09-10")=YEAR("2016-04-24");
    54  SELECT YEAR("2015-09-10")<>YEAR("2016-04-24");
    55  SELECT YEAR("2015-09-10")!=YEAR("2016-04-24");
    56  
    57  
    58  #NULL值
    59  SELECT YEAR(NULL) AS Year ;
    60  
    61  #SELECT, ORDER BY, GROUP BY
    62  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));
    63  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' ) ;
    64  SELECT * FROM Product;
    65  SELECT  YEAR (Selling_Date) year,  COUNT(Product_id) Product_Sold FROM Product GROUP BY YEAR (Selling_Date) ORDER BY YEAR (Selling_Date);
    66  DROP TABLE Product;
    67  
    68  #INSERT
    69  drop table if exists t1;
    70  create table t1(a INT,  b date);
    71  insert into t1 select year("2012-10-12"), "2012-10-12";
    72  insert into t1 select year("2004-04-24"), "2004-04-24";
    73  insert into t1 select year("2008-12-04"), "2008-12-04";
    74  select * from t1;
    75  drop table t1;
    76  
    77  #WHERE
    78  
    79  drop table if exists t1;
    80  create table t1(a INT,  b date);
    81  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
    82  select * from t1 where year(b)="2012";
    83  drop table t1;
    84  
    85  
    86  #ON CONDITION
    87  drop table if exists t1;
    88  drop table if exists t2;
    89  create table t1(a INT,  b date);
    90  create table t2(a INT,  b date);
    91  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
    92  insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12");
    93  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (year(t1.b) = year(t2.b));
    94  drop table t1;
    95  drop table t2;
    96  
    97  
    98  #HAVING
    99  
   100  drop table if exists t1;
   101  create table t1(a INT,  b date);
   102  insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
   103  select b from t1 group by b having year(b)="2012";
   104  drop table t1;
   105  
   106  
   107  #distinct
   108  drop table if exists t1;
   109  create table t1(a INT,  b date);
   110  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");
   111  select distinct year(b) from t1;
   112  drop table t1;
   113  
   114  
   115  CREATE TABLE t3(c1 DATE NOT NULL);
   116  INSERT INTO t3 VALUES('2000-01-01');
   117  INSERT INTO t3 VALUES('1999-12-31');
   118  INSERT INTO t3 VALUES('2000-01-01');
   119  INSERT INTO t3 VALUES('2006-12-25');
   120  INSERT INTO t3 VALUES('2008-02-29');
   121  SELECT YEAR(c1) FROM t3;
   122  DROP TABLE t3;
   123  CREATE TABLE t3(c1 DATETIME NOT NULL);
   124  INSERT INTO t3 VALUES('2000-01-01');
   125  INSERT INTO t3 VALUES('1999-12-31');
   126  INSERT INTO t3 VALUES('2000-01-01');
   127  INSERT INTO t3 VALUES('2006-12-25');
   128  INSERT INTO t3 VALUES('2008-02-29');
   129  SELECT YEAR(c1) FROM t3;
   130  DROP TABLE t3;
   131  
   132  
   133  CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
   134  INSERT INTO t3 VALUES('2000-01-01');
   135  INSERT INTO t3 VALUES('1999-12-31');
   136  INSERT INTO t3 VALUES('2000-01-01');
   137  INSERT INTO t3 VALUES('2006-12-25');
   138  INSERT INTO t3 VALUES('2008-02-29');
   139  SELECT YEAR(c1) FROM t3;
   140  DROP TABLE t3;