MDEV-4724 Some temporal functions do not preserve microseconds

18 files changed, 436 insertions, 135 deletions

diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index dd346cb94dc..88255a06adb 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -257,70 +257,70 @@ Tuesday 52 2001	%W %V %X	2002-01-01
 15-2001-1	%d-%Y-%c	2001-01-15
 select date,format,TIME(str_to_date(date, format)) as time from t1;
 date	format	time
-2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	10:11:12
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
-0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
-03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
-2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
+2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	10:11:12.000000
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02.000000
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02.000000
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02.000000
+2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12.000000
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	02:11:12.123450
 2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	00:11:12.123450
-2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12
-10:20:10	%H:%i:%s	10:20:10
-10:20:10	%h:%i:%s.%f	10:20:10
-10:20:10	%T	10:20:10
-10:20:10AM	%h:%i:%s%p	10:20:10
-10:20:10AM	%r	10:20:10
+2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12.000000
+10:20:10	%H:%i:%s	10:20:10.000000
+10:20:10	%h:%i:%s.%f	10:20:10.000000
+10:20:10	%T	10:20:10.000000
+10:20:10AM	%h:%i:%s%p	10:20:10.000000
+10:20:10AM	%r	10:20:10.000000
 10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
-15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
-15 September 2001	%d %M %Y	00:00:00
-15 SEPTEMB 2001	%d %M %Y	00:00:00
-15 MAY 2001	%d %b %Y	00:00:00
-15th May 2001	%D %b %Y	00:00:00
-Sunday 15 MAY 2001	%W %d %b %Y	00:00:00
-Sund 15 MAY 2001	%W %d %b %Y	00:00:00
-Tuesday 00 2002	%W %U %Y	00:00:00
-Thursday 53 1998	%W %u %Y	00:00:00
-Sunday 01 2001	%W %v %x	00:00:00
-Tuesday 52 2001	%W %V %X	00:00:00
-060 2004	%j %Y	00:00:00
-4 53 1998	%w %u %Y	00:00:00
-15-01-2001	%d-%m-%Y %H:%i:%S	00:00:00
-15-01-20	%d-%m-%y	00:00:00
-15-2001-1	%d-%Y-%c	00:00:00
+15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58.000000
+15 September 2001	%d %M %Y	00:00:00.000000
+15 SEPTEMB 2001	%d %M %Y	00:00:00.000000
+15 MAY 2001	%d %b %Y	00:00:00.000000
+15th May 2001	%D %b %Y	00:00:00.000000
+Sunday 15 MAY 2001	%W %d %b %Y	00:00:00.000000
+Sund 15 MAY 2001	%W %d %b %Y	00:00:00.000000
+Tuesday 00 2002	%W %U %Y	00:00:00.000000
+Thursday 53 1998	%W %u %Y	00:00:00.000000
+Sunday 01 2001	%W %v %x	00:00:00.000000
+Tuesday 52 2001	%W %V %X	00:00:00.000000
+060 2004	%j %Y	00:00:00.000000
+4 53 1998	%w %u %Y	00:00:00.000000
+15-01-2001	%d-%m-%Y %H:%i:%S	00:00:00.000000
+15-01-20	%d-%m-%y	00:00:00.000000
+15-2001-1	%d-%Y-%c	00:00:00.000000
 select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
 date	format	time2
-2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	10:11:12
-03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
-0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
-03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02
-2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
+2003-01-02 10:11:12	%Y-%m-%d %H:%i:%S	10:11:12.000000
+03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02.000000
+0003-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02.000000
+03-01-02 8:11:2.123456	%Y-%m-%d %H:%i:%S.%#	08:11:02.000000
+2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12.000000
 2003-01-02 01:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f%p	01:11:12.123450
 2003-01-02 02:11:12.12345AM	%Y-%m-%d %h:%i:%S.%f %p	02:11:12.123450
 2003-01-02 12:11:12.12345 am	%Y-%m-%d %h:%i:%S.%f%p	00:11:12.123450
-2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12
-10:20:10	%H:%i:%s	10:20:10
-10:20:10	%h:%i:%s.%f	10:20:10
-10:20:10	%T	10:20:10
-10:20:10AM	%h:%i:%s%p	10:20:10
-10:20:10AM	%r	10:20:10
+2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12.000000
+10:20:10	%H:%i:%s	10:20:10.000000
+10:20:10	%h:%i:%s.%f	10:20:10.000000
+10:20:10	%T	10:20:10.000000
+10:20:10AM	%h:%i:%s%p	10:20:10.000000
+10:20:10AM	%r	10:20:10.000000
 10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
