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