github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/tidb_mysql_test/t/date_formats.test (about) 1 set tidb_cost_model_version=1; 2 # 3 # Test of str_to_date 4 # 5 6 select str_to_date(concat('15-01-2001',' 2:59:58.999'), 7 concat('%d-%m-%Y',' ','%H:%i:%s.%f')); 8 select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T'); 9 10 SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 11 create table t1 (date char(30), format char(30) not null); 12 insert into t1 values 13 ('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'), 14 ('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'), 15 ('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'), 16 ('03-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'), 17 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'), 18 ('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'), 19 ('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'), 20 ('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'), 21 ('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'), 22 ('10:20:10', '%H:%i:%s'), 23 ('10:20:10', '%h:%i:%s.%f'), 24 ('10:20:10', '%T'), 25 ('10:20:10AM', '%h:%i:%s%p'), 26 ('10:20:10AM', '%r'), 27 ('10:20:10.44AM', '%h:%i:%s.%f%p'), 28 ('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'), 29 ('15 September 2001', '%d %M %Y'), 30 ('15 SEPTEMB 2001', '%d %M %Y'), 31 ('15 MAY 2001', '%d %b %Y'), 32 ('15th May 2001', '%D %b %Y'), 33 ('Sunday 15 MAY 2001', '%W %d %b %Y'), 34 ('Sund 15 MAY 2001', '%W %d %b %Y'), 35 ('Tuesday 00 2002', '%W %U %Y'), 36 ('Thursday 53 1998', '%W %u %Y'), 37 ('Sunday 01 2001', '%W %v %x'), 38 ('Tuesday 52 2001', '%W %V %X'), 39 ('060 2004', '%j %Y'), 40 ('4 53 1998', '%w %u %Y'), 41 ('15-01-2001', '%d-%m-%Y %H:%i:%S'), 42 ('15-01-20', '%d-%m-%y'), 43 ('15-2001-1', '%d-%Y-%c'); 44 45 select date,format,str_to_date(date, format) as str_to_date from t1; 46 # Use as a string 47 select date,format,concat('',str_to_date(date, format)) as con from t1; 48 # Use as datetime 49 select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1; 50 select date,format,DATE(str_to_date(date, format)) as date2 from t1; 51 select date,format,TIME(str_to_date(date, format)) as time from t1; 52 select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1; 53 # Test small bug in %f handling 54 select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d')); 55 56 # Test wrong dates or converion specifiers 57 58 truncate table t1; 59 insert into t1 values 60 ('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'), 61 ('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'), 62 ('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'), 63 ('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'), 64 ('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'), 65 ('10:20:10AM', '%H:%i:%s%p'), 66 ('15 Septembei 2001', '%d %M %Y'), 67 ('15 Ju 2001', '%d %M %Y'), 68 ('Sund 15 MA', '%W %d %b %Y'), 69 ('Thursdai 12 1998', '%W %u %Y'), 70 ('Sunday 01 2001', '%W %v %X'), 71 ('Tuesday 52 2001', '%W %V %x'), 72 ('Tuesday 52 2001', '%W %V %Y'), 73 ('Tuesday 52 2001', '%W %u %x'), 74 ('7 53 1998', '%w %u %Y'), 75 (NULL, get_format(DATE,'USA')); 76 select date,format,str_to_date(date, format) as str_to_date from t1; 77 select date,format,concat(str_to_date(date, format),'') as con from t1; 78 79 # Test 'maybe' date formats and 'strange but correct' results 80 81 truncate table t1; 82 insert into t1 values 83 ('10:20:10AM', '%h:%i:%s'), 84 ('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'), 85 ('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'); 86 87 select date,format,str_to_date(date, format) as str_to_date from t1; 88 select date,format,concat(str_to_date(date, format),'') as con from t1; 89 90 #drop table t1; 91 92 # 93 # Test of get_format 94 # 95 SET sql_mode = default; 96 select get_format(DATE, 'USA') as a; 97 select get_format(TIME, 'internal') as a; 98 select get_format(DATETIME, 'eur') as a; 99 select get_format(TIMESTAMP, 'eur') as a; 100 select get_format(DATE, 'TEST') as a; 101 select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA')); 102 103 explain select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001"); 104 105 # 106 # Test of date_format() 107 # 108 109 create table t2 (d date); 110 insert into t2 values ('2004-07-14'),('2005-07-14'); 111 select date_format(d,"%d") from t2 order by 1; 112 #drop table t1; 113 114 select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a; 115 116 SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 117 #create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1, 118 # str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2, 119 # str_to_date("2003-01-02", "%Y-%m-%d") as f3, 120 # str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5; 121 create table t3(f1 datetime ,f2 datetime,f3 datetime,f4 datetime,f5 datetime); 122 insert into t3 values (str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f"),str_to_date("10:11:12.0012", "%H:%i:%S.%f"), 123 str_to_date("2003-01-02", "%Y-%m-%d"), str_to_date("02", "%d") , str_to_date("02 10", "%d %H")); 124 describe t3; 125 --replace_column 2 # 126 select * from t3; 127 #drop table t1; 128 129 #create table t1 select "02 10" as a, "%d %H" as b; 130 create table t4(a text , b text); 131 Insert into t4 values ("02 10", "%d %H"); 132 select str_to_date(a,b) from t4; 133 #create table t2 select str_to_date(a,b) from t1; 134 #describe t2; 135 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1, 136 str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2, 137 str_to_date("2003-01-02", "%Y-%m-%d") as f3, 138 str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4, 139 str_to_date("02 10:11:12", "%d %H:%i:%S") as f5, 140 str_to_date("02 10", "%d %f") as f6; 141 #drop table t1;#, t2; 142 select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1, 143 addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2, 144 microsecond("1997-12-31 23:59:59.01XXXX") as f3; 145 146 select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, 147 str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2; 148 SET sql_mode = default; 149 150 # 151 # Test of locale dependent date format (WL#2928 Date Translation NRE) 152 # 153 set names latin1; 154 select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); 155 #set lc_time_names=ru_RU; 156 #set names koi8r; 157 #select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); 158 #set lc_time_names=de_DE; 159 set names latin1; 160 select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); 161 set names latin1; 162 #set lc_time_names=en_US; 163 164 # 165 # Bug #14016 166 # 167 create table t5 (f1 datetime); 168 insert into t5 (f1) values ("2005-01-01"); 169 insert into t5 (f1) values ("2005-02-01"); 170 select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t5 order by date_format(f1, "%M"); 171 #drop table t1; 172 173 # 174 # Bug #15828 175 # 176 select str_to_date( 1, NULL ); 177 select str_to_date( NULL, 1 ); 178 select str_to_date( 1, IF(1=1,NULL,NULL) ); 179 180 # 181 # Bug#11326 182 # TIME_FORMAT using "%r" returns wrong hour using 24:00:00 in TIME column 183 # 184 # This tests that 24:00:00 does not return PM, when it should be AM. 185 # Some other values are being tested same time. 186 # 187 188 SELECT TIME_FORMAT("24:00:00", '%r'); 189 SELECT TIME_FORMAT("00:00:00", '%r'); 190 SELECT TIME_FORMAT("12:00:00", '%r'); 191 SELECT TIME_FORMAT("15:00:00", '%r'); 192 SELECT TIME_FORMAT("01:00:00", '%r'); 193 SELECT TIME_FORMAT("25:00:00", '%r'); 194 195 # 196 # Bug#11324 197 # TIME_FORMAT using "%l:%i" returns 36:00 with 24:00:00 in TIME column 198 # 199 # This tests that 24:00:00 does not change to "36:00 AM". Testing 200 # some other values same time. 201 # 202 203 SELECT TIME_FORMAT("00:00:00", '%l %p'); 204 SELECT TIME_FORMAT("01:00:00", '%l %p'); 205 SELECT TIME_FORMAT("12:00:00", '%l %p'); 206 SELECT TIME_FORMAT("23:00:00", '%l %p'); 207 SELECT TIME_FORMAT("24:00:00", '%l %p'); 208 SELECT TIME_FORMAT("25:00:00", '%l %p'); 209 210 # 211 # Bug#20729: Bad date_format() call makes mysql server crash 212 # 213 SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896); 214 215 # 216 # Bug #22029: str_to_date returning NULL 217 # 218 219 select str_to_date('04 /30/2004', '%m /%d/%Y'); 220 select str_to_date('04/30 /2004', '%m /%d /%Y'); 221 select str_to_date('04/30/2004 ', '%m/%d/%Y '); 222 223 --echo "End of 4.1 tests" 224 225 # 226 # Bug #41470: DATE_FORMAT() crashes the complete server with a valid date 227 # 228 229 # show that these two do not crash the server: 230 SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as valid_date; 231 SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as valid_date; 232 # show that date within the Gregorian range render correct results: (THU) 233 SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date; 234 235 --echo "End of 5.0 tests" 236 237 238 --echo # 239 --echo # Start of 5.1 tests 240 --echo # 241 242 --echo # 243 --echo # Bug#58005 utf8 + get_format causes failed assertion: !str || str != Ptr' 244 --echo # 245 SET NAMES utf8; 246 SELECT LEAST('%', GET_FORMAT(datetime, 'eur'), CAST(GET_FORMAT(datetime, 'eur') AS CHAR(65535))); 247 SET NAMES latin1; 248 249 --echo # 250 --echo # End of 5.1 tests 251 --echo # 252 253 254 --echo # 255 --echo # Start of 5.6 tests 256 --echo # 257 258 --echo # 259 --echo # WL#946 Fractional seconds precision 260 --echo # Testing Item_func_date_format with NULL argument. 261 --echo # 262 SELECT CAST(TIME_FORMAT(NULL, '%s') AS CHAR); 263 SELECT CAST(TIME_FORMAT(NULL, '%s') AS SIGNED); 264 SELECT CAST(TIME_FORMAT(NULL, '%s') AS DECIMAL(23,6)); 265 SELECT CAST(TIME_FORMAT(NULL, '%s') AS TIME); 266 SELECT CAST(TIME_FORMAT(NULL, '%s') AS DATE); 267 SELECT CAST(TIME_FORMAT(NULL, '%s') AS DATETIME); 268 SELECT TIME_FORMAT(NULL, '%s')+0e0; 269 270 271 --echo # 272 --echo # End of 5.6 tests 273 --echo # 274 275 --echo # 276 --echo # Bug#19047644 EXTRACT_DATE_TIME MISBEHAVES WITH 277 --echo # UNINITIALISED VALUE ON GARBAGE INPUTS 278 --echo # 279 280 do str_to_date(1, "%#"); 281 282 --echo # 283 --echo # Bug#19047488 MAKE_DATE_TIME WITH TOO BIG STRING ARGUMENT, 284 --echo # INVALID MEMORY READS 285 --echo # 286 287 do timestamp(date_format('2011-11-11', right("12345" + 1, 3))); 288 289 --echo # 290 --echo # Bug #25949639: DATE FORMAT 'YYYYMMDD' ISN'T RECOGNIZED IN LEFT JOIN 291 --echo # 292 293 CREATE TABLE t6 (a varchar(10), PRIMARY KEY (a)); 294 CREATE TABLE t7 (a varchar(10), b date, PRIMARY KEY(a,b)); 295 CREATE TABLE t8 (a varchar(10), b TIME, PRIMARY KEY(a,b)); 296 INSERT INTO t6 VALUES ('test1'); 297 INSERT INTO t7 VALUES 298 ('test1','2016-12-13'),('test1','2016-12-14'),('test1','2016-12-15'); 299 INSERT INTO t8 VALUES 300 ('test1','11:13:14'), ('test1','12:13:14'), ('test1','10:13:14'); 301 302 ANALYZE TABLE t6, t7, t8; 303 304 let query1= 305 SELECT * 306 FROM t6 LEFT JOIN t7 307 ON t7.a = 'test1' AND t7.b = '20161213' 308 WHERE t6.a = 'test1'; 309 310 let query2= 311 SELECT * 312 FROM t6 LEFT JOIN t7 IGNORE INDEX(PRIMARY) 313 ON t7.a = 'test1' AND t7.b = '20161213' 314 WHERE t6.a = 'test1'; 315 316 eval EXPLAIN $query1; 317 eval EXPLAIN $query2; 318 eval $query1; 319 eval $query2; 320 321 SELECT b, b = '20161213', 322 CASE b WHEN '20161213' then 'found' ELSE 'not found' END FROM t7; 323 SELECT b, b IN ('20161213'), b in ('20161213', 0) FROM t7; 324 325 #Uses datatype TIME 326 SELECT b, b = '121314', 327 CASE b WHEN '121314' then 'found' ELSE 'not found' END FROM t8; 328 SELECT b, b in ('121314'), b in ('121314', 0) FROM t8; 329 330 #DROP TABLE t1, t2, t3;