-15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
-15 September 2001	%d %M %Y	00:00:00
-15 SEPTEMB 2001	%d %M %Y	00:00:00
-15 MAY 2001	%d %b %Y	00:00:00
-15th May 2001	%D %b %Y	00:00:00
-Sunday 15 MAY 2001	%W %d %b %Y	00:00:00
-Sund 15 MAY 2001	%W %d %b %Y	00:00:00
-Tuesday 00 2002	%W %U %Y	00:00:00
-Thursday 53 1998	%W %u %Y	00:00:00
-Sunday 01 2001	%W %v %x	00:00:00
-Tuesday 52 2001	%W %V %X	00:00:00
-060 2004	%j %Y	00:00:00
-4 53 1998	%w %u %Y	00:00:00
-15-01-2001	%d-%m-%Y %H:%i:%S	00:00:00
-15-01-20	%d-%m-%y	00:00:00
-15-2001-1	%d-%Y-%c	00:00:00
+15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58.000000
+15 September 2001	%d %M %Y	00:00:00.000000
+15 SEPTEMB 2001	%d %M %Y	00:00:00.000000
+15 MAY 2001	%d %b %Y	00:00:00.000000
+15th May 2001	%D %b %Y	00:00:00.000000
+Sunday 15 MAY 2001	%W %d %b %Y	00:00:00.000000
+Sund 15 MAY 2001	%W %d %b %Y	00:00:00.000000
+Tuesday 00 2002	%W %U %Y	00:00:00.000000
+Thursday 53 1998	%W %u %Y	00:00:00.000000
+Sunday 01 2001	%W %v %x	00:00:00.000000
+Tuesday 52 2001	%W %V %X	00:00:00.000000
+060 2004	%j %Y	00:00:00.000000
+4 53 1998	%w %u %Y	00:00:00.000000
+15-01-2001	%d-%m-%Y %H:%i:%S	00:00:00.000000
+15-01-20	%d-%m-%y	00:00:00.000000
+15-2001-1	%d-%Y-%c	00:00:00.000000
 select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
 concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'))
 2003-01-02 08:11:02.123456
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index feb92da3321..138b1a99d97 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -156,7 +156,7 @@ timestamp("2001-12-01", "25:01:01")
 2001-12-02 01:01:01
 select timestamp("2001-12-01 01:01:01.000100");
 timestamp("2001-12-01 01:01:01.000100")
-2001-12-01 01:01:01.000100
+2001-12-01 01:01:01.0001
 select timestamp("2001-12-01");
 timestamp("2001-12-01")
 2001-12-01 00:00:00
@@ -200,12 +200,12 @@ f3	time(6)	YES		NULL
 f4	time(6)	YES		NULL	
 f5	time(6)	YES		NULL	
 f6	time	YES		NULL	
-f7	datetime(6)	YES		NULL	
+f7	datetime	YES		NULL	
 f8	date	YES		NULL	
 f9	time(6)	YES		NULL	
 select * from t1;
 f1	f2	f3	f4	f5	f6	f7	f8	f9
-1997-01-01	1998-01-02 01:01:00.000003	49:01:01.000001	46:58:57.999999	-24:00:00.000001	10:11:12	2001-12-01 01:01:01.000000	1997-12-31	23:59:59.000001
+1997-01-01	1998-01-02 01:01:00.000003	49:01:01.000001	46:58:57.999999	-24:00:00.000001	10:11:12	2001-12-01 01:01:01	1997-12-31	23:59:59.000001
 create table test(t1 datetime, t2 time, t3 time, t4 datetime);
 insert into test values 
 ('2001-01-01 01:01:01', '01:01:01', null, '2001-02-01 01:01:01'),
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 8bbc692aa4a..99104136f2b 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -11,17 +11,17 @@ now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(no
 0	0	0
 select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
 from_unixtime(unix_timestamp("1994-03-02 10:11:12"))	from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s")	from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
-1994-03-02 10:11:12.000000	1994-03-02 10:11:12	19940302101112.000000
+1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112
 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
 sec_to_time(time_to_sec("0:30:47")/6.21);
 sec_to_time(9001)	sec_to_time(9001)+0	time_to_sec("15:12:22")	sec_to_time(time_to_sec("0:30:47")/6.21)
-02:30:01	23001	54742.000000	00:04:57.423510
+02:30:01	23001	54742	00:04:57.4235
 select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899);
 sec_to_time(9001.1)	time_to_sec('15:12:22.123456')	time_to_sec(15.5566778899)
 02:30:01.1	54742.123456	15.556677
 select sec_to_time(time_to_sec('-838:59:59'));
 sec_to_time(time_to_sec('-838:59:59'))
--838:59:59.000000
+-838:59:59
 select sec_to_time('9001.1'), sec_to_time('1234567890123.123');
 sec_to_time('9001.1')	sec_to_time('1234567890123.123')
 02:30:01.100000	838:59:59.999999
@@ -77,7 +77,7 @@ HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)
 23	3	22
 select TIME(230322), TIME(230322.33), TIME("230322.33");
 TIME(230322)	TIME(230322.33)	TIME("230322.33")
-23:03:22	23:03:22.33	23:03:22.330000
+23:03:22	23:03:22.33	23:03:22.33
 select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
 week(19980101)	week(19970101)	week(19980101,1)	week(19970101,1)
 0	0	1	1
@@ -569,7 +569,7 @@ select @a:=FROM_UNIXTIME(1);
 1970-01-01 03:00:01
 select unix_timestamp(@a);
 unix_timestamp(@a)
-1.000000
+1
 select unix_timestamp('1969-12-01 19:00:01');
 unix_timestamp('1969-12-01 19:00:01')
 NULL
