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
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
|
SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
drop table if exists t1, t2, t3;
create table t1 (a timestamp(7));
ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6
create table t1 (a timestamp(3), key(a));
insert t1 values ('2010-12-11 00:20:03.1234');
insert t1 values ('2010-12-11 15:47:11.1234');
insert t1 values (20101211010203.45678);
insert t1 values (20101211030405.789e0);
insert ignore t1 values (99991231235959e1);
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
select * from t1;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select cast(a AS double(30,6)) from t1;
cast(a AS double(30,6))
0.000000
20101211002003.120000
20101211010203.457031
20101211030405.790000
20101211154711.120000
select a DIV 1 from t1;
a DIV 1
0
20101211002003
20101211010203
20101211030405
20101211154711
select group_concat(distinct a) from t1;
group_concat(distinct a)
0000-00-00 00:00:00.000,2010-12-11 00:20:03.123,2010-12-11 01:02:03.456,2010-12-11 03:04:05.789,2010-12-11 15:47:11.123
alter table t1 engine=innodb;
select * from t1 order by a;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
select * from t1 order by a+0;
a
0000-00-00 00:00:00.000
2010-12-11 00:20:03.123
2010-12-11 01:02:03.456
2010-12-11 03:04:05.789
2010-12-11 15:47:11.123
drop table t1;
create table t1 (a timestamp(4)NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)) engine=innodb;
insert t1 values ('2010-12-11 01:02:03.456789');
select * from t1;
a
2010-12-11 01:02:03.4567
select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456';
extract(microsecond from a + interval 100 microsecond)
456800
select a from t1 where a>'2010-11-12 01:02:03.456' group by a;
a
2010-12-11 01:02:03.4567
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(4) NOT NULL DEFAULT current_timestamp(4) ON UPDATE current_timestamp(4)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
show columns from t1;
Field Type Null Key Default Extra
a timestamp(4) NO current_timestamp(4) on update current_timestamp(4)
select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1';
table_name t1
column_name a
column_default current_timestamp(4)
is_nullable NO
data_type timestamp
character_maximum_length NULL
character_octet_length NULL
numeric_precision NULL
numeric_scale NULL
datetime_precision 4
character_set_name NULL
collation_name NULL
column_type timestamp(4)
column_key
extra on update current_timestamp(4)
select a, a+interval 9876543 microsecond from t1;
a a+interval 9876543 microsecond
2010-12-11 01:02:03.4567 2010-12-11 01:02:13.333243
update t1 set a=a+interval 9876543 microsecond;
select * from t1;
a
2010-12-11 01:02:13.3332
select a, a + interval 2 year from t1;
a a + interval 2 year
2010-12-11 01:02:13.3332 2012-12-11 01:02:13.3332
insert ignore t1 select a + interval 2 year from t1;
select * from t1;
a
2010-12-11 01:02:13.3332
2012-12-11 01:02:13.3332
delete from t1 where a < 20110101;
select * from t1;
a
2012-12-11 01:02:13.3332
create table t2 select * from t1;
create table t3 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` timestamp(4) NOT NULL DEFAULT current_timestamp(4) ON UPDATE current_timestamp(4)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`a` timestamp(4) NOT NULL DEFAULT current_timestamp(4) ON UPDATE current_timestamp(4)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t2, t3;
insert t1 values ('2010-12-13 14:15:16.222222');
select a, a+0, a-1, a*1, a/2 from t1;
a a+0 a-1 a*1 a/2
2012-12-11 01:02:13.3332 20121211010213.3332 20121211010212.3332 20121211010213.3332 10060605505106.66660000
2010-12-13 14:15:16.2222 20101213141516.2222 20101213141515.2222 20101213141516.2222 10050606570758.11110000
select max(a), min(a), sum(a), avg(a) from t1;
max(a) min(a) sum(a) avg(a)
2012-12-11 01:02:13.3332 2010-12-13 14:15:16.2222 40222424151729.5554 20111212075864.77770000
create table t2 select a, a+0, a-1, a*1, a/2 from t1;
create table t3 select max(a), min(a), sum(a), avg(a) from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` timestamp(4) NOT NULL DEFAULT current_timestamp(4) ON UPDATE current_timestamp(4),
`a+0` decimal(19,4) NOT NULL,
`a-1` decimal(19,4) NOT NULL,
`a*1` decimal(19,4) NOT NULL,
`a/2` decimal(22,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table t3;
Table Create Table
t3 CREATE TABLE `t3` (
`max(a)` timestamp(4) NULL DEFAULT NULL,
`min(a)` timestamp(4) NULL DEFAULT NULL,
`sum(a)` decimal(40,4) DEFAULT NULL,
`avg(a)` decimal(22,8) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1, t2, t3;
create table t1 (f0_timestamp timestamp(0), f1_timestamp timestamp(1), f2_timestamp timestamp(2), f3_timestamp timestamp(3), f4_timestamp timestamp(4), f5_timestamp timestamp(5), f6_timestamp timestamp(6));
insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432');
select * from t1;
f0_timestamp 2010-11-12 11:14:17
f1_timestamp 2010-11-12 11:14:17.7
f2_timestamp 2010-11-12 11:14:17.76
f3_timestamp 2010-11-12 11:14:17.765
f4_timestamp 2010-11-12 11:14:17.7654
f5_timestamp 2010-11-12 11:14:17.76543
f6_timestamp 2010-11-12 11:14:17.765432
select cast(f0_timestamp as time(4)) time4_f0_timestamp, cast(f1_timestamp as datetime(3)) datetime3_f1_timestamp, cast(f2_timestamp as date) date_f2_timestamp, cast(f4_timestamp as double) double_f3_timestamp, cast(f4_timestamp as decimal(40,5)) decimal5_f4_timestamp, cast(f5_timestamp as signed) bigint_f5_timestamp, cast(f6_timestamp as char(255)) varchar_f6_timestamp from t1;
time4_f0_timestamp 11:14:17.0000
datetime3_f1_timestamp 2010-11-12 11:14:17.700
date_f2_timestamp 2010-11-12
double_f3_timestamp 20101112111417.766
decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
create table t2 (time4_f0_timestamp time(4), datetime3_f1_timestamp datetime(3), date_f2_timestamp date, double_f3_timestamp double, decimal5_f4_timestamp decimal(40,5), bigint_f5_timestamp bigint, varchar_f6_timestamp varchar(255));
insert t2 select * from t1;
Warnings:
Level Note
Code 1265
Message Data truncated for column 'time4_f0_timestamp' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_timestamp' at row 1
select * from t2;
time4_f0_timestamp 11:14:17.0000
datetime3_f1_timestamp 2010-11-12 11:14:17.700
date_f2_timestamp 2010-11-12
double_f3_timestamp 20101112111417.766
decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
alter table t1 change f0_timestamp time4_f0_timestamp time(4), change f1_timestamp datetime3_f1_timestamp datetime(3), change f2_timestamp date_f2_timestamp date, change f3_timestamp double_f3_timestamp double, change f4_timestamp decimal5_f4_timestamp decimal(40,5), change f5_timestamp bigint_f5_timestamp bigint, change f6_timestamp varchar_f6_timestamp varchar(255);
Warnings:
Level Note
Code 1265
Message Data truncated for column 'time4_f0_timestamp' at row 1
Level Note
Code 1265
Message Data truncated for column 'date_f2_timestamp' at row 1
select * from t1;
time4_f0_timestamp 11:14:17.0000
datetime3_f1_timestamp 2010-11-12 11:14:17.700
date_f2_timestamp 2010-11-12
double_f3_timestamp 20101112111417.766
decimal5_f4_timestamp 20101112111417.76540
bigint_f5_timestamp 20101112111417
varchar_f6_timestamp 2010-11-12 11:14:17.765432
alter table t1 modify time4_f0_timestamp timestamp(0), modify datetime3_f1_timestamp timestamp(1), modify date_f2_timestamp timestamp(2), modify double_f3_timestamp timestamp(3), modify decimal5_f4_timestamp timestamp(4), modify bigint_f5_timestamp timestamp(5), modify varchar_f6_timestamp timestamp(6);
select * from t1;
time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.766
decimal5_f4_timestamp 2010-11-12 11:14:17.7654
bigint_f5_timestamp 2010-11-12 11:14:17.00000
varchar_f6_timestamp 2010-11-12 11:14:17.765432
delete from t1;
insert t1 select * from t2;
select * from t1;
time4_f0_timestamp 2001-02-03 11:14:17
datetime3_f1_timestamp 2010-11-12 11:14:17.7
date_f2_timestamp 2010-11-12 00:00:00.00
double_f3_timestamp 2010-11-12 11:14:17.765
decimal5_f4_timestamp 2010-11-12 11:14:17.7654
bigint_f5_timestamp 2010-11-12 11:14:17.00000
varchar_f6_timestamp 2010-11-12 11:14:17.765432
drop table t1, t2;
create table t1 (a timestamp(6)NOT NULL DEFAULT '0000-00-00 00:00:00.000000', b timestamp(6)NOT NULL DEFAULT '0000-00-00 00:00:00.000000');
create procedure foo(x timestamp, y timestamp(4)) insert into t1 values (x, y);
call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123');
select * from t1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
create procedure bar(a int, c timestamp(5))
begin
declare b timestamp(4);
set b = c + interval a microsecond;
insert t1 values (b, c + interval a microsecond);
end|
call bar(1111111, '2011-01-02 3:4:5.123456');
select * from t1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop procedure foo;
drop procedure bar;
create function xyz(s char(20)) returns timestamp(4)
return addtime('2010-10-10 10:10:10.101010', s);
select xyz('1:1:1.010101');
xyz('1:1:1.010101')
2010-10-10 11:11:11.1111
drop function xyz;
create view v1 as select * from t1 group by a,b;
select * from v1;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
show columns from v1;
Field Type Null Key Default Extra
a timestamp(6) NO 0000-00-00 00:00:00.000000
b timestamp(6) NO 0000-00-00 00:00:00.000000
create table t2 select * from v1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
`b` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2;
a b
2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100
2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561
drop view v1;
drop table t1, t2;
SET timestamp=DEFAULT;
set time_zone='+03:00';
set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456;
create table t1 (a timestamp(5) DEFAULT CURRENT_TIMESTAMP);
insert t1 values ();
select * from t1;
a
2011-01-01 01:01:01.12345
drop table t1;
create or replace table t1 (a timestamp(5) default current_timestamp);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) default current_timestamp());
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) default current_timestamp(2));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT current_timestamp(2)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert t1 () values ();
select * from t1;
a
2011-01-01 01:01:01.12000
create or replace table t1 (a timestamp(5) default current_timestamp(5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) default current_timestamp(6));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) on update current_timestamp);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000' ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) on update current_timestamp());
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000' ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) on update current_timestamp(3));
ERROR HY000: Invalid ON UPDATE clause for 'a' column
create or replace table t1 (a timestamp(5) on update current_timestamp(5));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000' ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create or replace table t1 (a timestamp(5) on update current_timestamp(6));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` timestamp(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000' ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
#
# Start of 10.4 tests
#
#
# MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()
#
# ROUND(timestamp) and TRUNCATE(timestamp) currently return DATETIME.
# This may change in the future to return TIMESTAMP.
CREATE TABLE t1 (a1 TIMESTAMP(6) NULL DEFAULT '2001-01-01 00:00:00', a2 TIMESTAMP(6) NOT NULL);
CREATE TABLE t2 AS SELECT
ROUND(a1) AS r1,
ROUND(a2) AS r2,
TRUNCATE(a1,0) AS t1,
TRUNCATE(a2,0) AS t2
FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`r1` datetime DEFAULT NULL,
`r2` datetime DEFAULT NULL,
`t1` datetime DEFAULT NULL,
`t2` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
SET time_zone='+00:00';
CREATE TABLE t1 (a TIMESTAMP(6));
INSERT INTO t1 VALUES
('1970-01-01 00:00:01.999999'),
('2000-01-01 00:00:00.999999'),
('2000-01-01 23:59:59.999999'),
('2000-02-29 23:59:59.999999'),
('2000-12-31 23:59:59.999999'),
('2001-01-01 00:00:00.999999'),
('2001-01-01 23:59:59.999999'),
('2001-02-28 23:59:59.999999'),
('2001-12-31 23:59:59.999999'),
('2038-01-19 03:14:07.999999');
SELECT a, TRUNCATE(a,0) FROM t1;
a TRUNCATE(a,0)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01
2000-01-01 00:00:00.999999 2000-01-01 00:00:00
2000-01-01 23:59:59.999999 2000-01-01 23:59:59
2000-02-29 23:59:59.999999 2000-02-29 23:59:59
2000-12-31 23:59:59.999999 2000-12-31 23:59:59
2001-01-01 00:00:00.999999 2001-01-01 00:00:00
2001-01-01 23:59:59.999999 2001-01-01 23:59:59
2001-02-28 23:59:59.999999 2001-02-28 23:59:59
2001-12-31 23:59:59.999999 2001-12-31 23:59:59
2038-01-19 03:14:07.999999 2038-01-19 03:14:07
SELECT a, TRUNCATE(a,1) FROM t1;
a TRUNCATE(a,1)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.9
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.9
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.9
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.9
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.9
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.9
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.9
SELECT a, TRUNCATE(a,2) FROM t1;
a TRUNCATE(a,2)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.99
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.99
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.99
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.99
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.99
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.99
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.99
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.99
SELECT a, TRUNCATE(a,3) FROM t1;
a TRUNCATE(a,3)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999
SELECT a, TRUNCATE(a,4) FROM t1;
a TRUNCATE(a,4)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.9999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.9999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.9999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.9999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.9999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.9999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.9999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.9999
SELECT a, TRUNCATE(a,5) FROM t1;
a TRUNCATE(a,5)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.99999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.99999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.99999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.99999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.99999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.99999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.99999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.99999
SELECT a, TRUNCATE(a,6) FROM t1;
a TRUNCATE(a,6)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999
SELECT a, TRUNCATE(a,7) FROM t1;
a TRUNCATE(a,7)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999
SELECT a, TRUNCATE(a,-1) FROM t1;
a TRUNCATE(a,-1)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01
2000-01-01 00:00:00.999999 2000-01-01 00:00:00
2000-01-01 23:59:59.999999 2000-01-01 23:59:59
2000-02-29 23:59:59.999999 2000-02-29 23:59:59
2000-12-31 23:59:59.999999 2000-12-31 23:59:59
2001-01-01 00:00:00.999999 2001-01-01 00:00:00
2001-01-01 23:59:59.999999 2001-01-01 23:59:59
2001-02-28 23:59:59.999999 2001-02-28 23:59:59
2001-12-31 23:59:59.999999 2001-12-31 23:59:59
2038-01-19 03:14:07.999999 2038-01-19 03:14:07
SELECT a, TRUNCATE(a,-6) FROM t1;
a TRUNCATE(a,-6)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01
2000-01-01 00:00:00.999999 2000-01-01 00:00:00
2000-01-01 23:59:59.999999 2000-01-01 23:59:59
2000-02-29 23:59:59.999999 2000-02-29 23:59:59
2000-12-31 23:59:59.999999 2000-12-31 23:59:59
2001-01-01 00:00:00.999999 2001-01-01 00:00:00
2001-01-01 23:59:59.999999 2001-01-01 23:59:59
2001-02-28 23:59:59.999999 2001-02-28 23:59:59
2001-12-31 23:59:59.999999 2001-12-31 23:59:59
2038-01-19 03:14:07.999999 2038-01-19 03:14:07
SELECT a, ROUND(a) FROM t1;
a ROUND(a)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02
2000-01-01 00:00:00.999999 2000-01-01 00:00:01
2000-01-01 23:59:59.999999 2000-01-02 00:00:00
2000-02-29 23:59:59.999999 2000-03-01 00:00:00
2000-12-31 23:59:59.999999 2001-01-01 00:00:00
2001-01-01 00:00:00.999999 2001-01-01 00:00:01
2001-01-01 23:59:59.999999 2001-01-02 00:00:00
2001-02-28 23:59:59.999999 2001-03-01 00:00:00
2001-12-31 23:59:59.999999 2002-01-01 00:00:00
2038-01-19 03:14:07.999999 2038-01-19 03:14:08
SELECT a, ROUND(a,0) FROM t1;
a ROUND(a,0)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02
2000-01-01 00:00:00.999999 2000-01-01 00:00:01
2000-01-01 23:59:59.999999 2000-01-02 00:00:00
2000-02-29 23:59:59.999999 2000-03-01 00:00:00
2000-12-31 23:59:59.999999 2001-01-01 00:00:00
2001-01-01 00:00:00.999999 2001-01-01 00:00:01
2001-01-01 23:59:59.999999 2001-01-02 00:00:00
2001-02-28 23:59:59.999999 2001-03-01 00:00:00
2001-12-31 23:59:59.999999 2002-01-01 00:00:00
2038-01-19 03:14:07.999999 2038-01-19 03:14:08
SELECT a, ROUND(a,1) FROM t1;
a ROUND(a,1)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02.0
2000-01-01 00:00:00.999999 2000-01-01 00:00:01.0
2000-01-01 23:59:59.999999 2000-01-02 00:00:00.0
2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0
2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0
2001-01-01 00:00:00.999999 2001-01-01 00:00:01.0
2001-01-01 23:59:59.999999 2001-01-02 00:00:00.0
2001-02-28 23:59:59.999999 2001-03-01 00:00:00.0
2001-12-31 23:59:59.999999 2002-01-01 00:00:00.0
2038-01-19 03:14:07.999999 2038-01-19 03:14:08.0
SELECT a, ROUND(a,2) FROM t1;
a ROUND(a,2)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02.00
2000-01-01 00:00:00.999999 2000-01-01 00:00:01.00
2000-01-01 23:59:59.999999 2000-01-02 00:00:00.00
2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00
2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00
2001-01-01 00:00:00.999999 2001-01-01 00:00:01.00
2001-01-01 23:59:59.999999 2001-01-02 00:00:00.00
2001-02-28 23:59:59.999999 2001-03-01 00:00:00.00
2001-12-31 23:59:59.999999 2002-01-01 00:00:00.00
2038-01-19 03:14:07.999999 2038-01-19 03:14:08.00
SELECT a, ROUND(a,3) FROM t1;
a ROUND(a,3)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02.000
2000-01-01 00:00:00.999999 2000-01-01 00:00:01.000
2000-01-01 23:59:59.999999 2000-01-02 00:00:00.000
2000-02-29 23:59:59.999999 2000-03-01 00:00:00.000
2000-12-31 23:59:59.999999 2001-01-01 00:00:00.000
2001-01-01 00:00:00.999999 2001-01-01 00:00:01.000
2001-01-01 23:59:59.999999 2001-01-02 00:00:00.000
2001-02-28 23:59:59.999999 2001-03-01 00:00:00.000
2001-12-31 23:59:59.999999 2002-01-01 00:00:00.000
2038-01-19 03:14:07.999999 2038-01-19 03:14:08.000
SELECT a, ROUND(a,4) FROM t1;
a ROUND(a,4)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02.0000
2000-01-01 00:00:00.999999 2000-01-01 00:00:01.0000
2000-01-01 23:59:59.999999 2000-01-02 00:00:00.0000
2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0000
2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0000
2001-01-01 00:00:00.999999 2001-01-01 00:00:01.0000
2001-01-01 23:59:59.999999 2001-01-02 00:00:00.0000
2001-02-28 23:59:59.999999 2001-03-01 00:00:00.0000
2001-12-31 23:59:59.999999 2002-01-01 00:00:00.0000
2038-01-19 03:14:07.999999 2038-01-19 03:14:08.0000
SELECT a, ROUND(a,5) FROM t1;
a ROUND(a,5)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02.00000
2000-01-01 00:00:00.999999 2000-01-01 00:00:01.00000
2000-01-01 23:59:59.999999 2000-01-02 00:00:00.00000
2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00000
2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00000
2001-01-01 00:00:00.999999 2001-01-01 00:00:01.00000
2001-01-01 23:59:59.999999 2001-01-02 00:00:00.00000
2001-02-28 23:59:59.999999 2001-03-01 00:00:00.00000
2001-12-31 23:59:59.999999 2002-01-01 00:00:00.00000
2038-01-19 03:14:07.999999 2038-01-19 03:14:08.00000
SELECT a, ROUND(a,6) FROM t1;
a ROUND(a,6)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999
SELECT a, ROUND(a,7) FROM t1;
a ROUND(a,7)
1970-01-01 00:00:01.999999 1970-01-01 00:00:01.999999
2000-01-01 00:00:00.999999 2000-01-01 00:00:00.999999
2000-01-01 23:59:59.999999 2000-01-01 23:59:59.999999
2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999
2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999
2001-01-01 00:00:00.999999 2001-01-01 00:00:00.999999
2001-01-01 23:59:59.999999 2001-01-01 23:59:59.999999
2001-02-28 23:59:59.999999 2001-02-28 23:59:59.999999
2001-12-31 23:59:59.999999 2001-12-31 23:59:59.999999
2038-01-19 03:14:07.999999 2038-01-19 03:14:07.999999
SELECT a, ROUND(a,-1) FROM t1;
a ROUND(a,-1)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02
2000-01-01 00:00:00.999999 2000-01-01 00:00:01
2000-01-01 23:59:59.999999 2000-01-02 00:00:00
2000-02-29 23:59:59.999999 2000-03-01 00:00:00
2000-12-31 23:59:59.999999 2001-01-01 00:00:00
2001-01-01 00:00:00.999999 2001-01-01 00:00:01
2001-01-01 23:59:59.999999 2001-01-02 00:00:00
2001-02-28 23:59:59.999999 2001-03-01 00:00:00
2001-12-31 23:59:59.999999 2002-01-01 00:00:00
2038-01-19 03:14:07.999999 2038-01-19 03:14:08
SELECT a, ROUND(a,-6) FROM t1;
a ROUND(a,-6)
1970-01-01 00:00:01.999999 1970-01-01 00:00:02
2000-01-01 00:00:00.999999 2000-01-01 00:00:01
2000-01-01 23:59:59.999999 2000-01-02 00:00:00
2000-02-29 23:59:59.999999 2000-03-01 00:00:00
2000-12-31 23:59:59.999999 2001-01-01 00:00:00
2001-01-01 00:00:00.999999 2001-01-01 00:00:01
2001-01-01 23:59:59.999999 2001-01-02 00:00:00
2001-02-28 23:59:59.999999 2001-03-01 00:00:00
2001-12-31 23:59:59.999999 2002-01-01 00:00:00
2038-01-19 03:14:07.999999 2038-01-19 03:14:08
DROP TABLE t1;
SET time_zone=DEFAULT;
#
# MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()
#
CREATE TABLE t1 (a TIMESTAMP NOT NULL);
CREATE TABLE t2 AS SELECT FLOOR(a), CEILING(a) FROM t1;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`FLOOR(a)` datetime DEFAULT NULL,
`CEILING(a)` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1, t2;
#
# End of 10.4 tests
#
|