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 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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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) NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT current_timestamp(2)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT NULL ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT NULL ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT NULL ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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) NULL DEFAULT NULL ON UPDATE current_timestamp(5)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
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 COLLATE=latin1_swedish_ci
DROP TABLE t1, t2;
#
# End of 10.4 tests
#