@@ -611,10 +611,10 @@ unix_timestamp('1969-12-30 01:00:00')
 NULL
 select unix_timestamp('2038-01-17 12:00:00');
 unix_timestamp('2038-01-17 12:00:00')
-2147331600.000000
+2147331600
 select unix_timestamp('1970-01-01 03:00:01');
 unix_timestamp('1970-01-01 03:00:01')
-1.000000
+1
 select unix_timestamp('2038-01-19 07:14:07');
 unix_timestamp('2038-01-19 07:14:07')
 NULL
@@ -1947,7 +1947,7 @@ TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
 CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
@@ -1957,7 +1957,7 @@ TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
 DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
 TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 SELECT
 CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
 CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
@@ -1967,7 +1967,7 @@ TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
 CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
@@ -1977,7 +1977,7 @@ TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
 DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
 TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 SELECT
 CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
 CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
@@ -1987,7 +1987,7 @@ TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
 CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
@@ -1997,7 +1997,7 @@ TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 SELECT
 CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
 CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
@@ -2007,7 +2007,7 @@ TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') E
 DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
 TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
 CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
@@ -2017,7 +2017,7 @@ TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') E
 DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
 TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 CREATE TABLE t1 AS SELECT
 CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1,
 CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2,
@@ -2032,3 +2032,148 @@ t1	CREATE TABLE `t1` (
   `c4` varbinary(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+# MDEV-4724 Some temporal functions do not preserve microseconds
+#
+SELECT MAKETIME(10,10,10.231);
+MAKETIME(10,10,10.231)
+10:10:10.231
+SELECT MAKETIME(0, 0, 59.9);
+MAKETIME(0, 0, 59.9)
+00:00:59.9
+CREATE TABLE t1 AS SELECT
+MAKETIME(10,00,00),
+MAKETIME(10,00,00.1),
+MAKETIME(10,00,00.12),
+MAKETIME(10,00,00.123),
+MAKETIME(10,00,00.1234),
+MAKETIME(10,00,00.12345),
+MAKETIME(10,00,00.123456);
+SHOW COLUMNS FROM t1;
+Field	Type	Null	Key	Default	Extra
+MAKETIME(10,00,00)	time	YES		NULL	
+MAKETIME(10,00,00.1)	time(1)	YES		NULL	
+MAKETIME(10,00,00.12)	time(2)	YES		NULL	
+MAKETIME(10,00,00.123)	time(3)	YES		NULL	
+MAKETIME(10,00,00.1234)	time(4)	YES		NULL	
+MAKETIME(10,00,00.12345)	time(5)	YES		NULL	
+MAKETIME(10,00,00.123456)	time(6)	YES		NULL	
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT
+TIME('10:00:00'),
+TIME('10:00:00.1'),
+TIME('10:00:00.12'),
+TIME('10:00:00.123'),
+TIME('10:00:00.1234'),
+TIME('10:00:00.12345'),
+TIME('10:00:00.12346');
+SHOW COLUMNS FROM t1;
+Field	Type	Null	Key	Default	Extra
+TIME('10:00:00')	time	YES		NULL	
+TIME('10:00:00.1')	time(1)	YES		NULL	
+TIME('10:00:00.12')	time(2)	YES		NULL	
+TIME('10:00:00.123')	time(3)	YES		NULL	
+TIME('10:00:00.1234')	time(4)	YES		NULL	
+TIME('10:00:00.12345')	time(5)	YES		NULL	
+TIME('10:00:00.12346')	time(5)	YES		NULL	
+DROP TABLE t1;
+SET TIME_ZONE='+00:00';
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17');
+SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17');
+NOW()	UNIX_TIMESTAMP()	UNIX_TIMESTAMP(NOW())	UNIX_TIMESTAMP('2012-10-16 22:46:17')
+2012-10-16 22:46:17	1350427577	1350427577	1350427577
+SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456');
+SELECT @@timestamp, FROM_UNIXTIME(@@timestamp);
+@@timestamp	FROM_UNIXTIME(@@timestamp)
+97445.123456	1970-01-02 03:04:05.123456
+SET TIME_ZONE=DEFAULT;
+SET TIMESTAMP=DEFAULT;
+SELECT TIME('2012-10-16 15:54:16.12');
+TIME('2012-10-16 15:54:16.12')
+15:54:16.12
+SELECT TIMESTAMP('2012-10-16 15:54:16.12');
+TIMESTAMP('2012-10-16 15:54:16.12')
+2012-10-16 15:54:16.12
+SELECT TIMEDIFF('10:10:10.1','00:00:00');
+TIMEDIFF('10:10:10.1','00:00:00')
+10:10:10.1
+SELECT TIME_TO_SEC('10:10:10');
+TIME_TO_SEC('10:10:10')
+36610
+SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12');
+ADDTIME(TIME('10:10:10.1'),'10:10:10.12')
+20:20:20.22
+SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12');
+ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12')
+2001-01-01 20:20:20.22
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND)
+2001-01-01 00:00:01
+SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.1
+SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.12
+SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.123
+SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.1234
+SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.12345
+SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.123456
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND)
+2001-01-01 00:00:01
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND)
+2001-01-01 00:00:01.1
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND)
+2001-01-01 00:00:01.12
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND)
+2001-01-01 00:00:01.123
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND)
+2001-01-01 00:00:01.1234
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND)
+2001-01-01 00:00:01.12345
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND)
+2001-01-01 00:00:01.123456
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND)
+2000-12-31 23:59:59
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND)
+2000-12-31 23:59:58.9
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND)
+2000-12-31 23:59:58.88
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND)
+2000-12-31 23:59:58.877
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND)
+2000-12-31 23:59:58.8766
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND)
+2000-12-31 23:59:58.87655
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND)
+2000-12-31 23:59:58.876544
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND)
+2001-01-01 00:00:00.000000
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'xxx'
+SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00');
+CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00')
+2001-01-01 11:20:30.12
diff --git a/mysql-test/r/func_time_hires.result b/mysql-test/r/func_time_hires.result
index 4aa0333c4b4..1a736c89573 100644
--- a/mysql-test/r/func_time_hires.result
+++ b/mysql-test/r/func_time_hires.result
@@ -15,11 +15,11 @@ current_time(3)	01:01:01.123
 current_timestamp(4)	2011-01-01 01:01:01.1234
 localtime(5)	2011-01-01 01:01:01.12345
 localtimestamp(6)	2011-01-01 01:01:01.123456
