github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_extract.result (about) 1 select extract(YEAR FROM "1999-01-02 10:11:12"); 2 extract(YEAR FROM "1999-01-02 10:11:12") 3 1999 4 select extract(YEAR_MONTH FROM "1999-01-02"); 5 extract(YEAR_MONTH FROM "1999-01-02") 6 199901 7 select extract(DAY FROM "1999-01-02"); 8 extract(DAY FROM "1999-01-02") 9 2 10 select extract(DAY_HOUR FROM "1999-01-02 10:11:12"); 11 extract(DAY_HOUR FROM "1999-01-02 10:11:12") 12 02 10 13 select extract(HOUR FROM "1999-01-02 10:11:12"); 14 extract(HOUR FROM "1999-01-02 10:11:12") 15 10 16 select extract(SECOND FROM "1999-01-02 10:11:12"); 17 extract(SECOND FROM "1999-01-02 10:11:12") 18 12 19 select extract(MONTH FROM "2001-02-00"); 20 invalid input: invalid datatime value 2001-02-00 21 SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter; 22 quarter 23 1 24 SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter; 25 quarter 26 1 27 SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter; 28 quarter 29 1 30 SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter; 31 quarter 32 2 33 SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter; 34 quarter 35 2 36 SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter; 37 quarter 38 2 39 SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter; 40 quarter 41 3 42 SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter; 43 quarter 44 3 45 SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter; 46 quarter 47 3 48 SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter; 49 quarter 50 4 51 SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter; 52 quarter 53 4 54 SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter; 55 quarter 56 4 57 select extract(month from NULL); 58 extract(month from NULL) 59 null 60 create table t1(a INT, b date); 61 create table t2(a INT, b date); 62 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 63 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12"); 64 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (extract(year from t1.b) = extract(year from t2.b)); 65 a a 66 4 4 67 1 4 68 drop table t1; 69 drop table t2; 70 create table t1 (ctime varchar(20)); 71 insert into t1 values ('2001-01-12 12:23:40'),('2002-03-12 12:23:40'); 72 select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; 73 ctime 74 2001-01-12 12:23:40 75 select ctime from t1 where extract(MONTH FROM ctime) > 2; 76 ctime 77 2002-03-12 12:23:40 78 drop table t1; 79 CREATE TABLE t1 (i1 INT,d1 DATETIME); 80 INSERT INTO t1 SELECT MAX(1), "2023-04-23 08:23:21" FROM t1; 81 SELECT i1, EXTRACT(YEAR FROM "2023-04-23 08:23:21"), EXTRACT(YEAR FROM d1) FROM t1; 82 i1 EXTRACT(YEAR FROM "2023-04-23 08:23:21") EXTRACT(YEAR FROM d1) 83 null 2023 2023 84 DROP TABLE t1; 85 select extract(DAY_MINUTE FROM "02 10:11:12"); 86 extract(DAY_MINUTE FROM "02 10:11:12") 87 5811 88 select extract(DAY_SECOND FROM "225 10:11:12"); 89 extract(DAY_SECOND FROM "225 10:11:12") 90 8385959 91 select extract(HOUR_MINUTE FROM "10:11:12"); 92 extract(HOUR_MINUTE FROM "10:11:12") 93 1011 94 select extract(HOUR_SECOND FROM "10:11:12"); 95 extract(HOUR_SECOND FROM "10:11:12") 96 101112 97 select extract(MINUTE FROM "10:11:12"); 98 extract(MINUTE FROM "10:11:12") 99 11 100 select extract(MINUTE_SECOND FROM "10:11:12"); 101 extract(MINUTE_SECOND FROM "10:11:12") 102 1112 103 SELECT EXTRACT(HOUR FROM '100000:02:03'); 104 EXTRACT(HOUR FROM '100000:02:03') 105 838 106 select reverse(concat_ws(",",extract(year from 20090702))); 107 invalid argument function extract, bad value [VARCHAR BIGINT] 108 create table t1 (d date, dt datetime, t timestamp, c char(10)); 109 insert ignore into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00"); 110 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 13 near " ignore into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");"; 111 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; 112 extract(MONTH FROM "0000-00-00") extract(MONTH FROM d) extract(MONTH FROM dt) extract(MONTH FROM t) extract(MONTH FROM c) 113 drop table t1; 114 CREATE TABLE t1 (i1 INT,d1 DATETIME); 115 INSERT INTO t1 values(1, "2023-04-23 08:23:21"); 116 SELECT i1, EXTRACT(HOUR FROM d1) FROM t1; 117 i1 EXTRACT(HOUR FROM d1) 118 1 08 119 DROP TABLE t1; 120 CREATE TABLE t1 121 (`date` date, 122 `extracted_week` int, 123 `weekday` int, 124 `week` int, 125 `default_week_format` int); 126 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 1; 127 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 2; 128 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 3; 129 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 4; 130 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 5; 131 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 6; 132 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 7; 133 INSERT INTO t1 SELECT '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), weekday('2000-01-01'), WEEK('2000-01-01'), 0; 134 SELECT * FROM t1; 135 date extracted_week weekday week default_week_format 136 2000-01-01 52 5 52 1 137 2000-01-01 52 5 52 2 138 2000-01-01 52 5 52 3 139 2000-01-01 52 5 52 4 140 2000-01-01 52 5 52 5 141 2000-01-01 52 5 52 6 142 2000-01-01 52 5 52 7 143 2000-01-01 52 5 52 0 144 SELECT distinct extracted_week from t1; 145 extracted_week 146 52 147 select date from t1 having extract(day FROM date)=1; 148 SQL syntax error: column "t1.date" must appear in the GROUP BY clause or be used in an aggregate function 149 drop table t1; 150 CREATE TABLE t1 151 (`date` date, 152 `extracted_week` int); 153 INSERT INTO t1 select '2000-01-01', EXTRACT(WEEK FROM '2000-01-01'); 154 SELECT * FROM t1; 155 date extracted_week 156 2000-01-01 52 157 SELECT distinct extracted_week from t1; 158 extracted_week 159 52 160 select date from t1 having extract(day FROM date)=1; 161 SQL syntax error: column "t1.date" must appear in the GROUP BY clause or be used in an aggregate function 162 drop table t1;