summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/func_sapdb.result19
-rw-r--r--mysql-test/r/func_time.result57
-rw-r--r--mysql-test/r/keywords.result16
-rw-r--r--mysql-test/t/func_sapdb.test3
-rw-r--r--mysql-test/t/func_time.test24
-rw-r--r--mysql-test/t/keywords.test8
6 files changed, 107 insertions, 20 deletions
diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result
index e330c73727b..6bd4c6f46a2 100644
--- a/mysql-test/r/func_sapdb.result
+++ b/mysql-test/r/func_sapdb.result
@@ -188,13 +188,14 @@ ttt qqq
NULL NULL
NULL NULL
2001-01-01 02:02:02 26:02:02
-SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
-ttt qqq
--744:00:00 NULL
-26305:01:02 22:58:58
--26305:01:02 -22:58:58
-NULL 26:02:02
-NULL NULL
-NULL NULL
-00:00:00 -24:00:00
+SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq,
+TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test;
+ttt qqq eee rrr
+-744:00:00 NULL NULL NULL
+26305:01:02 22:58:58 -22:58:58 NULL
+-26305:01:02 -22:58:58 22:58:58 NULL
+NULL 26:02:02 26:02:02 NULL
+NULL NULL NULL NULL
+NULL NULL NULL NULL
+00:00:00 -24:00:00 24:00:00 NULL
drop table t1, test;
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index b8709487c6d..0bad2c4d636 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -464,6 +464,63 @@ date_add(date,INTERVAL "1 1:1" DAY_MINUTE)
select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
date_add(date,INTERVAL "1 1:1:1" DAY_SECOND)
2003-01-03 01:01:01
+select date_add(date,INTERVAL "1" WEEK) from t1;
+date_add(date,INTERVAL "1" WEEK)
+2003-01-09 00:00:00
+select date_add(date,INTERVAL "1" QUARTER) from t1;
+date_add(date,INTERVAL "1" QUARTER)
+2003-04-02
+select timestampadd(MINUTE, 1, date) from t1;
+timestampadd(MINUTE, 1, date)
+2003-01-02 00:01:00
+select timestampadd(WEEK, 1, date) from t1;
+timestampadd(WEEK, 1, date)
+2003-01-09 00:00:00
+select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
+timestampadd(SQL_TSI_SECOND, 1, date)
+2003-01-02 00:00:01
+select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;
+timestampadd(SQL_TSI_FRAC_SECOND, 1, date)
+2003-01-02 00:00:00.000001
+select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
+a
+3
+select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
+a
+-1
+select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
+a
+-5
+select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
+a
+0
+select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
+a
+107
+select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
+a
+12
+select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
+a
+2136
+select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
+a
+89
+select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
+a
+128159
+select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
+a
+7689539
+select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;
+a
+7689538999999
+select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,
+timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,
+timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
+timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
+a1 a2 a3 a4
+28 28 29 29
select date_add(time,INTERVAL 1 SECOND) from t1;
date_add(time,INTERVAL 1 SECOND)
2006-07-08 00:00:01
diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result
index c218379110f..88a0ab8abd5 100644
--- a/mysql-test/r/keywords.result
+++ b/mysql-test/r/keywords.result
@@ -1,12 +1,14 @@
drop table if exists t1;
-create table t1 (time time, date date, timestamp timestamp);
-insert into t1 values ("12:22:22","97:02:03","1997-01-02");
+create table t1 (time time, date date, timestamp timestamp,
+quarter int, week int, year int, timestampadd int, timestampdiff int);
+insert into t1 values ("12:22:22","97:02:03","1997-01-02",1,2,3,4,5);
select * from t1;
-time date timestamp
-12:22:22 1997-02-03 1997-01-02 00:00:00
-select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time) from t1;
-t1.time+0 t1.date+0 t1.timestamp+0 concat(date," ",time)
-122222 19970203 19970102000000 1997-02-03 12:22:22
+time date timestamp quarter week year timestampadd timestampdiff
+12:22:22 1997-02-03 1997-01-02 00:00:00 1 2 3 4 5
+select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time),
+t1.quarter+t1.week, t1.year+timestampadd, timestampdiff from t1;
+t1.time+0 t1.date+0 t1.timestamp+0 concat(date," ",time) t1.quarter+t1.week t1.year+timestampadd timestampdiff
+122222 19970203 19970102000000 1997-02-03 12:22:22 3 7 5
drop table t1;
create table events(binlog int);
insert into events values(1);
diff --git a/mysql-test/t/func_sapdb.test b/mysql-test/t/func_sapdb.test
index afd84fe9630..ba64e13873d 100644
--- a/mysql-test/t/func_sapdb.test
+++ b/mysql-test/t/func_sapdb.test
@@ -94,6 +94,7 @@ insert into test values
('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
SELECT ADDTIME(t1,t2) As ttt, ADDTIME(t2, t3) As qqq from test;
-SELECT TIMEDIFF(t1,t4) As ttt, TIMEDIFF(t2, t3) As qqq from test;
+SELECT TIMEDIFF(t1, t4) As ttt, TIMEDIFF(t2, t3) As qqq,
+ TIMEDIFF(t3, t2) As eee, TIMEDIFF(t2, t4) As rrr from test;
drop table t1, test;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 30d616915ab..35a532f330e 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -219,6 +219,30 @@ select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;
select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;
select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;
+select date_add(date,INTERVAL "1" WEEK) from t1;
+select date_add(date,INTERVAL "1" QUARTER) from t1;
+select timestampadd(MINUTE, 1, date) from t1;
+select timestampadd(WEEK, 1, date) from t1;
+select timestampadd(SQL_TSI_SECOND, 1, date) from t1;
+select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;
+
+select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;
+select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;
+select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;
+select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;
+select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;
+select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;
+select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;
+select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;
+select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;
+select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;
+select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;
+
+select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,
+ timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,
+ timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
+ timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
+
# The following is not as one would expect...
select date_add(time,INTERVAL 1 SECOND) from t1;
drop table t1;
diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test
index e7ec63afe54..3392bfa1b3b 100644
--- a/mysql-test/t/keywords.test
+++ b/mysql-test/t/keywords.test
@@ -6,10 +6,12 @@
drop table if exists t1;
--enable_warnings
-create table t1 (time time, date date, timestamp timestamp);
-insert into t1 values ("12:22:22","97:02:03","1997-01-02");
+create table t1 (time time, date date, timestamp timestamp,
+quarter int, week int, year int, timestampadd int, timestampdiff int);
+insert into t1 values ("12:22:22","97:02:03","1997-01-02",1,2,3,4,5);
select * from t1;
-select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time) from t1;
+select t1.time+0,t1.date+0,t1.timestamp+0,concat(date," ",time),
+ t1.quarter+t1.week, t1.year+timestampadd, timestampdiff from t1;
drop table t1;
create table events(binlog int);
insert into events values(1);
View Lorry Controller Status