-time_to_sec('12:34:56')	45296.000000
-time_to_sec('12:34:56.789')	45296.789000
+time_to_sec('12:34:56')	45296
+time_to_sec('12:34:56.789')	45296.789
 select sec_to_time(time_to_sec('1:2:3')), sec_to_time(time_to_sec('2:3:4.567890'));
-sec_to_time(time_to_sec('1:2:3'))	01:02:03.000000
-sec_to_time(time_to_sec('2:3:4.567890'))	02:03:04.567890
+sec_to_time(time_to_sec('1:2:3'))	01:02:03
+sec_to_time(time_to_sec('2:3:4.567890'))	02:03:04.56789
 select time_to_sec(sec_to_time(11111)), time_to_sec(sec_to_time(11111.22222));
 time_to_sec(sec_to_time(11111))	11111
 time_to_sec(sec_to_time(11111.22222))	11111.22222
@@ -48,7 +48,7 @@ t1	CREATE TABLE `t1` (
   `localtime(5)` datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000',
   `localtimestamp(6)` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
   `time_to_sec(123456)` bigint(17) DEFAULT NULL,
-  `time_to_sec('12:34:56.789')` decimal(22,6) DEFAULT NULL
+  `time_to_sec('12:34:56.789')` decimal(19,3) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t1;
 sec_to_time(12345)	03:25:45
@@ -63,15 +63,15 @@ current_timestamp(4)	2011-01-01 01:01:01.1234
 localtime(5)	2011-01-01 01:01:01.12345
 localtimestamp(6)	2011-01-01 01:01:01.123456
 time_to_sec(123456)	45296
-time_to_sec('12:34:56.789')	45296.789000
+time_to_sec('12:34:56.789')	45296.789
 drop table t1;
 select unix_timestamp('2011-01-01 01:01:01'), unix_timestamp('2011-01-01 01:01:01.123456'), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)));;
-unix_timestamp('2011-01-01 01:01:01')	1293832861.000000
+unix_timestamp('2011-01-01 01:01:01')	1293832861
 unix_timestamp('2011-01-01 01:01:01.123456')	1293832861.123456
 unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0)))	1293832861
 unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)))	1293832861.1234
 select from_unixtime(unix_timestamp('2011/1/1 1:1:1')), from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))));;
-from_unixtime(unix_timestamp('2011/1/1 1:1:1'))	2011-01-01 01:01:01.000000
+from_unixtime(unix_timestamp('2011/1/1 1:1:1'))	2011-01-01 01:01:01
 from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456'))	2011-01-01 01:01:01.123456
 from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0))))	2011-01-01 01:01:01
 from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))))	2011-01-01 01:01:01.1234
@@ -167,7 +167,7 @@ CONVERT_TZ('2011-01-02 12:00:00', '+00:00', '+03:00')
 2011-01-02 15:00:00
 SELECT CONVERT_TZ('2011-01-02 12:00:00.123', '+00:00', '+03:00');
 CONVERT_TZ('2011-01-02 12:00:00.123', '+00:00', '+03:00')
-2011-01-02 15:00:00.123000
+2011-01-02 15:00:00.123
 SELECT CONVERT_TZ('2011-01-02 12:00:00.123456', '+00:00', '+03:00');
 CONVERT_TZ('2011-01-02 12:00:00.123456', '+00:00', '+03:00')
 2011-01-02 15:00:00.123456
@@ -200,7 +200,7 @@ time(f1)
 alter table t1 modify f1 varchar(100);
 select time(f1) from t1;
 time(f1)
