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