1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
|
#
# MDEV-17385 MICROSECOND() returns confusing results with an out-of-range TIME-alike argument
#
CREATE TABLE t1 (v VARCHAR(64), ll BIGINT, t TIME, dt DATETIME, d DATE);
CREATE TABLE t2 AS SELECT
EXTRACT(DAY FROM t),
EXTRACT(DAY_HOUR FROM t),
EXTRACT(DAY_MINUTE FROM t),
EXTRACT(DAY_SECOND FROM t),
EXTRACT(DAY_MICROSECOND FROM t),
EXTRACT(DAY FROM d),
EXTRACT(DAY_HOUR FROM d),
EXTRACT(DAY_MINUTE FROM d),
EXTRACT(DAY_SECOND FROM d),
EXTRACT(DAY_MICROSECOND FROM d),
EXTRACT(DAY FROM v),
EXTRACT(DAY_HOUR FROM v),
EXTRACT(DAY_MINUTE FROM v),
EXTRACT(DAY_SECOND FROM v),
EXTRACT(DAY_MICROSECOND FROM v),
EXTRACT(DAY FROM ll),
EXTRACT(DAY_HOUR FROM ll),
EXTRACT(DAY_MINUTE FROM ll),
EXTRACT(DAY_SECOND FROM ll),
EXTRACT(DAY_MICROSECOND FROM ll)
FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`EXTRACT(DAY FROM t)` int(3) DEFAULT NULL,
`EXTRACT(DAY_HOUR FROM t)` int(5) DEFAULT NULL,
`EXTRACT(DAY_MINUTE FROM t)` int(7) DEFAULT NULL,
`EXTRACT(DAY_SECOND FROM t)` int(9) DEFAULT NULL,
`EXTRACT(DAY_MICROSECOND FROM t)` bigint(15) DEFAULT NULL,
`EXTRACT(DAY FROM d)` int(3) DEFAULT NULL,
`EXTRACT(DAY_HOUR FROM d)` int(5) DEFAULT NULL,
`EXTRACT(DAY_MINUTE FROM d)` int(7) DEFAULT NULL,
`EXTRACT(DAY_SECOND FROM d)` int(9) DEFAULT NULL,
`EXTRACT(DAY_MICROSECOND FROM d)` bigint(15) DEFAULT NULL,
`EXTRACT(DAY FROM v)` int(8) DEFAULT NULL,
`EXTRACT(DAY_HOUR FROM v)` int(10) DEFAULT NULL,
`EXTRACT(DAY_MINUTE FROM v)` bigint(12) DEFAULT NULL,
`EXTRACT(DAY_SECOND FROM v)` bigint(14) DEFAULT NULL,
`EXTRACT(DAY_MICROSECOND FROM v)` bigint(20) DEFAULT NULL,
`EXTRACT(DAY FROM ll)` int(8) DEFAULT NULL,
`EXTRACT(DAY_HOUR FROM ll)` int(10) DEFAULT NULL,
`EXTRACT(DAY_MINUTE FROM ll)` bigint(12) DEFAULT NULL,
`EXTRACT(DAY_SECOND FROM ll)` bigint(14) DEFAULT NULL,
`EXTRACT(DAY_MICROSECOND FROM ll)` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64), b DECIMAL(32,9));
INSERT INTO t1 VALUES
('9999-12-31 23:59:59.123456', 99991231235959.123456),
('2001-01-01 10:20:30.123456', 20010101102030.123456),
('4294967296:59:59.123456', 42949672965959.123456),
('4294967295:59:59.123456', 42949672955959.123456),
('87649416:59:59.123456', 876494165959.123456),
('87649415:59:59.123456', 876494155959.123456),
('87649414:59:59.123456', 876494145959.123456),
('9999:59:59.123456', 99995959.123456),
('9999:01:01.123456', 99990101.123456),
('9999:01:01', 99990101),
('0.999999', 0.999999),
('0.99999', 0.99999),
('0.9999', 0.9999),
('0.999', 0.999),
('0.99', 0.99),
('0.9', 0.9),
('000000',0);
# Summary:
# Check that FUNC(varchar) and FUNC(decimal) give equal results
# Expect empty sets
SELECT a, b, EXTRACT(DAY_HOUR FROM a), EXTRACT(DAY_HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(DAY_HOUR FROM a)<=>EXTRACT(DAY_HOUR FROM b));
a b EXTRACT(DAY_HOUR FROM a) EXTRACT(DAY_HOUR FROM b)
SELECT a, b, EXTRACT(DAY FROM a), EXTRACT(DAY FROM b) FROM t1 WHERE NOT (EXTRACT(DAY FROM a)<=>EXTRACT(DAY FROM b));
a b EXTRACT(DAY FROM a) EXTRACT(DAY FROM b)
SELECT a, b, EXTRACT(HOUR FROM a), EXTRACT(HOUR FROM b) FROM t1 WHERE NOT (EXTRACT(HOUR FROM a)<=>EXTRACT(HOUR FROM b));
a b EXTRACT(HOUR FROM a) EXTRACT(HOUR FROM b)
SELECT a, b, EXTRACT(MINUTE FROM a), EXTRACT(MINUTE FROM b) FROM t1 WHERE NOT (EXTRACT(MINUTE FROM a)<=>EXTRACT(MINUTE FROM b));
a b EXTRACT(MINUTE FROM a) EXTRACT(MINUTE FROM b)
SELECT a, b, EXTRACT(SECOND FROM a), EXTRACT(SECOND FROM b) FROM t1 WHERE NOT (EXTRACT(SECOND FROM a)<=>EXTRACT(SECOND FROM b));
a b EXTRACT(SECOND FROM a) EXTRACT(SECOND FROM b)
SELECT a, b, EXTRACT(MICROSECOND FROM a), EXTRACT(MICROSECOND FROM b) FROM t1 WHERE NOT (EXTRACT(MICROSECOND FROM a)<=>EXTRACT(MICROSECOND FROM b));
a b EXTRACT(MICROSECOND FROM a) EXTRACT(MICROSECOND FROM b)
# Detailed results
SELECT
a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY FROM a) * 24 + EXTRACT(HOUR FROM a) AS dh,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
a cidm dh EXTRACT(DAY_HOUR FROM a) EXTRACT(DAY FROM a) EXTRACT(HOUR FROM a) EXTRACT(MINUTE FROM a) EXTRACT(SECOND FROM a) EXTRACT(MICROSECOND FROM a)
9999-12-31 23:59:59.123456 NULL 767 3123 31 23 59 59 123456
2001-01-01 10:20:30.123456 NULL 34 110 1 10 20 30 123456
4294967296:59:59.123456 NULL NULL NULL NULL NULL NULL NULL NULL
4294967295:59:59.123456 NULL NULL NULL NULL NULL NULL NULL NULL
87649416:59:59.123456 NULL NULL NULL NULL NULL NULL NULL NULL
87649415:59:59.123456 3652058 23:59:59.123456 87649415 365205823 3652058 23 59 59 123456
87649414:59:59.123456 3652058 22:59:59.123456 87649414 365205822 3652058 22 59 59 123456
9999:59:59.123456 416 15:59:59.123456 9999 41615 416 15 59 59 123456
9999:01:01.123456 416 15:01:01.123456 9999 41615 416 15 1 1 123456
9999:01:01 416 15:01:01.000000 9999 41615 416 15 1 1 0
0.999999 00:00:00.999999 0 0 0 0 0 0 999999
0.99999 00:00:00.999990 0 0 0 0 0 0 999990
0.9999 00:00:00.999900 0 0 0 0 0 0 999900
0.999 00:00:00.999000 0 0 0 0 0 0 999000
0.99 00:00:00.990000 0 0 0 0 0 0 990000
0.9 00:00:00.900000 0 0 0 0 0 0 900000
000000 00:00:00.000000 0 0 0 0 0 0 0
Warnings:
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '9999-12-31 23:59:59.123456'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2001-01-01 10:20:30.123456'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect interval value: '4294967296:59:59.123456'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect interval value: '4294967295:59:59.123456'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
Warning 1292 Incorrect interval value: '87649416:59:59.123456'
SELECT
b,
CAST(b AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY FROM b) * 24 + EXTRACT(HOUR FROM b) AS dh,
EXTRACT(DAY_HOUR FROM b),
EXTRACT(DAY FROM b),
EXTRACT(HOUR FROM b),
EXTRACT(MINUTE FROM b),
EXTRACT(SECOND FROM b),
EXTRACT(MICROSECOND FROM b)
FROM t1;
b cidm dh EXTRACT(DAY_HOUR FROM b) EXTRACT(DAY FROM b) EXTRACT(HOUR FROM b) EXTRACT(MINUTE FROM b) EXTRACT(SECOND FROM b) EXTRACT(MICROSECOND FROM b)
99991231235959.123456000 NULL 767 3123 31 23 59 59 123456
20010101102030.123456000 NULL 34 110 1 10 20 30 123456
42949672965959.123456000 NULL NULL NULL NULL NULL NULL NULL NULL
42949672955959.123456000 NULL NULL NULL NULL NULL NULL NULL NULL
876494165959.123456000 NULL NULL NULL NULL NULL NULL NULL NULL
876494155959.123456000 3652058 23:59:59.123456 87649415 365205823 3652058 23 59 59 123456
876494145959.123456000 3652058 22:59:59.123456 87649414 365205822 3652058 22 59 59 123456
99995959.123456000 416 15:59:59.123456 9999 41615 416 15 59 59 123456
99990101.123456000 416 15:01:01.123456 9999 41615 416 15 1 1 123456
99990101.000000000 416 15:01:01.000000 9999 41615 416 15 1 1 0
0.999999000 00:00:00.999999 0 0 0 0 0 0 999999
0.999990000 00:00:00.999990 0 0 0 0 0 0 999990
0.999900000 00:00:00.999900 0 0 0 0 0 0 999900
0.999000000 00:00:00.999000 0 0 0 0 0 0 999000
0.990000000 00:00:00.990000 0 0 0 0 0 0 990000
0.900000000 00:00:00.900000 0 0 0 0 0 0 900000
0.000000000 00:00:00.000000 0 0 0 0 0 0 0
Warnings:
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '99991231235959.123456000'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '20010101102030.123456000'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '42949672965959.123456000'
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect interval value: '42949672965959.123456000' for column `test`.`t1`.`b` at row 3
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '42949672955959.123456000'
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect interval value: '42949672955959.123456000' for column `test`.`t1`.`b` at row 4
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '876494165959.123456000'
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Warning 1292 Incorrect interval value: '876494165959.123456000' for column `test`.`t1`.`b` at row 5
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494155959.123456000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '876494145959.123456000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '99995959.123456000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.123456000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '99990101.000000000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999999000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999990000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999900000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.999000000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.990000000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.900000000'
Note 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '0.000000000'
DROP TABLE t1;
# Special case: DAY + TIME
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES ('9999-01-01');
SELECT a,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY_MINUTE FROM a),
EXTRACT(DAY_SECOND FROM a),
EXTRACT(DAY_MICROSECOND FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
a EXTRACT(DAY_HOUR FROM a) EXTRACT(DAY_MINUTE FROM a) EXTRACT(DAY_SECOND FROM a) EXTRACT(DAY_MICROSECOND FROM a) EXTRACT(DAY FROM a) EXTRACT(HOUR FROM a) EXTRACT(MINUTE FROM a) EXTRACT(SECOND FROM a) EXTRACT(MICROSECOND FROM a)
9999-01-01 100 10000 1000000 1000000000000 1 0 0 0 0
DROP TABLE t1;
# Bad values
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES ('');
SELECT a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm,
EXTRACT(DAY_HOUR FROM a),
EXTRACT(DAY_MINUTE FROM a),
EXTRACT(DAY_SECOND FROM a),
EXTRACT(DAY_MICROSECOND FROM a),
EXTRACT(DAY FROM a),
EXTRACT(HOUR FROM a),
EXTRACT(MINUTE FROM a),
EXTRACT(SECOND FROM a),
EXTRACT(MICROSECOND FROM a)
FROM t1;
a cidm EXTRACT(DAY_HOUR FROM a) EXTRACT(DAY_MINUTE FROM a) EXTRACT(DAY_SECOND FROM a) EXTRACT(DAY_MICROSECOND FROM a) EXTRACT(DAY FROM a) EXTRACT(HOUR FROM a) EXTRACT(MINUTE FROM a) EXTRACT(SECOND FROM a) EXTRACT(MICROSECOND FROM a)
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
Warnings:
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
Warning 1292 Incorrect interval value: ''
DROP TABLE t1;
# Backward compatibility
# This still parses as DATETIME
SELECT EXTRACT(YEAR FROM '2001/02/03 10:20:30');
EXTRACT(YEAR FROM '2001/02/03 10:20:30')
2001
SELECT EXTRACT(MONTH FROM '2001/02/03 10:20:30');
EXTRACT(MONTH FROM '2001/02/03 10:20:30')
2
SELECT EXTRACT(DAY FROM '2001/02/03 10:20:30');
EXTRACT(DAY FROM '2001/02/03 10:20:30')
3
SELECT EXTRACT(YEAR FROM '01/02/03 10:20:30');
EXTRACT(YEAR FROM '01/02/03 10:20:30')
2001
SELECT EXTRACT(MONTH FROM '01/02/03 10:20:30');
EXTRACT(MONTH FROM '01/02/03 10:20:30')
2
SELECT EXTRACT(DAY FROM '01/02/03 10:20:30');
EXTRACT(DAY FROM '01/02/03 10:20:30')
3
SELECT EXTRACT(YEAR FROM '01:02:03 10:20:30');
EXTRACT(YEAR FROM '01:02:03 10:20:30')
2001
SELECT EXTRACT(MONTH FROM '01:02:03 10:20:30');
EXTRACT(MONTH FROM '01:02:03 10:20:30')
2
SELECT EXTRACT(DAY FROM '01:02:03 10:20:30');
EXTRACT(DAY FROM '01:02:03 10:20:30')
3
# This still parses as DATETIME and returns NULL
SELECT EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434");
EXTRACT(YEAR FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning 1292 Incorrect datetime value: '2011-02-32 8:46:06.23434'
SELECT EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434");
EXTRACT(MONTH FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning 1292 Incorrect datetime value: '2011-02-32 8:46:06.23434'
SELECT EXTRACT(DAY FROM "2011-02-32 8:46:06.23434");
EXTRACT(DAY FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning 1292 Incorrect interval value: '2011-02-32 8:46:06.23434'
SELECT EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434");
EXTRACT(HOUR FROM "2011-02-32 8:46:06.23434")
NULL
Warnings:
Warning 1292 Incorrect interval value: '2011-02-32 8:46:06.23434'
# This still parses as DATE
SELECT EXTRACT(YEAR FROM '2001/02/03');
EXTRACT(YEAR FROM '2001/02/03')
2001
SELECT EXTRACT(MONTH FROM '2001/02/03');
EXTRACT(MONTH FROM '2001/02/03')
2
SELECT EXTRACT(DAY FROM '2001/02/03');
EXTRACT(DAY FROM '2001/02/03')
3
SELECT EXTRACT(YEAR FROM '01/02/03');
EXTRACT(YEAR FROM '01/02/03')
2001
SELECT EXTRACT(MONTH FROM '01/02/03');
EXTRACT(MONTH FROM '01/02/03')
2
SELECT EXTRACT(DAY FROM '01/02/03');
EXTRACT(DAY FROM '01/02/03')
3
SELECT EXTRACT(YEAR FROM '01-02-03');
EXTRACT(YEAR FROM '01-02-03')
2001
SELECT EXTRACT(MONTH FROM '01-02-03');
EXTRACT(MONTH FROM '01-02-03')
2
SELECT EXTRACT(DAY FROM '01-02-03');
EXTRACT(DAY FROM '01-02-03')
3
SELECT EXTRACT(YEAR FROM '1-2-3');
EXTRACT(YEAR FROM '1-2-3')
1
SELECT EXTRACT(MONTH FROM '1-2-3');
EXTRACT(MONTH FROM '1-2-3')
2
SELECT EXTRACT(DAY FROM '1-2-3');
EXTRACT(DAY FROM '1-2-3')
3
SELECT EXTRACT(HOUR FROM '1-2-3');
EXTRACT(HOUR FROM '1-2-3')
0
SELECT EXTRACT(DAY FROM '2024-01-03 garbage /////');
EXTRACT(DAY FROM '2024-01-03 garbage /////')
3
Warnings:
Warning 1292 Truncated incorrect date value: '2024-01-03 garbage /////'
SELECT EXTRACT(DAY FROM '24-01-03 garbage /////');
EXTRACT(DAY FROM '24-01-03 garbage /////')
3
Warnings:
Warning 1292 Truncated incorrect date value: '24-01-03 garbage /////'
SELECT EXTRACT(DAY FROM '01-02-03');
EXTRACT(DAY FROM '01-02-03')
3
SELECT EXTRACT(DAY FROM '24:02:03T');
EXTRACT(DAY FROM '24:02:03T')
3
SELECT EXTRACT(DAY FROM '24-02-03');
EXTRACT(DAY FROM '24-02-03')
3
SELECT EXTRACT(DAY FROM '24/02/03');
EXTRACT(DAY FROM '24/02/03')
3
SELECT EXTRACT(DAY FROM '11111');
EXTRACT(DAY FROM '11111')
1
SELECT TIME('2001-01-01T'), TIME('2001-01-01T ');
TIME('2001-01-01T') TIME('2001-01-01T ')
00:00:00 00:00:00
SELECT TIME('2001/01/01T'), TIME('2001/01/01T ');
TIME('2001/01/01T') TIME('2001/01/01T ')
00:00:00 00:00:00
SELECT TIME('2001:01:01T'), TIME('2001:01:01T ');
TIME('2001:01:01T') TIME('2001:01:01T ')
00:00:00 00:00:00
SELECT EXTRACT(DAY FROM '2001-01-01T'), EXTRACT(DAY FROM '2001-01-01T ');
EXTRACT(DAY FROM '2001-01-01T') EXTRACT(DAY FROM '2001-01-01T ')
1 1
SELECT EXTRACT(DAY FROM '2001/01/01T'), EXTRACT(DAY FROM '2001/01/01T ');
EXTRACT(DAY FROM '2001/01/01T') EXTRACT(DAY FROM '2001/01/01T ')
1 1
SELECT EXTRACT(DAY FROM '2001:01:01T'), EXTRACT(DAY FROM '2001:01:01T ');
EXTRACT(DAY FROM '2001:01:01T') EXTRACT(DAY FROM '2001:01:01T ')
1 1
SELECT TIME('2001:01:01T'), TIME('2001:01:01T ');
TIME('2001:01:01T') TIME('2001:01:01T ')
00:00:00 00:00:00
SELECT EXTRACT(HOUR FROM '2001-01-01T'), EXTRACT(HOUR FROM '2001-01-01T ');
EXTRACT(HOUR FROM '2001-01-01T') EXTRACT(HOUR FROM '2001-01-01T ')
0 0
SELECT EXTRACT(HOUR FROM '2001/01/01T'), EXTRACT(HOUR FROM '2001/01/01T ');
EXTRACT(HOUR FROM '2001/01/01T') EXTRACT(HOUR FROM '2001/01/01T ')
0 0
SELECT EXTRACT(HOUR FROM '2001:01:01T'), EXTRACT(HOUR FROM '2001:01:01T ');
EXTRACT(HOUR FROM '2001:01:01T') EXTRACT(HOUR FROM '2001:01:01T ')
0 0
# This still parses as DATE and returns NULL (without trying TIME)
SELECT EXTRACT(DAY FROM '100000:02:03T');
EXTRACT(DAY FROM '100000:02:03T')
NULL
Warnings:
Warning 1292 Incorrect interval value: '100000:02:03T'
SELECT EXTRACT(DAY FROM '100000/02/03');
EXTRACT(DAY FROM '100000/02/03')
NULL
Warnings:
Warning 1292 Incorrect interval value: '100000/02/03'
SELECT EXTRACT(DAY FROM '100000-02-03');
EXTRACT(DAY FROM '100000-02-03')
NULL
Warnings:
Warning 1292 Incorrect interval value: '100000-02-03'
SELECT EXTRACT(DAY FROM '1111');
EXTRACT(DAY FROM '1111')
NULL
Warnings:
Warning 1292 Incorrect interval value: '1111'
SELECT EXTRACT(DAY FROM '111');
EXTRACT(DAY FROM '111')
NULL
Warnings:
Warning 1292 Incorrect interval value: '111'
SELECT EXTRACT(DAY FROM '11');
EXTRACT(DAY FROM '11')
NULL
Warnings:
Warning 1292 Incorrect interval value: '11'
SELECT EXTRACT(DAY FROM '1');
EXTRACT(DAY FROM '1')
NULL
Warnings:
Warning 1292 Incorrect interval value: '1'
# This still parses as TIME
SELECT EXTRACT(HOUR FROM '11111');
EXTRACT(HOUR FROM '11111')
1
SELECT EXTRACT(HOUR FROM '1111');
EXTRACT(HOUR FROM '1111')
0
SELECT EXTRACT(HOUR FROM '111');
EXTRACT(HOUR FROM '111')
0
SELECT EXTRACT(HOUR FROM '11');
EXTRACT(HOUR FROM '11')
0
SELECT EXTRACT(HOUR FROM '1');
EXTRACT(HOUR FROM '1')
0
SELECT TIME('01:02:03:');
TIME('01:02:03:')
01:02:03
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03:'
SELECT TIME('01:02:03-');
TIME('01:02:03-')
01:02:03
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03-'
SELECT TIME('01:02:03;');
TIME('01:02:03;')
01:02:03
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03;'
SELECT TIME('01:02:03/');
TIME('01:02:03/')
01:02:03
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03/'
SELECT EXTRACT(HOUR FROM '01:02:03:');
EXTRACT(HOUR FROM '01:02:03:')
1
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03:'
SELECT EXTRACT(HOUR FROM '01:02:03-');
EXTRACT(HOUR FROM '01:02:03-')
1
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03-'
SELECT EXTRACT(HOUR FROM '01:02:03;');
EXTRACT(HOUR FROM '01:02:03;')
1
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03;'
SELECT EXTRACT(HOUR FROM '01:02:03/');
EXTRACT(HOUR FROM '01:02:03/')
1
Warnings:
Warning 1292 Truncated incorrect time value: '01:02:03/'
# Backward compatibility preserved for YEAR and MONTH only
# (behavior has changed for DAY, see below)
SELECT EXTRACT(YEAR FROM '01:02:03');
EXTRACT(YEAR FROM '01:02:03')
2001
SELECT EXTRACT(MONTH FROM '01:02:03');
EXTRACT(MONTH FROM '01:02:03')
2
SELECT EXTRACT(YEAR FROM '24:01:03 garbage /////');
EXTRACT(YEAR FROM '24:01:03 garbage /////')
2024
Warnings:
Warning 1292 Truncated incorrect date value: '24:01:03 garbage /////'
SELECT EXTRACT(MONTH FROM '24:01:03 garbage /////');
EXTRACT(MONTH FROM '24:01:03 garbage /////')
1
Warnings:
Warning 1292 Truncated incorrect date value: '24:01:03 garbage /////'
# This still parses as TIME 00:20:01
SELECT TIME('2001/01/01');
TIME('2001/01/01')
00:20:01
Warnings:
Warning 1292 Truncated incorrect time value: '2001/01/01'
SELECT TIME('2001-01-01');
TIME('2001-01-01')
00:20:01
Warnings:
Warning 1292 Truncated incorrect time value: '2001-01-01'
# This still parses as TIME and overflows to '838:59:59'
SELECT TIME('2001:01:01');
TIME('2001:01:01')
838:59:59
Warnings:
Warning 1292 Truncated incorrect time value: '2001:01:01'
# This used to parse as DATE, now parses as TIME interval
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES
('2024:01:03 garbage /////'),
('24:01:03 garbage /////'),
('01:01:03 garbage /////'),
('2024:02:03'),
('100000:02:03'),
('24:02:03'),
('01:02:03'),
('01:02:03:'),
('01:02:03-'),
('01:02:03;'),
('01:02:03/'),
('20 10:20:30');
SELECT
EXTRACT(DAY FROM a),
EXTRACT(DAY_SECOND FROM a), a,
CAST(a AS INTERVAL DAY_SECOND(6)) AS cidm
FROM t1;
EXTRACT(DAY FROM a) EXTRACT(DAY_SECOND FROM a) a cidm
84 84080103 2024:01:03 garbage ///// NULL
1 1000103 24:01:03 garbage ///// NULL
0 10103 01:01:03 garbage ///// NULL
84 84080203 2024:02:03 84 08:02:03.000000
4166 4166160203 100000:02:03 4166 16:02:03.000000
1 1000203 24:02:03 1 00:02:03.000000
0 10203 01:02:03 01:02:03.000000
0 10203 01:02:03: 01:02:03.000000
0 10203 01:02:03- NULL
0 10203 01:02:03; 01:02:03.000000
0 10203 01:02:03/ 01:02:03.000000
20 20102030 20 10:20:30 20 10:20:30.000000
Warnings:
Warning 1292 Truncated incorrect time value: '2024:01:03 garbage /////'
Warning 1292 Truncated incorrect time value: '2024:01:03 garbage /////'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '2024:01:03 garbage /////'
Warning 1292 Truncated incorrect time value: '24:01:03 garbage /////'
Warning 1292 Truncated incorrect time value: '24:01:03 garbage /////'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '24:01:03 garbage /////'
Warning 1292 Truncated incorrect time value: '01:01:03 garbage /////'
Warning 1292 Truncated incorrect time value: '01:01:03 garbage /////'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '01:01:03 garbage /////'
Warning 1292 Truncated incorrect time value: '01:02:03:'
Warning 1292 Truncated incorrect time value: '01:02:03:'
Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03:'
Warning 1292 Truncated incorrect time value: '01:02:03-'
Warning 1292 Truncated incorrect time value: '01:02:03-'
Warning 1292 Incorrect INTERVAL DAY TO SECOND value: '01:02:03-'
Warning 1292 Truncated incorrect time value: '01:02:03;'
Warning 1292 Truncated incorrect time value: '01:02:03;'
Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03;'
Warning 1292 Truncated incorrect time value: '01:02:03/'
Warning 1292 Truncated incorrect time value: '01:02:03/'
Warning 1292 Truncated incorrect INTERVAL DAY TO SECOND value: '01:02:03/'
DROP TABLE t1;
|
