create schema date_add_time_interval;
set current_schema = date_add_time_interval;

set dolphin.cmpt_version = '5.7';
select date_add(time'838:59:59', interval -1 day);
select date_sub(time'838:59:59', interval 1 day);
select date_add(time'838:59:59', interval 1 day);
select date_sub(time'838:59:59', interval -1 day);
select date_add(time'838:59:59', interval -1 month);
select date_sub(time'838:59:59', interval 1 month);
select date_add(time'838:59:59', interval -1 year);
select date_sub(time'838:59:59', interval 1 year);

SELECT SUBDATE(time'23:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT SUBDATE(time'23:59:59','08:00:2b:01:02:03');
SELECT SUBDATE(time'838:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT SUBDATE(time'838:59:59','08:00:2b:01:02:03');
SELECT ADDDATE(time'23:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT ADDDATE(time'23:59:59','08:00:2b:01:02:03');
SELECT ADDDATE(time'838:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT ADDDATE(time'838:59:59','08:00:2b:01:02:03');

set dolphin.cmpt_version = '8.0';
select date_add(time'838:59:59', interval -1 day);
select date_sub(time'838:59:59', interval 1 day);
select date_add(time'838:59:59', interval 1 day);
select date_sub(time'838:59:59', interval -1 day);
select date_add(time'838:59:59', interval -1 month);
select date_sub(time'838:59:59', interval 1 month);
select date_add(time'838:59:59', interval -1 year);
select date_sub(time'838:59:59', interval 1 year);

SELECT SUBDATE(time'23:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT SUBDATE(time'23:59:59','08:00:2b:01:02:03');
SELECT SUBDATE(time'838:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT SUBDATE(time'838:59:59','08:00:2b:01:02:03');
SELECT ADDDATE(time'23:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT ADDDATE(time'23:59:59','08:00:2b:01:02:03');
SELECT ADDDATE(time'838:59:59.9', '3 years 2 mons 5 days 14:25:26');
SELECT ADDDATE(time'838:59:59','08:00:2b:01:02:03');


create table test1(c1 date);
create table x1 as select * from test1 where c1 >= date_sub(curdate(), interval 3 month);
create table x2 as select * from test1 where c1 >= date_add(curdate(), interval 3 month);

drop table test1;
drop table x1;
drop table x2;

create table testdate_add(d time);
insert into testdate_add(d) values('2022-12-31 22:12:20');
select * from testdate_add;

set dolphin.cmpt_version = '5.7';
select date_add(d,INTERVAL 2 DAY_MINUTE) from testdate_add;
select date_sub(d,INTERVAL 2 DAY_HOUR) from testdate_add;
select date_sub(date_add(d,INTERVAL 2 DAY_MINUTE),INTERVAL 2 DAY_HOUR) from testdate_add;
select date_add(date_sub(d,INTERVAL 2 DAY_MINUTE),INTERVAL 2 DAY_HOUR) from testdate_add;

set dolphin.cmpt_version = '8.0';
select date_add(d,INTERVAL 2 DAY_MINUTE) from testdate_add;
select date_sub(d,INTERVAL 2 DAY_HOUR) from testdate_add;
select date_sub(date_add(d,INTERVAL 2 DAY_MINUTE),INTERVAL 2 DAY_HOUR) from testdate_add;
select date_add(date_sub(d,INTERVAL 2 DAY_MINUTE),INTERVAL 2 DAY_HOUR) from testdate_add;

drop table testdate_add;
reset dolphin.cmpt_version;
reset current_schema;
drop schema date_add_time_interval;