github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_extract.test (about)

     1  #SELECT 数据格式
     2  
     3  select extract(YEAR FROM "1999-01-02 10:11:12");
     4  select extract(YEAR_MONTH FROM "1999-01-02");
     5  select extract(DAY FROM "1999-01-02");
     6  select extract(DAY_HOUR FROM "1999-01-02 10:11:12");
     7  select extract(HOUR FROM "1999-01-02 10:11:12");
     8  select extract(SECOND FROM "1999-01-02 10:11:12");
     9  select extract(MONTH FROM "2001-02-00");
    10  
    11  
    12  #SELECT 数据格式
    13  SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;
    14  SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;
    15  SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;
    16  SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;
    17  SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;
    18  SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;
    19  SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;
    20  SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;
    21  SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;
    22  SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;
    23  SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;
    24  SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;
    25  
    26  
    27  #NULL
    28  select extract(month from NULL);
    29  
    30  #ON CONDITION
    31  create table t1(a INT,  b date);
    32  create table t2(a INT,  b date);
    33  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
    34  insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12");
    35  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (extract(year from t1.b) = extract(year from t2.b));
    36  drop table t1;
    37  drop table t2;
    38  
    39  
    40  #WHERE，算术运算, 比较运算
    41  create table t1 (ctime varchar(20));
    42  insert into t1 values ('2001-01-12 12:23:40'),('2002-03-12 12:23:40');
    43  select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;
    44  select ctime from t1 where extract(MONTH FROM ctime) > 2;
    45  drop table t1;
    46  
    47  #嵌套
    48  CREATE TABLE t1 (i1 INT,d1 DATETIME);
    49  #INSERT INTO t1 SELECT MAX(1), NOW() FROM t1;
    50  #SELECT i1, EXTRACT(YEAR FROM NOW()), EXTRACT(YEAR FROM d1) FROM t1;
    51  INSERT INTO t1 SELECT MAX(1), "2023-04-23 08:23:21" FROM t1;
    52  SELECT i1, EXTRACT(YEAR FROM "2023-04-23 08:23:21"), EXTRACT(YEAR FROM d1) FROM t1;
    53  DROP TABLE t1;
    54  
    55  
    56  #EXTREME VALUE
    57  -- @bvt:issue#4689
    58  select extract(DAY_MINUTE FROM "02 10:11:12");
    59  select extract(DAY_SECOND FROM "225 10:11:12");
    60  select extract(HOUR_MINUTE FROM "10:11:12");
    61  select extract(HOUR_SECOND FROM "10:11:12");
    62  select extract(MINUTE FROM "10:11:12");
    63  select extract(MINUTE_SECOND FROM "10:11:12");
    64  
    65  #EXTREME VALUE
    66  SELECT EXTRACT(HOUR FROM '100000:02:03');
    67  -- @bvt:issue
    68  
    69  select reverse(concat_ws(",",extract(year from 20090702)));
    70  
    71  create table t1 (d date, dt datetime, t timestamp, c char(10));
    72  insert ignore into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
    73  select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;
    74  drop table t1;
    75  
    76  CREATE TABLE t1 (i1 INT,d1 DATETIME);
    77  INSERT INTO t1 values(1, "2023-04-23 08:23:21");
    78  SELECT i1, EXTRACT(HOUR FROM d1) FROM t1;
    79  DROP TABLE t1;
    80  
    81  
    82  #INSERT INTO, DISTINCT,HAVING
    83  CREATE TABLE t1
    84  (`date` date,
    85  `extracted_week` int,
    86  `weekday` int,
    87  `week` int,
    88  `default_week_format` int);
    89  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 1;
    90  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 2;
    91  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 3;
    92  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 4;
    93  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 5;
    94  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 6;
    95  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 7;
    96  INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 0;
    97  SELECT * FROM t1;
    98  SELECT distinct extracted_week from t1;
    99  select date from t1 having extract(day FROM date)=1;
   100  drop table t1;
   101  
   102  
   103  CREATE TABLE t1
   104  (`date` date,
   105  `extracted_week` int);
   106  INSERT INTO t1 select '2000-01-01', EXTRACT(WEEK FROM '2000-01-01');
   107  SELECT * FROM t1;
   108  SELECT distinct extracted_week from t1;
   109  select date from t1 having extract(day FROM date)=1;
   110  drop table t1;