-21:00:00
+21:00:00.000000
 select time(f1) from t1 union all select time(f1 + interval 1 second) from t1;
 time(f1)
 21:00:00.000000
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index cce5d6e9e2f..4b245edef09 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -1793,8 +1793,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` decimal(22,6) DEFAULT NULL,
-  `param10` decimal(65,30) DEFAULT NULL,
+  `const10` bigint(17) DEFAULT NULL,
+  `param10` bigint(20) DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1823,8 +1823,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	246	24	16	Y	0	6	63
-def	test	t5	t5	param10	param10	246	67	40	Y	0	30	63
+def	test	t5	t5	const10	const10	8	17	9	Y	32768	0	63
+def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -1850,8 +1850,8 @@ const08	1991-08-05 01:01:01
 param08	1991-08-05 01:01:01
 const09	1991-08-05 01:01:01
 param09	1991-08-05 01:01:01
-const10	662680861.000000
-param10	662680861.000000000000000000000000000000
+const10	662680861
+param10	662680861
 const11	1991
 param11	1991
 const12	NULL
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 328df9e5978..4798d63b5e7 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -1776,8 +1776,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` decimal(22,6) DEFAULT NULL,
-  `param10` decimal(65,30) DEFAULT NULL,
+  `const10` bigint(17) DEFAULT NULL,
+  `param10` bigint(20) DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1806,8 +1806,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	246	24	16	Y	0	6	63
-def	test	t5	t5	param10	param10	246	67	40	Y	0	30	63
+def	test	t5	t5	const10	const10	8	17	9	Y	32768	0	63
+def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -1833,8 +1833,8 @@ const08	1991-08-05 01:01:01
 param08	1991-08-05 01:01:01
 const09	1991-08-05 01:01:01
 param09	1991-08-05 01:01:01
-const10	662680861.000000
-param10	662680861.000000000000000000000000000000
+const10	662680861
+param10	662680861
 const11	1991
 param11	1991
 const12	NULL
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index cc4330c57e4..f94273ea3ad 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -1777,8 +1777,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` decimal(22,6) DEFAULT NULL,
-  `param10` decimal(65,30) DEFAULT NULL,
+  `const10` bigint(17) DEFAULT NULL,
+  `param10` bigint(20) DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1807,8 +1807,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	246	24	16	Y	0	6	63
-def	test	t5	t5	param10	param10	246	67	40	Y	0	30	63
+def	test	t5	t5	const10	const10	8	17	9	Y	32768	0	63
+def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -1834,8 +1834,8 @@ const08	1991-08-05 01:01:01
 param08	1991-08-05 01:01:01
 const09	1991-08-05 01:01:01
 param09	1991-08-05 01:01:01
-const10	662680861.000000
-param10	662680861.000000000000000000000000000000
+const10	662680861
+param10	662680861
 const11	1991
 param11	1991
 const12	NULL
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index fe1665fc24e..eed82775d7b 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -1713,8 +1713,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` decimal(22,6) DEFAULT NULL,
-  `param10` decimal(65,30) DEFAULT NULL,
+  `const10` bigint(17) DEFAULT NULL,
+  `param10` bigint(20) DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1743,8 +1743,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	246	24	16	Y	0	6	63
-def	test	t5	t5	param10	param10	246	67	40	Y	0	30	63
+def	test	t5	t5	const10	const10	8	17	9	Y	32768	0	63
+def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -1770,8 +1770,8 @@ const08	1991-08-05 01:01:01
 param08	1991-08-05 01:01:01
 const09	1991-08-05 01:01:01
 param09	1991-08-05 01:01:01
-const10	662680861.000000
-param10	662680861.000000000000000000000000000000
+const10	662680861
+param10	662680861
 const11	1991
 param11	1991
 const12	NULL
