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

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