MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmeticbb-11.0-bar-MDEV-15750

    In progress.

16 files changed, 993 insertions, 65 deletions

diff --git a/mysql-test/main/disabled.def b/mysql-test/main/disabled.def
index 02104f6a680..5af531200da 100644
--- a/mysql-test/main/disabled.def
+++ b/mysql-test/main/disabled.def
@@ -18,3 +18,4 @@ file_contents            : MDEV-6526 these files are not installed anymore
 max_statement_time       : cannot possibly work, depends on timing
 partition_open_files_limit : open_files_limit check broken by MDEV-18360
 partition_innodb         : Waiting for fix MDEV-20169
+partition_datatype       : MDEV-15750 in progress
diff --git a/mysql-test/main/timezone2.result b/mysql-test/main/timezone2.result
index 72dbd5ed990..588255df5b9 100644
--- a/mysql-test/main/timezone2.result
+++ b/mysql-test/main/timezone2.result
@@ -676,3 +676,64 @@ SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a >= ALL (SELECT * FROM t1);
 a	UNIX_TIMESTAMP(a)
 2010-10-31 02:25:25	1288481125
 DROP TABLE t1;
+#
+# Start of 11.0 tests
+#
+#
+# MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic
+#
+SET time_zone='Europe/Moscow';
+CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
+INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288477526));
+UPDATE t1 SET b=a+INTERVAL 1 HOUR;
+SELECT
+a,
+b,
+UNIX_TIMESTAMP(a),
+UNIX_TIMESTAMP(b),
+TIMESTAMPDIFF(HOUR,a,b),
+TIMESTAMPDIFF(MINUTE,a,b),
+TIMESTAMPDIFF(SECOND,a,b),
+TIMESTAMPDIFF(MICROSECOND,a,b)
+FROM t1;
+a	2010-10-31 02:25:26
+b	2010-10-31 02:25:26
+UNIX_TIMESTAMP(a)	1288477526
+UNIX_TIMESTAMP(b)	1288481126
+TIMESTAMPDIFF(HOUR,a,b)	1
+TIMESTAMPDIFF(MINUTE,a,b)	60
+TIMESTAMPDIFF(SECOND,a,b)	3600
+TIMESTAMPDIFF(MICROSECOND,a,b)	3600000000
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526));
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UNIX_TIMESTAMP(a)	a
+1288477526	2010-10-31 02:25:26
+UPDATE t1 SET a=a + INTERVAL 1 HOUR;
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UNIX_TIMESTAMP(a)	a
+1288481126	2010-10-31 02:25:26
+UPDATE t1 SET a=a - INTERVAL 1 HOUR;
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UNIX_TIMESTAMP(a)	a
+1288477526	2010-10-31 02:25:26
+DROP TABLE t1;
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526));
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UNIX_TIMESTAMP(a)	a
+1288477526	2010-10-31 02:25:26
+UPDATE t1 SET a=ADDTIME(a,'01:00:00');
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UNIX_TIMESTAMP(a)	a
+1288481126	2010-10-31 02:25:26
+UPDATE t1 SET a=SUBTIME(a,'01:00:00');
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UNIX_TIMESTAMP(a)	a
+1288477526	2010-10-31 02:25:26
+DROP TABLE t1;
+SET time_zone=DEFAULT;
+#
+# End of 11.0 tests
+#
diff --git a/mysql-test/main/timezone2.test b/mysql-test/main/timezone2.test
index b5045203903..cbd1ef2230e 100644
--- a/mysql-test/main/timezone2.test
+++ b/mysql-test/main/timezone2.test
@@ -630,3 +630,53 @@ SELECT a, UNIX_TIMESTAMP(a) FROM t1 ORDER BY a;
 SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a <= ALL (SELECT * FROM t1);
 SELECT a, UNIX_TIMESTAMP(a) FROM t1 WHERE a >= ALL (SELECT * FROM t1);
 DROP TABLE t1;
+
+--echo #
+--echo # Start of 11.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic
+--echo #
+
+SET time_zone='Europe/Moscow';
+
+CREATE TABLE t1 (a TIMESTAMP, b TIMESTAMP);
+INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526),FROM_UNIXTIME(1288477526));
+UPDATE t1 SET b=a+INTERVAL 1 HOUR;
+query_vertical
+SELECT
+  a,
+  b,
+  UNIX_TIMESTAMP(a),
+  UNIX_TIMESTAMP(b),
+  TIMESTAMPDIFF(HOUR,a,b),
+  TIMESTAMPDIFF(MINUTE,a,b),
+  TIMESTAMPDIFF(SECOND,a,b),
+  TIMESTAMPDIFF(MICROSECOND,a,b)
+FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526));
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UPDATE t1 SET a=a + INTERVAL 1 HOUR;
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UPDATE t1 SET a=a - INTERVAL 1 HOUR;
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (FROM_UNIXTIME(1288477526));
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UPDATE t1 SET a=ADDTIME(a,'01:00:00');
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+UPDATE t1 SET a=SUBTIME(a,'01:00:00');
+SELECT UNIX_TIMESTAMP(a), a FROM t1;
+DROP TABLE t1;
+
+SET time_zone=DEFAULT;
+
+--echo #
+--echo # End of 11.0 tests
+--echo #
diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result
index d69ebf736d2..cc5cb4315c3 100644
--- a/mysql-test/main/type_timestamp.result
+++ b/mysql-test/main/type_timestamp.result
@@ -769,7 +769,7 @@ SELECT DATE_ADD(a, INTERVAL 10 SECOND) FROM t1;
 DATE_ADD(a, INTERVAL 10 SECOND)
 NULL
 Warnings:
-Warning	1292	Incorrect datetime value: '0000-00-00 00:00:00'
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
 DROP TABLE t1;
 #
 # MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value
@@ -1348,3 +1348,210 @@ drop table t1;
 #
 # End of 10.10 tests
 #