@@ -5067,8 +5067,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` decimal(22,6) DEFAULT NULL,
-  `param10` decimal(65,30) DEFAULT NULL,
+  `const10` bigint(17) DEFAULT NULL,
+  `param10` bigint(20) DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -5097,8 +5097,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	246	24	16	Y	0	6	63
-def	test	t5	t5	param10	param10	246	67	40	Y	0	30	63
+def	test	t5	t5	const10	const10	8	17	9	Y	32768	0	63
+def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -5124,8 +5124,8 @@ const08	1991-08-05 01:01:01
 param08	1991-08-05 01:01:01
 const09	1991-08-05 01:01:01
 param09	1991-08-05 01:01:01
-const10	662680861.000000
-param10	662680861.000000000000000000000000000000
+const10	662680861
+param10	662680861
 const11	1991
 param11	1991
 const12	NULL
diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result
index 5ae7e6f8117..d84fe54ba8c 100644
--- a/mysql-test/r/timezone.result
+++ b/mysql-test/r/timezone.result
@@ -7,7 +7,7 @@ select @a:=FROM_UNIXTIME(1);
 1970-01-01 01:00:01
 select unix_timestamp(@a);
 unix_timestamp(@a)
-1.000000
+1
 CREATE TABLE t1 (ts int);
 INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 01:00'));
 INSERT INTO t1 (ts) VALUES (Unix_timestamp('2002-10-27 02:00'));
@@ -44,7 +44,7 @@ unix_timestamp('1970-01-01 01:00:01'),
 unix_timestamp('2038-01-19 04:14:07'),
 unix_timestamp('2038-01-19 04:14:08');
 unix_timestamp('1970-01-01 01:00:00')	unix_timestamp('1970-01-01 01:00:01')	unix_timestamp('2038-01-19 04:14:07')	unix_timestamp('2038-01-19 04:14:08')
-0.000000	1.000000	2147483647.000000	NULL
+0	1	2147483647	NULL
 select unix_timestamp('1969-12-31 23:59:59'), unix_timestamp('1970-01-01 00:00:00'), unix_timestamp('1970-01-01 00:59:59');
 unix_timestamp('1969-12-31 23:59:59')	unix_timestamp('1970-01-01 00:00:00')	unix_timestamp('1970-01-01 00:59:59')
 NULL	NULL	NULL
diff --git a/mysql-test/r/timezone4.result b/mysql-test/r/timezone4.result
index ad0672890a2..28028bea657 100644
--- a/mysql-test/r/timezone4.result
+++ b/mysql-test/r/timezone4.result
@@ -3,4 +3,4 @@ from_unixtime(0)
 1969-12-31 14:00:00
 select unix_timestamp('1969-12-31 14:00:01');
 unix_timestamp('1969-12-31 14:00:01')
-1.000000
+1
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index 23943c3c848..c66f3f08e08 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -186,10 +186,10 @@ Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
 #
 SELECT CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')));
 CONCAT(GREATEST(TIME('00:00:01'),TIME('00:00:00')))
-00:00:01.000000
+00:00:01
 SELECT CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')));
 CONCAT(GREATEST(TIME('32 00:00:01'),TIME('00:00:00')))
-768:00:01.000000
+768:00:01
 #
 # End of 5.3 tests
 #
diff --git a/mysql-test/suite/maria/r/ps_maria.result b/mysql-test/suite/maria/r/ps_maria.result
index 3546e78512c..207ae8a59db 100644
--- a/mysql-test/suite/maria/r/ps_maria.result
+++ b/mysql-test/suite/maria/r/ps_maria.result
@@ -1793,8 +1793,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` decimal(22,6) DEFAULT NULL,
-  `param10` decimal(65,30) DEFAULT NULL,
+  `const10` bigint(17) DEFAULT NULL,
+  `param10` bigint(20) DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1823,8 +1823,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	246	24	16	Y	0	6	63
-def	test	t5	t5	param10	param10	246	67	40	Y	0	30	63
+def	test	t5	t5	const10	const10	8	17	9	Y	32768	0	63
+def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -1850,8 +1850,8 @@ const08	1991-08-05 01:01:01
 param08	1991-08-05 01:01:01
 const09	1991-08-05 01:01:01
 param09	1991-08-05 01:01:01
-const10	662680861.000000
-param10	662680861.000000000000000000000000000000
+const10	662680861
+param10	662680861
 const11	1991
 param11	1991
 const12	NULL
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 6aedf3f55df..aba78b92c3e 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1255,3 +1255,69 @@ CREATE TABLE t1 AS SELECT
   CONCAT(CASE WHEN 1 THEN TIME(101010) ELSE TIME(101010) END) AS c4;
 SHOW CREATE TABLE t1;
 DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-4724 Some temporal functions do not preserve microseconds
+--echo #
+SELECT MAKETIME(10,10,10.231);
+SELECT MAKETIME(0, 0, 59.9);
+CREATE TABLE t1 AS SELECT
+  MAKETIME(10,00,00),
+  MAKETIME(10,00,00.1),
+  MAKETIME(10,00,00.12),
+  MAKETIME(10,00,00.123),
+  MAKETIME(10,00,00.1234),
+  MAKETIME(10,00,00.12345),
+  MAKETIME(10,00,00.123456);
+SHOW COLUMNS FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS SELECT
+  TIME('10:00:00'),
+  TIME('10:00:00.1'),
+  TIME('10:00:00.12'),
+  TIME('10:00:00.123'),
+  TIME('10:00:00.1234'),
+  TIME('10:00:00.12345'),
+  TIME('10:00:00.12346');
+SHOW COLUMNS FROM t1;
+DROP TABLE t1;
+
+SET TIME_ZONE='+00:00';
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17');
+SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17');
+SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456');
+SELECT @@timestamp, FROM_UNIXTIME(@@timestamp);
+SET TIME_ZONE=DEFAULT;
+SET TIMESTAMP=DEFAULT;
+
+SELECT TIME('2012-10-16 15:54:16.12');
+SELECT TIMESTAMP('2012-10-16 15:54:16.12');
+SELECT TIMEDIFF('10:10:10.1','00:00:00');
+SELECT TIME_TO_SEC('10:10:10');
+SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12');
+SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12');
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND);
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND);
+SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00');
diff --git a/sql/item.cc b/sql/item.cc
index 1383500b007..5207d7d9ba3 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -488,6 +488,44 @@ uint Item::decimal_precision() const
 }
 
 
+#if MARIADB_VERSION_ID < 1000000
+static uint ms_to_precision(uint ms)
+{
+  uint cut, precision;
+  for (cut= 10, precision= 6 ; precision > 0 ; cut*= 10, precision--)
+  {
+    if (ms % cut)
+      return precision;
+  }
+  return 0;
+}
+#else
+#error Change the code to use MYSQL_TIME_STATUS::precision instead.
+#endif
+
+
+uint Item::temporal_precision(enum_field_types type)
+{
+  if (const_item() && result_type() == STRING_RESULT &&
+      !is_temporal_type(field_type()))
+  {
+    MYSQL_TIME ltime;
+    String buf, *tmp;
+    int was_cut;
+    DBUG_ASSERT(fixed);
+    if ((tmp= val_str(&buf)) &&
+        (type == MYSQL_TYPE_TIME ?
+         str_to_time(tmp->ptr(), tmp->length(),
+                     &ltime, TIME_TIME_ONLY, &was_cut) :
+         str_to_datetime(tmp->ptr(), tmp->length(),
+                         &ltime, TIME_FUZZY_DATES, &was_cut)) >
+        MYSQL_TIMESTAMP_ERROR)
+      return min(ms_to_precision(ltime.second_part), TIME_SECOND_PART_DIGITS);
+  }
+  return min(decimals, TIME_SECOND_PART_DIGITS);
+}
+
+
 void Item::print_item_w_name(String *str, enum_query_type query_type)
 {
   print(str, query_type);
diff --git a/sql/item.h b/sql/item.h
index 5613fe547c4..13f8e359dbd 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -906,6 +906,10 @@ public:
   virtual uint decimal_precision() const;
   inline int decimal_int_part() const
   { return my_decimal_int_part(decimal_precision(), decimals); }
+  /**
+    TIME or DATETIME precision of the item: 0..6
+  */
+  uint temporal_precision(enum_field_types type);
   /* 
     Returns true if this is constant (during query execution, i.e. its value
     will not change until next fix_fields) and its value is known.
diff --git a/sql/item_func.h b/sql/item_func.h
index 44070354990..4eb0105376a 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1707,6 +1707,8 @@ public:
   double val_real();
   longlong val_int();
   String* val_str(String*);
+  my_decimal *val_decimal(my_decimal *dec_buf)
+  { return val_decimal_from_real(dec_buf); }
   /* TODO: fix to support views */
   const char *func_name() const { return "get_system_var"; }
   /**
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index d89cd01d26a..1f6bff6fa6c 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1201,7 +1201,19 @@ bool get_interval_value(Item *args,interval_type int_type,
   CHARSET_INFO *cs=str_value->charset();
 
   bzero((char*) interval,sizeof(*interval));
-  if ((int) int_type <= INTERVAL_MICROSECOND)
+  if (int_type == INTERVAL_SECOND && args->decimals)
+  {
+    my_decimal decimal_value, *val;
+    ulonglong second;
+    ulong second_part;
+    if (!(val= args->val_decimal(&decimal_value)))
+      return true;
+    interval->neg= my_decimal2seconds(val, &second, &second_part);
+    interval->second= second;
+    interval->second_part= second_part;
+    return false;
+  }
+  else if ((int) int_type <= INTERVAL_MICROSECOND)
   {
     value= args->val_int();
     if (args->null_value)
@@ -1815,7 +1827,7 @@ bool Item_func_from_unixtime::get_date(MYSQL_TIME *ltime,
 
 void Item_func_convert_tz::fix_length_and_dec()
 {
-  decimals= args[0]->decimals;
+  decimals= args[0]->temporal_precision(MYSQL_TYPE_DATETIME);
   Item_temporal_func::fix_length_and_dec();
 }
 
@@ -1890,28 +1902,40 @@ void Item_date_add_interval::fix_length_and_dec()
   */
   cached_field_type= MYSQL_TYPE_STRING;
   arg0_field_type= args[0]->field_type();
+  uint interval_dec= 0;
+  if (int_type == INTERVAL_MICROSECOND ||
+      (int_type >= INTERVAL_DAY_MICROSECOND &&
+       int_type <= INTERVAL_SECOND_MICROSECOND))
+    interval_dec= TIME_SECOND_PART_DIGITS;
+  else if (int_type == INTERVAL_SECOND && args[1]->decimals > 0)
+    interval_dec= min(args[1]->decimals, TIME_SECOND_PART_DIGITS);
+
   if (arg0_field_type == MYSQL_TYPE_DATETIME ||
       arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+  {
+    decimals= max(args[0]->temporal_precision(MYSQL_TYPE_DATETIME), interval_dec);
     cached_field_type= MYSQL_TYPE_DATETIME;
+  }
   else if (arg0_field_type == MYSQL_TYPE_DATE)
   {
     if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
       cached_field_type= arg0_field_type;
     else
+    {
+      decimals= interval_dec;
       cached_field_type= MYSQL_TYPE_DATETIME;
+    }
   }
   else if (arg0_field_type == MYSQL_TYPE_TIME)
   {
+    decimals= max(args[0]->temporal_precision(MYSQL_TYPE_TIME), interval_dec);
     if (int_type >= INTERVAL_DAY && int_type != INTERVAL_YEAR_MONTH)
       cached_field_type= arg0_field_type;
     else
       cached_field_type= MYSQL_TYPE_DATETIME;
   }
-  if (int_type == INTERVAL_MICROSECOND || int_type >= INTERVAL_DAY_MICROSECOND)
-    decimals= 6;
   else
-    decimals= args[0]->decimals;
-
+    decimals= max(args[0]->temporal_precision(MYSQL_TYPE_DATETIME), interval_dec);
   Item_temporal_func::fix_length_and_dec();
   value.alloc(max_length);
 }
@@ -2412,9 +2436,17 @@ void Item_func_add_time::fix_length_and_dec()
   if (arg0_field_type == MYSQL_TYPE_DATE ||
       arg0_field_type == MYSQL_TYPE_DATETIME ||
       arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+  {
     cached_field_type= MYSQL_TYPE_DATETIME;
+    decimals= max(args[0]->temporal_precision(MYSQL_TYPE_DATETIME),
+                  args[1]->temporal_precision(MYSQL_TYPE_TIME));
+  }
   else if (arg0_field_type == MYSQL_TYPE_TIME)
+  {
     cached_field_type= MYSQL_TYPE_TIME;
+    decimals= max(args[0]->temporal_precision(MYSQL_TYPE_TIME),
+                  args[1]->temporal_precision(MYSQL_TYPE_TIME));
+  }
   Item_temporal_func::fix_length_and_dec();
 }
 
@@ -2598,16 +2630,17 @@ bool Item_func_maketime::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
 {
   DBUG_ASSERT(fixed == 1);
   bool overflow= 0;
-
   longlong hour=   args[0]->val_int();
   longlong minute= args[1]->val_int();
-  longlong second= args[2]->val_int();
+  ulonglong second;
+  ulong microsecond;
+  bool neg= args[2]->get_seconds(&second, &microsecond);
 
   if ((null_value=(args[0]->null_value || 
                    args[1]->null_value ||
                    args[2]->null_value ||
                    minute < 0 || minute > 59 ||
-                   second < 0 || second > 59)))
+                   neg || second > 59)))
     return 1;
 
   bzero(ltime, sizeof(*ltime));