+#
+# Start of 11.0 tests
+#
+#
+# MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic
+#
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+SELECT a, TIMESTAMPDIFF(SECOND,a,a) FROM t1;
+a	TIMESTAMPDIFF(SECOND,a,a)
+0000-00-00 00:00:00	NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT a, TIMESTAMPADD(SECOND,0,a) FROM t1;
+a	TIMESTAMPADD(SECOND,0,a)
+0000-00-00 00:00:00	NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT a, DATE_ADD(a, INTERVAL 0 SECOND) FROM t1;
+a	DATE_ADD(a, INTERVAL 0 SECOND)
+0000-00-00 00:00:00	NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT a, DATE_SUB(a, INTERVAL 0 SECOND) FROM t1;
+a	DATE_SUB(a, INTERVAL 0 SECOND)
+0000-00-00 00:00:00	NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT a + INTERVAL 0 SECOND FROM t1;
+a + INTERVAL 0 SECOND
+NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT a - INTERVAL 0 SECOND FROM t1;
+a - INTERVAL 0 SECOND
+NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT ADDTIME(a, '00:00:00') FROM t1;
+ADDTIME(a, '00:00:00')
+NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+SELECT SUBTIME(a, '00:00:00') FROM t1;
+SUBTIME(a, '00:00:00')
+NULL
+Warnings:
+Warning	1525	Incorrect timestamp value: '0000-00-00 00:00:00'
+DROP TABLE t1;
+SET @@time_zone='+00:00';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (from_unixtime(1));
+INSERT INTO t1 VALUES (from_unixtime(2));
+SELECT * FROM t1 ORDER BY a;
+a
+1970-01-01 00:00:01
+1970-01-01 00:00:02
+SELECT a, TIMESTAMPDIFF(SECOND,a,a) FROM t1 ORDER BY a;
+a	TIMESTAMPDIFF(SECOND,a,a)
+1970-01-01 00:00:01	0
+1970-01-01 00:00:02	0
+SELECT a, TIMESTAMPADD(SECOND,-1,a) FROM t1 ORDER BY a;
+a	TIMESTAMPADD(SECOND,-1,a)
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, DATE_ADD(a, INTERVAL -1 SECOND) FROM t1 ORDER BY a;
+a	DATE_ADD(a, INTERVAL -1 SECOND)
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, DATE_SUB(a, INTERVAL 1 SECOND) FROM t1 ORDER BY a;
+a	DATE_SUB(a, INTERVAL 1 SECOND)
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, a + INTERVAL -1 SECOND FROM t1 ORDER BY a;
+a	a + INTERVAL -1 SECOND
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, a - INTERVAL 1 SECOND FROM t1 ORDER BY a;
+a	a - INTERVAL 1 SECOND
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, ADDTIME(a, '-00:00:01') FROM t1 ORDER BY a;
+a	ADDTIME(a, '-00:00:01')
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, SUBTIME(a, '00:00:01') FROM t1 ORDER BY a;
+a	SUBTIME(a, '00:00:01')
+1970-01-01 00:00:01	NULL
+1970-01-01 00:00:02	1970-01-01 00:00:01
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+DROP TABLE t1;
+SET @@time_zone=DEFAULT;
+SET @@time_zone='+00:00';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFE));
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFF));
+SELECT * FROM t1 ORDER BY a;
+a
+2038-01-19 03:14:06
+2038-01-19 03:14:07
+SELECT a, TIMESTAMPDIFF(SECOND,a,a) FROM t1 ORDER BY a;
+a	TIMESTAMPDIFF(SECOND,a,a)
+2038-01-19 03:14:06	0
+2038-01-19 03:14:07	0
+SELECT a, TIMESTAMPADD(SECOND,1,a) FROM t1 ORDER BY a;
+a	TIMESTAMPADD(SECOND,1,a)
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, DATE_ADD(a, INTERVAL 1 SECOND) FROM t1 ORDER BY a;
+a	DATE_ADD(a, INTERVAL 1 SECOND)
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, DATE_SUB(a, INTERVAL -1 SECOND) FROM t1 ORDER BY a;
+a	DATE_SUB(a, INTERVAL -1 SECOND)
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, a + INTERVAL 1 SECOND FROM t1 ORDER BY a;
+a	a + INTERVAL 1 SECOND
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, a - INTERVAL -1 SECOND FROM t1 ORDER BY a;
+a	a - INTERVAL -1 SECOND
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, ADDTIME(a, '00:00:01') FROM t1 ORDER BY a;
+a	ADDTIME(a, '00:00:01')
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+SELECT a, SUBTIME(a, '-00:00:01') FROM t1 ORDER BY a;
+a	SUBTIME(a, '-00:00:01')
+2038-01-19 03:14:06	2038-01-19 03:14:07
+2038-01-19 03:14:07	NULL
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+DROP TABLE t1;
+SET @@time_zone=DEFAULT;
+SET @@time_zone='+00:00';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFE));
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFF));
+SELECT * FROM t1 ORDER BY a;
+a
+2038-01-19 03:14:06
+2038-01-19 03:14:07
+CREATE TABLE t2 AS SELECT
+a,
+TIMESTAMPDIFF(SECOND,a,a),
+TIMESTAMPADD(SECOND,1,a),
+DATE_ADD(a, INTERVAL 1 SECOND),
+DATE_SUB(a, INTERVAL -1 SECOND),
+a + INTERVAL 1 SECOND,
+a - INTERVAL -1 SECOND,
+ADDTIME(a, '00:00:01'),
+SUBTIME(a, '-00:00:01')
+FROM t1;
+Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` timestamp NULL DEFAULT NULL,
+  `TIMESTAMPDIFF(SECOND,a,a)` bigint(21) DEFAULT NULL,
+  `TIMESTAMPADD(SECOND,1,a)` timestamp NULL DEFAULT NULL,
+  `DATE_ADD(a, INTERVAL 1 SECOND)` timestamp NULL DEFAULT NULL,
+  `DATE_SUB(a, INTERVAL -1 SECOND)` timestamp NULL DEFAULT NULL,
+  `a + INTERVAL 1 SECOND` timestamp NULL DEFAULT NULL,
+  `a - INTERVAL -1 SECOND` timestamp NULL DEFAULT NULL,
+  `ADDTIME(a, '00:00:01')` timestamp NULL DEFAULT NULL,
+  `SUBTIME(a, '-00:00:01')` timestamp NULL DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t2;
+DROP TABLE t1;
+SET @@time_zone=DEFAULT;
+#
+# End of 11.0 tests
+#
diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test
index b5b1fb02f40..7f9cd2f40be 100644
--- a/mysql-test/main/type_timestamp.test
+++ b/mysql-test/main/type_timestamp.test
@@ -897,3 +897,81 @@ drop table t1;
 --echo #
 --echo # End of 10.10 tests
 --echo #
+
+
+--echo #
+--echo # Start of 11.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-15750 preserve MYSQL_TYPE_TIMESTAMP in temporal arithmetic
+--echo #
+
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES ('0000-00-00 00:00:00');
+SELECT a, TIMESTAMPDIFF(SECOND,a,a) FROM t1;
+SELECT a, TIMESTAMPADD(SECOND,0,a) FROM t1;
+SELECT a, DATE_ADD(a, INTERVAL 0 SECOND) FROM t1;
+SELECT a, DATE_SUB(a, INTERVAL 0 SECOND) FROM t1;
+SELECT a + INTERVAL 0 SECOND FROM t1;
+SELECT a - INTERVAL 0 SECOND FROM t1;
+SELECT ADDTIME(a, '00:00:00') FROM t1;
+SELECT SUBTIME(a, '00:00:00') FROM t1;
+DROP TABLE t1;
+
+SET @@time_zone='+00:00';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (from_unixtime(1));
+INSERT INTO t1 VALUES (from_unixtime(2));
+SELECT * FROM t1 ORDER BY a;
+SELECT a, TIMESTAMPDIFF(SECOND,a,a) FROM t1 ORDER BY a;
+SELECT a, TIMESTAMPADD(SECOND,-1,a) FROM t1 ORDER BY a;
+SELECT a, DATE_ADD(a, INTERVAL -1 SECOND) FROM t1 ORDER BY a;
+SELECT a, DATE_SUB(a, INTERVAL 1 SECOND) FROM t1 ORDER BY a;
+SELECT a, a + INTERVAL -1 SECOND FROM t1 ORDER BY a;
+SELECT a, a - INTERVAL 1 SECOND FROM t1 ORDER BY a;
+SELECT a, ADDTIME(a, '-00:00:01') FROM t1 ORDER BY a;
+SELECT a, SUBTIME(a, '00:00:01') FROM t1 ORDER BY a;
+DROP TABLE t1;
+SET @@time_zone=DEFAULT;
+
+SET @@time_zone='+00:00';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFE));
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFF));
+SELECT * FROM t1 ORDER BY a;
+SELECT a, TIMESTAMPDIFF(SECOND,a,a) FROM t1 ORDER BY a;
+SELECT a, TIMESTAMPADD(SECOND,1,a) FROM t1 ORDER BY a;
+SELECT a, DATE_ADD(a, INTERVAL 1 SECOND) FROM t1 ORDER BY a;
+SELECT a, DATE_SUB(a, INTERVAL -1 SECOND) FROM t1 ORDER BY a;
+SELECT a, a + INTERVAL 1 SECOND FROM t1 ORDER BY a;
+SELECT a, a - INTERVAL -1 SECOND FROM t1 ORDER BY a;
+SELECT a, ADDTIME(a, '00:00:01') FROM t1 ORDER BY a;
+SELECT a, SUBTIME(a, '-00:00:01') FROM t1 ORDER BY a;
+DROP TABLE t1;
+SET @@time_zone=DEFAULT;
+
+SET @@time_zone='+00:00';
+CREATE TABLE t1 (a TIMESTAMP);
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFE));
+INSERT INTO t1 VALUES (from_unixtime(0x7FFFFFFF));
+SELECT * FROM t1 ORDER BY a;
+CREATE TABLE t2 AS SELECT
+  a,
+  TIMESTAMPDIFF(SECOND,a,a),
+  TIMESTAMPADD(SECOND,1,a),
+  DATE_ADD(a, INTERVAL 1 SECOND),
+  DATE_SUB(a, INTERVAL -1 SECOND),
+  a + INTERVAL 1 SECOND,
+  a - INTERVAL -1 SECOND,
+  ADDTIME(a, '00:00:01'),
+  SUBTIME(a, '-00:00:01')
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+SET @@time_zone=DEFAULT;
+
+--echo #
+--echo # End of 11.0 tests
+--echo #
diff --git a/mysql-test/main/type_timestamp_hires.result b/mysql-test/main/type_timestamp_hires.result
index ac2d4392bee..f98b095c1fe 100644
--- a/mysql-test/main/type_timestamp_hires.result
+++ b/mysql-test/main/type_timestamp_hires.result
@@ -1,3 +1,4 @@
+SET @@time_zone='+00:00';
 SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30');
 drop table if exists t1, t2, t3;
 create table t1 (a timestamp(7));
diff --git a/mysql-test/main/type_timestamp_hires.test b/mysql-test/main/type_timestamp_hires.test
index 27aed16d6be..7b64470a4c2 100644
--- a/mysql-test/main/type_timestamp_hires.test
+++ b/mysql-test/main/type_timestamp_hires.test
@@ -1,4 +1,9 @@
 
+# type_hrtime.inc uses expressions like TEMPORAL_TYPE+INTERVAL
+# In case of TIMESTAMP+INTERVAL, results depend on the current @@time_zone.
+# Make results deterministic.
+SET @@time_zone='+00:00';
+
 let type=timestamp;
 --source include/type_hrtime.inc
 
diff --git a/mysql-test/suite/compat/oracle/r/func_add_months.result b/mysql-test/suite/compat/oracle/r/func_add_months.result
index 0502c20f74e..660e7bd8993 100644
--- a/mysql-test/suite/compat/oracle/r/func_add_months.result
+++ b/mysql-test/suite/compat/oracle/r/func_add_months.result
@@ -16,18 +16,25 @@ c1	ADD_MONTHS(c2, 2)	ADD_MONTHS(c3, 2)	ADD_MONTHS(c5, 2)
 5	2031-11-02 08:15:22	2031-11-02	2031-11-02 08:15:22
 6	0000-11-02 00:00:00	0000-11-02	1980-11-02 00:00:00
 7	9999-11-02 00:00:00	9999-11-02	1980-11-02 00:00:00
-SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1;
-c1	ADD_MONTHS(c2, 15)	ADD_MONTHS(c3, 200)	ADD_MONTHS(c5, 2000)
-1	2013-02-12 12:10:11	2028-07-12	2178-07-12 12:10:11
-2	2023-02-12 00:23:12	2038-07-12	2188-07-12 00:23:12
-3	2012-04-22 16:45:45	2027-09-22	2177-09-22 16:45:45
-4	2032-08-12 04:11:34	2048-01-12	2198-01-12 04:11:34
-5	2032-12-02 08:15:22	2048-05-02	2198-05-02 08:15:22
-6	0001-12-02 00:00:00	0017-05-02	2147-05-02 00:00:00
-7	NULL	NULL	2147-05-02 00:00:00
+SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(CAST(c5 AS DATETIME),2000), ADD_MONTHS(c5, 2000) FROM t1;
+c1	ADD_MONTHS(c2, 15)	ADD_MONTHS(c3, 200)	ADD_MONTHS(CAST(c5 AS DATETIME),2000)	ADD_MONTHS(c5, 2000)
+1	2013-02-12 12:10:11	2028-07-12	2178-07-12 12:10:11	NULL
+2	2023-02-12 00:23:12	2038-07-12	2188-07-12 00:23:12	NULL
+3	2012-04-22 16:45:45	2027-09-22	2177-09-22 16:45:45	NULL
+4	2032-08-12 04:11:34	2048-01-12	2198-01-12 04:11:34	NULL
+5	2032-12-02 08:15:22	2048-05-02	2198-05-02 08:15:22	NULL
+6	0001-12-02 00:00:00	0017-05-02	2147-05-02 00:00:00	NULL
+7	NULL	NULL	2147-05-02 00:00:00	NULL
 Warnings:
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
+Warning	1441	Datetime function: timestamp field overflow
 Warning	1441	Datetime function: datetime field overflow
 Warning	1441	Datetime function: datetime field overflow
+Warning	1441	Datetime function: timestamp field overflow
 SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1;
 c1	ADD_MONTHS(c2, 0)	ADD_MONTHS(c3, -200)	ADD_MONTHS(c5, -2)
 1	2011-11-12 12:10:11	1995-03-12	2011-09-12 12:10:11
diff --git a/mysql-test/suite/compat/oracle/t/func_add_months.test b/mysql-test/suite/compat/oracle/t/func_add_months.test
index ca9391ef824..c91010ef38c 100644
--- a/mysql-test/suite/compat/oracle/t/func_add_months.test
+++ b/mysql-test/suite/compat/oracle/t/func_add_months.test
@@ -12,7 +12,7 @@ INSERT INTO t1 VALUES (7, '9999-09-02', '9999-09-02', '00:00:00', '1980-09-02');
 
 # some normal case
 SELECT c1, ADD_MONTHS(c2, 2), ADD_MONTHS(c3, 2), ADD_MONTHS(c5, 2) FROM t1;
-SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(c5, 2000) FROM t1;
+SELECT c1, ADD_MONTHS(c2, 15), ADD_MONTHS(c3, 200), ADD_MONTHS(CAST(c5 AS DATETIME),2000), ADD_MONTHS(c5, 2000) FROM t1;
 SELECT c1, ADD_MONTHS(c2, 0), ADD_MONTHS(c3, -200), ADD_MONTHS(c5, -2) FROM t1;
 SELECT c1, ADD_MONTHS(c2, -15), ADD_MONTHS(c3, -111), ADD_MONTHS(c5, 2) FROM t1;
 
diff --git a/sql/item_func.h b/sql/item_func.h
index 520dbdc90c7..22ffda60111 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -661,6 +661,52 @@ public:
   };
 
 
+  class Handler_timestamp: public Handler_temporal
+  {
+  protected:
+    Datetime to_datetime(THD *thd, Item_handled_func *item) const
+    {
+      Timestamp_or_zero_datetime_native_null ts(thd, item);
+      return Timestamp_or_zero_datetime(ts).to_datetime(thd);
+    }
+  public:
+    const Type_handler *return_type_handler(const Item_handled_func *) const
+    {
+      return &type_handler_timestamp2;
+    }
+    bool get_date(THD *thd, Item_handled_func *item,
+                  MYSQL_TIME *ltime, date_mode_t fuzzydate) const
+    {
+      // QQ: check fuzzydate???
+      return item->null_value= to_datetime(thd, item).copy_to_mysql_time(ltime);
+    }
+    double val_real(Item_handled_func *item) const
+    {
+      Datetime dt= to_datetime(current_thd, item);
+      item->null_value= !dt.is_valid_datetime();
+      return dt.to_double();
+    }
+    longlong val_int(Item_handled_func *item) const
+    {
+      Datetime dt= to_datetime(current_thd, item);
+      item->null_value= !dt.is_valid_datetime();
+      return dt.to_longlong();
+    }
+    my_decimal *val_decimal(Item_handled_func *item, my_decimal *to) const
+    {
+      Datetime dt= to_datetime(current_thd, item);
+      item->null_value= !dt.is_valid_datetime();
+      return dt.to_decimal(to);
+    }
+    String *val_str_ascii(Item_handled_func *item, String *to) const
+    {
+      Datetime dt= to_datetime(current_thd, item);
+      item->null_value= !dt.is_valid_datetime();
+      return dt.to_string(to, item->decimals);
+    }
+  };
+
+
   class Handler_int: public Handler
   {
   public:
@@ -726,6 +772,22 @@ public:
     }
   };
 
+  class Handler_slonglong: public Handler_int
+  {
+  public:
+    const Type_handler *return_type_handler(const Item_handled_func *item) const
+    {
+      return &type_handler_slonglong;
+    }
+    bool fix_length_and_dec(Item_handled_func *item) const
+    {
+      item->unsigned_flag= false;
+      item->collation= DTCollation_numeric();
+      item->fix_char_length(21);
+      return false;
+    }
+  };
+
   class Handler_ulonglong: public Handler_int
   {
   public:
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 52b9ae7a682..9f0cbba472e 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -56,17 +56,24 @@
 #include <m_ctype.h>
 #include <time.h>
 
-/** Day number for Dec 31st, 9999. */
-#define MAX_DAY_NUMBER 3652424L
 
 Func_handler_date_add_interval_datetime_arg0_time
   func_handler_date_add_interval_datetime_arg0_time;
 
 Func_handler_date_add_interval_datetime func_handler_date_add_interval_datetime;
+
+Func_handler_date_add_interval_timestamp_is_timestamp_plus_YYYYMM
+  func_handler_date_add_interval_timestamp_is_timestamp_plus_YYYYMM;
+
+Func_handler_date_add_interval_timestamp_is_timestamp_plus_DDhhmmss
+  func_handler_date_add_interval_timestamp_is_timestamp_plus_DDhhmmss;
+
 Func_handler_date_add_interval_date     func_handler_date_add_interval_date;
 Func_handler_date_add_interval_time     func_handler_date_add_interval_time;
 Func_handler_date_add_interval_string   func_handler_date_add_interval_string;
 
+Func_handler_add_time_timestamp func_handler_add_time_timestamp_add(1);
+Func_handler_add_time_timestamp func_handler_add_time_timestamp_sub(-1);
 Func_handler_add_time_datetime func_handler_add_time_datetime_add(1);
 Func_handler_add_time_datetime func_handler_add_time_datetime_sub(-1);
 Func_handler_add_time_time     func_handler_add_time_time_add(1);
@@ -2848,11 +2855,25 @@ bool Item_date_add_interval::fix_length_and_dec(THD *thd)
   */
   arg0_field_type= args[0]->field_type();
 
-  if (arg0_field_type == MYSQL_TYPE_DATETIME ||
-      arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+  if (arg0_field_type == MYSQL_TYPE_DATETIME)
   {
     set_func_handler(&func_handler_date_add_interval_datetime);
   }
+  else if (arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+  {
+    switch (int_type) {
+    case INTERVAL_YEAR:
+    case INTERVAL_QUARTER:
+    case INTERVAL_MONTH:
+    case INTERVAL_YEAR_MONTH:
+      set_func_handler(
+        &func_handler_date_add_interval_timestamp_is_timestamp_plus_YYYYMM);
+      break;
+    default:
+      set_func_handler(
+        &func_handler_date_add_interval_timestamp_is_timestamp_plus_DDhhmmss);
+    }
+  }
   else if (arg0_field_type == MYSQL_TYPE_DATE)
   {
     if (int_type <= INTERVAL_DAY || int_type == INTERVAL_YEAR_MONTH)
@@ -2876,6 +2897,72 @@ bool Item_date_add_interval::fix_length_and_dec(THD *thd)
 }
 
 
+bool Func_handler_date_add_interval_timestamp_is_timestamp_plus_YYYYMM
+      ::val_native(THD *thd, Item_handled_func *func, Native *to) const
+{
+  Timestamp_null ts(thd, func->arguments()[0], false);
+  if ((func->null_value= ts.is_null()))
+    return true;
+  INTERVAL interval;
+  if (get_interval_value(thd, func->arguments()[1], int_type(func), &interval))
+    return func->null_value= true;
+
+  if (sub(func))
+    interval.neg = !interval.neg;
+
+  return func->null_value= ts.add(thd, int_type(func), interval) ||
+                           ts.check_zero_timestamp_result_with_warn(thd) ||
+                           ts.to_native(to, func->decimals);
+}
+
+
+bool Func_handler_date_add_interval_timestamp_is_timestamp_plus_DDhhmmss
+       ::val_native(THD *thd, Item_handled_func *func, Native *to) const
+{
+  Timestamp_null ts(thd, func->arguments()[0], false);
+  if ((func->null_value= ts.is_null()))
+    return true;
+  INTERVAL interval;
+  if (get_interval_value(thd, func->arguments()[1], int_type(func), &interval))
+    return func->null_value= true;
+
+  // Avoid overflow in INTERVAL::unsigned_DDhhmmssff_to_seconds()
+  if (interval.DDhhmmssff_to_days_abs() > Date::MAX_DAY_NUMBER())
+  {
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                        ER_DATETIME_FUNCTION_OVERFLOW,
+                        ER_THD(thd, ER_DATETIME_FUNCTION_OVERFLOW),
+                        "interval");
+    return func->null_value= true;
+  }
+
+  Sec6 sec6(interval.neg, interval.DDhhmmssff_to_integer_seconds_abs(),
+                          interval.second_part % 1000000);
+
+  return func->null_value=
+           (sub(func) ? ts.sub(thd, sec6) : ts.add(thd, sec6)) ||
+            ts.check_zero_timestamp_result_with_warn(thd) ||
+            ts.to_native(to, func->decimals);
+}
+
+
+bool Func_handler_add_time_timestamp
+       ::val_native(THD *thd, Item_handled_func *func, Native *to) const
+{
+  Timestamp_null ts(thd, func->arguments()[0], false);
+  if (ts.is_null())
+    return func->null_value= true;
+  Interval_DDhhmmssff it(thd, func->arguments()[1]);
+  if (!it.is_valid_interval_DDhhmmssff())
+    return (func->null_value= true);
+  return func->null_value=
+           (m_sign < 0 ? ts.sub(thd, it.to_sec6()) :
+                         ts.add(thd, it.to_sec6())) ||
+           ts.check_zero_timestamp_result_with_warn(thd) ||
+           ts.to_native(to, func->decimals);
+}
+
+
 bool Func_handler_date_add_interval_datetime_arg0_time::
        get_date(THD *thd, Item_handled_func *item,
                 MYSQL_TIME *to, date_mode_t fuzzy) const
@@ -3467,12 +3554,16 @@ bool Item_func_add_time::fix_length_and_dec(THD *thd)
 
   arg0_field_type= args[0]->field_type();
   if (arg0_field_type == MYSQL_TYPE_DATE ||
-      arg0_field_type == MYSQL_TYPE_DATETIME ||
-      arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+      arg0_field_type == MYSQL_TYPE_DATETIME)
   {
     set_func_handler(sign > 0 ? &func_handler_add_time_datetime_add :
                                 &func_handler_add_time_datetime_sub);
   }
+  else if (arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+  {
+    set_func_handler(sign > 0 ? &func_handler_add_time_timestamp_add :
+                                &func_handler_add_time_timestamp_sub);
+  }
   else if (arg0_field_type == MYSQL_TYPE_TIME)
   {
     set_func_handler(sign > 0 ? &func_handler_add_time_time_add :
@@ -3580,12 +3671,71 @@ longlong Item_func_microsecond::val_int()
 }
 
 
-longlong Item_func_timestamp_diff::val_int()
+/*
+  Generic TIMESTAMPDIFF implementation:
+    TIMESTAMPDIFF(int_type, datetime, datetime)
+    TIMESTAMPDIFF(int_type, datetime, timestamp)
+    TIMESTAMPDIFF(int_type, timestamp, datetime)
+
+  Special case:
+    TIMESTAMPDIFF(special_timestamp2_int_type, timestamp, timestamp)
+    special_timestamp2_int_type := YEAR | QUARTER | MONTH
+
+  All non-datetime arguments are converted to datetime before calculating.
+  DST changes for timestamp arguments are not taken into account.
+*/
+class Func_handler_datetime_diff: public Item_handled_func::Handler_slonglong
+{
+public:
+  bool fix_length_and_dec(Item_handled_func *func) const override
+  {
+     func->set_maybe_null();
+     return Handler_slonglong::fix_length_and_dec(func);
+  }
+  Longlong_null to_longlong_null(Item_handled_func *func) const override
+  {
+    return static_cast<Item_func_timestamp_diff*>(func)->datetime_diff();
+  }
+};
+
+
+/*
+  Fast and DST-change aware TIMESTAMPDIFF implementation for two
+  TIMESTAMP arguments in combination with the interval type which
+  does not need year or month for calculations:
+
+    TIMESTAMPDIFF(int_type, timestamp, timestamp)
+    int_type := WEEK | DAY | HOUR | MINUTE | SECOND | MICROSECOND
+*/
+class Func_handler_timestamp_diff: public Item_handled_func::Handler_slonglong
+{
+public:
+  bool fix_length_and_dec(Item_handled_func *func) const override
+  {
+     func->set_maybe_null();
+     return Handler_slonglong::fix_length_and_dec(func);
+  }
+  Longlong_null to_longlong_null(Item_handled_func *func) const override
+  {
+    THD *thd= current_thd;
+    Timestamp_null expr1(thd, func->arguments()[0], false);
+    if (expr1.is_null())
+      return Longlong_null();
+    Timestamp_null expr2(thd, func->arguments()[1], false);
+    if (expr2.is_null())
+      return Longlong_null();
+    interval_type it_type= static_cast<Item_func_timestamp_diff*>(func)->
+                              get_int_type();
+    return Longlong_null(expr2.diff(expr1).DDhhmmssff_to_integer_units(it_type));
+  }
+};
+
+
+Longlong_null Item_func_timestamp_diff::datetime_diff()
 {
   MYSQL_TIME ltime1, ltime2;
   ulonglong seconds;
   ulong microseconds;
-  long months= 0;
   int neg= 1;
   THD *thd= current_thd;
   Datetime::Options opt(TIME_NO_ZEROS, thd);
@@ -3594,7 +3744,7 @@ longlong Item_func_timestamp_diff::val_int()
 
   if (Datetime(thd, args[0], opt).copy_to_mysql_time(&ltime1) ||
       Datetime(thd, args[1], opt).copy_to_mysql_time(&ltime2))
-    goto null_date;
+    return Longlong_null();
 
   if (calc_time_diff(&ltime2,&ltime1, 1,
 		     &seconds, &microseconds))
@@ -3606,6 +3756,7 @@ longlong Item_func_timestamp_diff::val_int()
   {
     uint year_beg, year_end, month_beg, month_end, day_beg, day_end;
     uint years= 0;
+    long months= 0;
     uint second_beg, second_end, microsecond_beg, microsecond_end;
 
     if (neg == -1)
@@ -3653,38 +3804,48 @@ longlong Item_func_timestamp_diff::val_int()
 	     ((second_end < second_beg) ||
 	      (second_end == second_beg && microsecond_end < microsecond_beg)))
       months-= 1;
+    switch (int_type) {
+    case INTERVAL_YEAR:
+      return Longlong_null(months / 12 * neg);
+    case INTERVAL_QUARTER:
+      return Longlong_null(months / 3 * neg);
+    case INTERVAL_MONTH:
+      return Longlong_null(months * neg);
+    default:
+      break;
+    }
+    DBUG_ASSERT(0);
+    return Longlong_null();
   }
 
-  switch (int_type) {
-  case INTERVAL_YEAR:
-    return months/12*neg;
-  case INTERVAL_QUARTER:
-    return months/3*neg;
-  case INTERVAL_MONTH:
-    return months*neg;
-  case INTERVAL_WEEK:          
-    return ((longlong) (seconds / SECONDS_IN_24H / 7L)) * neg;
-  case INTERVAL_DAY:		
-    return ((longlong) (seconds / SECONDS_IN_24H)) * neg;
-  case INTERVAL_HOUR:		
-    return ((longlong) (seconds / 3600L)) * neg;
-  case INTERVAL_MINUTE:		
-    return ((longlong) (seconds / 60L)) * neg;
-  case INTERVAL_SECOND:		
-    return ((longlong) seconds) * neg;
-  case INTERVAL_MICROSECOND:
-    /*
-      In MySQL difference between any two valid datetime values
-      in microseconds fits into longlong.
-    */
-    return ((longlong) ((ulonglong) seconds * 1000000L + microseconds)) * neg;
-  default:
-    break;
-  }
+  return Longlong_null(Sec6(neg < 0, seconds, microseconds).
+                         DDhhmmssff_to_integer_units(int_type));
+}
 
-null_date:
-  null_value=1;
-  return 0;
+
+bool Item_func_timestamp_diff::fix_length_and_dec(THD *thd)
+{
+  static Func_handler_datetime_diff fh_datetime_diff;
+  static Func_handler_timestamp_diff fh_timestamp_diff;
+  if (args[0]->field_type() == MYSQL_TYPE_TIMESTAMP &&
+      args[1]->field_type() == MYSQL_TYPE_TIMESTAMP)
+  {
+    switch (int_type) {
+    case INTERVAL_WEEK:
+    case INTERVAL_DAY:
+    case INTERVAL_HOUR:
+    case INTERVAL_MINUTE:
+    case INTERVAL_SECOND:
+    case INTERVAL_MICROSECOND:
+      set_func_handler(&fh_timestamp_diff);
+      break;
+    default:
+      set_func_handler(&fh_datetime_diff);
+    }
+  }
+  else
+    set_func_handler(&fh_datetime_diff);
+  return m_func_handler->fix_length_and_dec(this);
 }
 
 
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index a5f6d9307c6..7b5c1ae58e8 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -1567,7 +1567,7 @@ public:
 };
 
 
-class Item_func_timestamp_diff :public Item_longlong_func
+class Item_func_timestamp_diff :public Item_handled_func
 {
   bool check_arguments() const override
   { return check_argument_types_can_return_date(0, arg_count); }
@@ -1577,19 +1577,14 @@ public:
   interval_type get_int_type() const { return int_type; };
 public:
   Item_func_timestamp_diff(THD *thd, Item *a, Item *b, interval_type type_arg):
-    Item_longlong_func(thd, a, b), int_type(type_arg) {}
+    Item_handled_func(thd, a, b), int_type(type_arg) {}
   LEX_CSTRING func_name_cstring() const override
   {
     static LEX_CSTRING name= {STRING_WITH_LEN("timestampdiff") };
     return name;
   }
-  longlong val_int() override;
-  bool fix_length_and_dec(THD *thd) override
-  {
-    decimals=0;
-    set_maybe_null();
-    return FALSE;
-  }
+  Longlong_null datetime_diff();
+  bool fix_length_and_dec(THD *thd) override;
   void print(String *str, enum_query_type query_type) override;
   Item *get_copy(THD *thd) override
   { return get_item_copy<Item_func_timestamp_diff>(thd, this); }
@@ -1730,6 +1725,37 @@ public:
 };
 
 
+class Func_handler_date_add_interval_timestamp:
+        public Item_handled_func::Handler_timestamp,
+        public Func_handler_date_add_interval
+{
+public:
+  bool fix_length_and_dec(Item_handled_func *item) const override
+  {
+    uint dec= MY_MAX(item->arguments()[0]->datetime_precision(current_thd),
+                     interval_dec(item->arguments()[1], int_type(item)));
+    item->fix_attributes_datetime(dec);
+    return false;
+  }
+};
+
+
+class Func_handler_date_add_interval_timestamp_is_timestamp_plus_YYYYMM:
+        public Func_handler_date_add_interval_timestamp
+{
+public:
+  bool val_native(THD *thd, Item_handled_func *func, Native *to) const override;
+};
+
+
+class Func_handler_date_add_interval_timestamp_is_timestamp_plus_DDhhmmss:
+        public Func_handler_date_add_interval_timestamp
+{
+public:
+  bool val_native(THD *thd, Item_handled_func *func, Native *to) const override;
+};
+
+
 class Func_handler_date_add_interval_datetime_arg0_time:
         public Func_handler_date_add_interval_datetime
 {
@@ -1859,6 +1885,26 @@ public:
 };
 
 
+class Func_handler_add_time_timestamp:
+        public Item_handled_func::Handler_timestamp,
+        public Func_handler_sign
+{
+public:
+  Func_handler_add_time_timestamp(int sign)
+   :Func_handler_sign(sign)
+  { }
+  bool fix_length_and_dec(Item_handled_func *item) const
+  {
+    THD *thd= current_thd;
+    uint dec0= item->arguments()[0]->datetime_precision(thd);
+    uint dec1= Interval_DDhhmmssff::fsp(thd, item->arguments()[1]);
+    item->fix_attributes_datetime(MY_MAX(dec0, dec1));
+    return false;
+  }
+  bool val_native(THD *thd, Item_handled_func *func, Native *to) const override;
+};
+
+
 class Func_handler_add_time_time:
         public Item_handled_func::Handler_time,
         public Func_handler_sign
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index 6899654c28f..3119a010794 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -24,8 +24,6 @@
 #include <m_ctype.h>
 
 
-#define MAX_DAY_NUMBER 3652424L
-
 	/* Some functions to calculate dates */
 
 /*
@@ -236,7 +234,7 @@ bool get_date_from_daynr(long daynr,uint *ret_year,uint *ret_month,
   uchar *month_pos;
   DBUG_ENTER("get_date_from_daynr");
 
-  if (daynr < 366 || daynr > MAX_DAY_NUMBER)
+  if (daynr < 366 || daynr > Date::MAX_DAY_NUMBER())
     DBUG_RETURN(1);
 
   year= (uint) (daynr*100 / 36525L);
@@ -962,10 +960,7 @@ bool date_add_interval(THD *thd, MYSQL_TIME *ltime, interval_type int_type,
     my_bool neg= 0;
     enum enum_mysql_timestamp_type time_type= ltime->time_type;
 
-    if (((ulonglong) interval.day +
-         (ulonglong) interval.hour / 24 +
-         (ulonglong) interval.minute / 24 / 60 +
-         (ulonglong) interval.second / 24 / 60 / 60) > MAX_DAY_NUMBER)
+    if (interval.DDhhmmss_to_days_abs() > Date::MAX_DAY_NUMBER())
       goto invalid_date;
 
     if (time_type != MYSQL_TIMESTAMP_TIME)
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 5d109309d85..6a6cb853a7c 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -401,6 +401,57 @@ bool Timestamp::to_TIME(THD *thd, MYSQL_TIME *to, date_mode_t fuzzydate) const
 }
 
 
+
+bool Timestamp::check_zero_timestamp_result_with_warn(THD *thd) const
+{
+  if (!tv_sec && !tv_usec)
+  {
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                        ER_DATETIME_FUNCTION_OVERFLOW,
+                        ER_THD(thd, ER_DATETIME_FUNCTION_OVERFLOW),
+                        "timestamp");
+    return true;
+  }
+  return false;
+}
+
+
+bool Sec6::check_timestamp_range_with_warn(THD *thd) const
+{
+  if (neg() || sec() > TIMESTAMP_MAX_VALUE)
+  {
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                        ER_DATETIME_FUNCTION_OVERFLOW,
+                        ER_THD(thd, ER_DATETIME_FUNCTION_OVERFLOW),
+                        "timestamp");
+    return true;
+  }
+  return false;
+}
+
+
+bool Timestamp::add(THD *thd, interval_type int_type, const INTERVAL &interval)
+{
+  MYSQL_TIME mysql_time;
+  my_tz_OFFSET0->gmt_sec_to_TIME(&mysql_time, tv_sec);
+  mysql_time.second_part= tv_usec;
+  if (date_add_interval(thd, &mysql_time, int_type, interval))
+    return true;
+  uint error_code;
+  tv_sec= my_tz_OFFSET0->TIME_to_gmt_sec(&mysql_time, &error_code);
+  tv_usec= mysql_time.second_part;
+  if (error_code)
+  {
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                        ER_DATETIME_FUNCTION_OVERFLOW,
+                        ER_THD(thd, ER_DATETIME_FUNCTION_OVERFLOW),
+                        "timestamp");
+    return true;
+  }
+  return false;
+};
+
+
 Timestamp::Timestamp(THD *thd, const MYSQL_TIME *ltime, uint *error_code)
  :Timeval(TIME_to_timestamp(thd, ltime, error_code), ltime->second_part)
 { }
@@ -422,6 +473,22 @@ Timestamp_or_zero_datetime::Timestamp_or_zero_datetime(THD *thd,
 }
 
 
+bool Timestamp_or_zero_datetime_native::
+       check_zero_datetime_with_warn(THD *thd) const
+{
+  if (is_zero_datetime())
+  {
+    char buff[]= "0000-00-00 00:00:00";
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+                        ER_WRONG_VALUE,
+                        ER_THD(thd, ER_WRONG_VALUE),
+                        "timestamp", buff);
+    return true;
+  }
+  return false;
+}
+
+
 bool Timestamp_or_zero_datetime::to_TIME(THD *thd, MYSQL_TIME *to,
                                          date_mode_t fuzzydate) const
 {
@@ -461,6 +528,19 @@ int Timestamp_or_zero_datetime_native::save_in_field(Field *field,
 }
 
 
+Timestamp_null::Timestamp_null(THD *thd, Item *item, bool allow_zero_timestamp)
+ :Timestamp(0, 0), Null_flag(true)
+{
+  Timestamp_or_zero_datetime_native_null native(thd, item, false);
+  if (!native.is_null() &&
+      (allow_zero_timestamp || !native.check_zero_datetime_with_warn(thd)))
+  {
+    Timestamp::operator=(Timestamp(native));
+    m_is_null= false;
+  }
+}
+
+
 void Sec6::make_from_decimal(const my_decimal *d, ulong *nanoseconds)
 {
   m_neg= my_decimal2seconds(d, &m_sec, &m_usec, nanoseconds);
diff --git a/sql/sql_type.h b/sql/sql_type.h
index dcdf6438fd9..7c9f6df6126 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -546,10 +546,65 @@ public:
   {
     make_from_int(Longlong_hybrid(nr, unsigned_val));
   }
+  explicit Sec6(bool neg, ulonglong sec, ulong usec)
+   :m_sec(sec), m_usec(usec), m_neg(neg), m_truncated(false)
+  { }
+  explicit Sec6(bool neg, const struct timeval tv)
+   :m_sec(tv.tv_sec), m_usec(tv.tv_usec), m_neg(neg), m_truncated(false)
+  { }
+  explicit Sec6(const struct timeval tv)
+   :m_sec(tv.tv_sec), m_usec(tv.tv_usec), m_neg(false), m_truncated(false)
+  { }
   bool neg() const { return m_neg; }
   bool truncated() const { return m_truncated; }
   ulonglong sec() const { return m_sec; }
   long usec() const { return m_usec; }
+  ulonglong to_sec_usec_abs() const
+  {
+    return m_sec * 1000000ULL + m_usec;
+  }
+  longlong to_sec_usec() const
+  {
+    return (longlong) to_sec_usec_abs() * (m_neg ? -1LL : +1LL);
+  }
+
+  Sec6 operator-() const
+  {
+    DBUG_ASSERT(!m_truncated);
+    Sec6 res(*this);
+    res.m_neg= !res.m_neg;
+    return res;
+  }
+
+  Sec6 operator+(const Sec6 &rhs) const
+  {
+    if (m_neg == rhs.m_neg)
+    {
+      DBUG_ASSERT(!m_truncated);
+      DBUG_ASSERT(!rhs.m_truncated);
+      Sec6 res(*this);
+      res.m_sec+= rhs.m_sec;
+      res.m_usec+= rhs.m_usec;
+      if (res.m_usec > 1000000)
+      {
+        res.m_sec++;
+        res.m_usec-= 1000000;
+      }
+      return res;
+    }
+    longlong sec_usec= to_sec_usec() + rhs.to_sec_usec();
+    bool neg= sec_usec < 0;
+    if (neg)
+      sec_usec*= -1;
+    return Sec6(neg, (ulonglong) sec_usec / 1000000ULL,
+                     (ulonglong) sec_usec % 1000000ULL);
+  }
+
+  Sec6 operator-(const Sec6 &rhs) const
+  {
+    return *this + (-rhs);
+  }
+
   /**
     Converts Sec6 to MYSQL_TIME
     @param thd           current thd
@@ -650,6 +705,44 @@ public:
     ltime->second_part= m_usec;
     return false;
   }
+
+  /*
+    Convert to MariaDB interval units.
+    Only the whole part of the specified unit is returned (without fractions).
+    E.g. in case of INTERVAL_SECOND microseconds are not mixed to the result.
+  */
+  ulonglong DDhhmmssff_to_integer_units_abs(interval_type int_type) const
+  {
+    switch (int_type) {
+    case INTERVAL_WEEK:
+      return m_sec / SECONDS_IN_24H / 7ULL;
+    case INTERVAL_DAY:
+      return m_sec / SECONDS_IN_24H;
+    case INTERVAL_HOUR:
+      return m_sec / 3600ULL;
+    case INTERVAL_MINUTE:
+      return m_sec / 60ULL;
+    case INTERVAL_SECOND:
+      return m_sec;
+    case INTERVAL_MICROSECOND:
+      /*
+        In MySQL difference between any two valid datetime values
+        in microseconds fits into longlong.
+      */
+      return to_sec_usec_abs();
+    default:
+      break;
+    }
+    DBUG_ASSERT(0);
+    return 0;
+  }
+
+  longlong DDhhmmssff_to_integer_units(interval_type int_type) const
+  {
+    return (longlong) DDhhmmssff_to_integer_units_abs(int_type) *
+           (m_neg ? -1LL : +1LL);
+  }
+
   Sec6 &trunc(uint dec)
   {
     m_usec-= my_time_fraction_remainder(m_usec, dec);
@@ -663,6 +756,7 @@ public:
       my_snprintf(to, nbytes, "%s%llu", m_neg ? "-" : "", m_sec);
   }
   void make_truncated_warning(THD *thd, const char *type_str) const;
+  bool check_timestamp_range_with_warn(THD *thd) const;
 };
 
 
@@ -1492,6 +1586,17 @@ public:
                                                 dec));
     return str;
   }
+  ulonglong to_seconds_abs() const
+  {
+    return (ulonglong) MYSQL_TIME::hour * 60 * 60 +
+           (ulonglong) MYSQL_TIME::minute * 60 +
+           (ulonglong) MYSQL_TIME::second;
+  }
+  Sec6 to_sec6() const
+  {
+    DBUG_ASSERT(is_valid_interval_DDhhmmssff());
+    return Sec6(MYSQL_TIME::neg, to_seconds_abs(), MYSQL_TIME::second_part);
+  }
 };
 
 class Schema;
@@ -2157,6 +2262,11 @@ public:
      :Temporal_with_date::Options(fuzzydate)
     { }
   };
+  // Day number for '9999-12-31'
+  static constexpr uint MAX_DAY_NUMBER()
+  {
+    return 3652424;
+  }
 public:
   Date(Item *item, date_mode_t fuzzydate)
    :Date(current_thd, item, fuzzydate)
@@ -2765,6 +2875,46 @@ public:
     int warn= 0;
     return round(dec, mode, &warn);
   }
+  bool check_zero_timestamp_result_with_warn(THD *thd) const;
+  /*
+   Add an arbitrary interval.
+   Uses MYSQL_TIME representation internally.
+  */
+  bool add(THD *thd, interval_type int_type, const INTERVAL &interval);
+
+  /*
+    Add or subtract a simple interval represented as Timeval.
+  */
+  bool add(THD *thd, const Sec6 &rhs)
+  {
+    Sec6 res= Sec6(*this) + rhs;
+    if (res.check_timestamp_range_with_warn(thd))
+      return true;
+    *this= Timestamp(res.sec(), res.usec());
+    return false;
+  }
+  bool sub(THD *thd, const Sec6 &rhs)
+  {
+    Sec6 res= Sec6(*this) - rhs;
+    if (res.check_timestamp_range_with_warn(thd))
+      return true;
+    *this= Timestamp(res.sec(), res.usec());
+    return false;
+  }
+
+  Sec6 diff(const Timestamp &rhs) const
+  {
+    return Sec6(tv()) - Sec6(rhs.tv());
+  }
+
+};
+
+
+class Timestamp_null: public Timestamp, public Null_flag
+{
+public:
+  // TODO: change this to pass NO_ZERO_DATE instead of bool?
+  explicit Timestamp_null(THD *thd, Item *item, bool allow_zero_timestamp);
 };
 
 
@@ -2846,6 +2996,7 @@ public:
   {
     return length() == 0;
   }
+  bool check_zero_datetime_with_warn(THD *thd) const;
 };
 
 
diff --git a/sql/structs.h b/sql/structs.h
index b36f8e6a1a0..e60dc83650a 100644
--- a/sql/structs.h
+++ b/sql/structs.h
@@ -204,6 +204,29 @@ typedef struct {
   ulong year,month,day,hour;
   ulonglong minute,second,second_part;
   bool neg;
+  ulonglong DDhhmmss_to_integer_seconds_abs() const
+  {
+    return second +
+           (minute * 60) +
+           (hour * 60 * 60) +
+           ((ulonglong) day * 24 * 60 * 60);
+  }
+  ulonglong DDhhmmssff_to_integer_seconds_abs() const
+  {
+    return DDhhmmss_to_integer_seconds_abs() + second_part / 1000000;
+  }
+  ulonglong DDhhmmss_to_days_abs() const
+  {
+    return (ulonglong) day +
+           hour / 24 +
+           minute / 24 / 60 +
+           second / 24 / 60 / 60;
+  }
+  ulonglong DDhhmmssff_to_days_abs() const
+  {
+    return DDhhmmss_to_days_abs() +
+           second_part / 24 / 60 / 60 / 1000000;
+  }
 } INTERVAL;