@@ -2629,6 +2662,7 @@ bool Item_func_maketime::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
     ltime->hour=   (uint) ((hour < 0 ? -hour : hour));
     ltime->minute= (uint) minute;
     ltime->second= (uint) second;
+    ltime->second_part= microsecond;
   }
   else
   {
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index b0245e6f743..19a9ac12985 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -361,13 +361,15 @@ class Item_func_dayname :public Item_func_weekday
 
 class Item_func_seconds_hybrid: public Item_func_numhybrid
 {
+protected:
+  virtual enum_field_types arg0_expected_type() const = 0;
 public:
   Item_func_seconds_hybrid() :Item_func_numhybrid() {}
   Item_func_seconds_hybrid(Item *a) :Item_func_numhybrid(a) {}
   void fix_num_length_and_dec()
   {
     if (arg_count)
-      decimals= args[0]->decimals;
+      decimals= args[0]->temporal_precision(arg0_expected_type());
     set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
     max_length=17 + (decimals ? decimals + 1 : 0);
     set_persist_maybe_null(1);
@@ -383,6 +385,8 @@ public:
 class Item_func_unix_timestamp :public Item_func_seconds_hybrid
 {
   bool get_timestamp_value(my_time_t *seconds, ulong *second_part);
+protected:
+  enum_field_types arg0_expected_type() const { return MYSQL_TYPE_DATETIME; }
 public:
   Item_func_unix_timestamp() :Item_func_seconds_hybrid() {}
   Item_func_unix_timestamp(Item *a) :Item_func_seconds_hybrid(a) {}
@@ -413,6 +417,8 @@ public:
   
 class Item_func_time_to_sec :public Item_func_seconds_hybrid
 {
+protected:
+  enum_field_types arg0_expected_type() const { return MYSQL_TYPE_TIME; }
 public:
   Item_func_time_to_sec(Item *item) :Item_func_seconds_hybrid(item) {}
   const char *func_name() const { return "time_to_sec"; }
@@ -829,7 +835,7 @@ public:
   void fix_length_and_dec()
   {
     if (decimals == NOT_FIXED_DEC)
-      decimals= args[0]->decimals;
+      decimals= args[0]->temporal_precision(field_type());
     Item_temporal_func::fix_length_and_dec();
   }
 };
@@ -903,7 +909,8 @@ public:
   const char *func_name() const { return "timediff"; }
   void fix_length_and_dec()
   {
-    decimals= max(args[0]->decimals, args[1]->decimals);
+    decimals= max(args[0]->temporal_precision(MYSQL_TYPE_TIME),
+                  args[1]->temporal_precision(MYSQL_TYPE_TIME));
     Item_timefunc::fix_length_and_dec();
   }
   bool get_date(MYSQL_TIME *ltime, uint fuzzy_date);
@@ -915,6 +922,11 @@ public:
   Item_func_maketime(Item *a, Item *b, Item *c)
     :Item_timefunc(a, b, c) 
   {}
+  void fix_length_and_dec()
+  {
+    decimals= min(args[2]->decimals, TIME_SECOND_PART_DIGITS);
+    Item_timefunc::fix_length_and_dec();
+  }
   const char *func_name() const { return "maketime"; }
   bool get_date(MYSQL_TIME *ltime, uint fuzzy_date);
 };