1477 lines
50 KiB
Plaintext
1477 lines
50 KiB
Plaintext
select dayname("1962-03-01")+0;
|
|
dayname("1962-03-01")+0
|
|
3
|
|
select dayname("1962-03-02")+0;
|
|
dayname("1962-03-02")+0
|
|
4
|
|
select dayname("1962-03-03")+0;
|
|
dayname("1962-03-03")+0
|
|
5
|
|
select dayname("1962-03-04")+0;
|
|
dayname("1962-03-04")+0
|
|
6
|
|
select dayname("1962-03-05")+0;
|
|
dayname("1962-03-05")+0
|
|
0
|
|
select dayname("1962-03-06")+0;
|
|
dayname("1962-03-06")+0
|
|
1
|
|
select dayname("1962-03-07")+0;
|
|
dayname("1962-03-07")+0
|
|
2
|
|
select dayname("1962-03-08")+0;
|
|
dayname("1962-03-08")+0
|
|
3
|
|
select dayname("1962-03-01")+1;
|
|
dayname("1962-03-01")+1
|
|
4
|
|
select dayname("1962-03-01")+2;
|
|
dayname("1962-03-01")+2
|
|
5
|
|
select dayname("1962-03-01")+3;
|
|
dayname("1962-03-01")+3
|
|
6
|
|
select dayname("1962-03-01")+4;
|
|
dayname("1962-03-01")+4
|
|
7
|
|
select dayname("1962-03-01")+5;
|
|
dayname("1962-03-01")+5
|
|
8
|
|
select dayname("1962-03-01")+6;
|
|
dayname("1962-03-01")+6
|
|
9
|
|
select dayname("1962-03-01")+7;
|
|
dayname("1962-03-01")+7
|
|
10
|
|
select dayname("1962-03-01")+2333;
|
|
dayname("1962-03-01")+2333
|
|
2336
|
|
select dayname("1962-03-01")+2.333;
|
|
dayname("1962-03-01")+2.333
|
|
5.333
|
|
select dayname("1962-03-01")>2;
|
|
dayname("1962-03-01")>2
|
|
1
|
|
select dayname("1962-03-01")<2;
|
|
dayname("1962-03-01")<2
|
|
0
|
|
select dayname("1962-03-01")=3;
|
|
dayname("1962-03-01")=3
|
|
1
|
|
select dayname("1962-03-01")!=3;
|
|
dayname("1962-03-01")!=3
|
|
0
|
|
select dayname("1962-03-01")<4;
|
|
dayname("1962-03-01")<4
|
|
1
|
|
select dayname("1962-03-01")>4;
|
|
dayname("1962-03-01")>4
|
|
0
|
|
select !dayname("1962-03-01");
|
|
!dayname("1962-03-01")
|
|
0
|
|
select dayname("1962-03-01")&1;
|
|
dayname("1962-03-01")&1
|
|
1
|
|
select dayname("1962-03-01")&3;
|
|
dayname("1962-03-01")&3
|
|
3
|
|
select dayname("1962-03-01")&7;
|
|
dayname("1962-03-01")&7
|
|
3
|
|
select dayname("1962-03-01")|1;
|
|
dayname("1962-03-01")|1
|
|
3
|
|
select dayname("1962-03-01")|3;
|
|
dayname("1962-03-01")|3
|
|
3
|
|
select dayname("1962-03-01")|7;
|
|
dayname("1962-03-01")|7
|
|
7
|
|
select dayname("1962-03-01")^1;
|
|
dayname("1962-03-01")^1
|
|
2
|
|
select dayname("1962-03-01")^3;
|
|
dayname("1962-03-01")^3
|
|
0
|
|
select dayname("1962-03-01")^7;
|
|
dayname("1962-03-01")^7
|
|
4
|
|
drop table if exists t;
|
|
create table t (a bigint primary key, b timestamp);
|
|
insert into t values (1, "2019-04-29 11:56:12");
|
|
explain format = 'plan_tree' select * from t where b = (select max(b) from t);
|
|
id task access object operator info
|
|
TableReader root data:Selection
|
|
└─Selection cop[tikv] eq(expression__time.t.b, ScalarQueryCol#6(2019-04-29 11:56:12))
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
ScalarSubQuery root Output: ScalarQueryCol#6
|
|
└─MaxOneRow root
|
|
└─StreamAgg root funcs:max(expression__time.t.b)->Column
|
|
└─TopN root expression__time.t.b:desc, offset:0, count:1
|
|
└─TableReader root data:TopN
|
|
└─TopN cop[tikv] expression__time.t.b:desc, offset:0, count:1
|
|
└─Selection cop[tikv] not(isnull(expression__time.t.b))
|
|
└─TableFullScan cop[tikv] table:t keep order:false, stats:pseudo
|
|
select * from t where b = (select max(b) from t);
|
|
a b
|
|
1 2019-04-29 11:56:12
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL 1.123456789e3 SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL 1.123456789e3 SECOND
|
|
1900-01-01 00:18:43.456789
|
|
SELECT 19000101000000 + INTERVAL 1.123456789e3 SECOND;
|
|
19000101000000 + INTERVAL 1.123456789e3 SECOND
|
|
1900-01-01 00:18:43.456789
|
|
select date("1900-01-01") + interval 1.123456789e3 second;
|
|
date("1900-01-01") + interval 1.123456789e3 second
|
|
1900-01-01 00:18:43.456789
|
|
SELECT "1900-01-01 00:18:43.456789" - INTERVAL 1.123456789e3 SECOND;
|
|
"1900-01-01 00:18:43.456789" - INTERVAL 1.123456789e3 SECOND
|
|
1900-01-01 00:00:00
|
|
SELECT 19000101001843.456789 - INTERVAL 1.123456789e3 SECOND;
|
|
19000101001843.456789 - INTERVAL 1.123456789e3 SECOND
|
|
1900-01-01 00:00:00
|
|
SELECT 19000101000000.0005 + INTERVAL 0.0005 SECOND;
|
|
19000101000000.0005 + INTERVAL 0.0005 SECOND
|
|
1900-01-01 00:00:00.001000
|
|
select date("1900-01-01") - interval 1.123456789e3 second;
|
|
date("1900-01-01") - interval 1.123456789e3 second
|
|
1899-12-31 23:41:16.543211
|
|
select 19000101000000 - interval 1.123456789e3 second;
|
|
19000101000000 - interval 1.123456789e3 second
|
|
1899-12-31 23:41:16.543211
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "2" HOUR;
|
|
"1900-01-01 00:00:00" + INTERVAL "2" HOUR
|
|
1900-01-01 02:00:00
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "-2" HOUR;
|
|
"1900-01-01 00:00:00" + INTERVAL "-2" HOUR
|
|
1899-12-31 22:00:00
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "128" HOUR;
|
|
"1900-01-01 00:00:00" + INTERVAL "128" HOUR
|
|
1900-01-06 08:00:00
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "1e+3" HOUR;
|
|
"1900-01-01 00:00:00" + INTERVAL "1e+3" HOUR
|
|
1900-01-01 01:00:00
|
|
SELECT "1900-01-01 00:00:00" + INTERVAL "1+1" HOUR;
|
|
"1900-01-01 00:00:00" + INTERVAL "1+1" HOUR
|
|
1900-01-01 01:00:00
|
|
drop table if exists t;
|
|
create table t (id int primary key auto_increment, a varchar(32));
|
|
insert into t (a) values(''), ('+1'), ('+1+2'), ('-1'), ('2.2'), ('2.9'), ('2.2+1'), ('2+2.2'), ('5-2'), ('1e2'),
|
|
('true'), ('false'), ('xxx'), ('xxx+1'), ('xxx1'), (' 1 '), ('xxx-1'),
|
|
('9223372036854775808'), ('-9223372036854775809'), ('9223372036854775808-02'), ('-9223372036854775809-02');
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a MICROSECOND as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-01-01 00:00:00.000001
|
|
3 +1+2 1900-01-01 00:00:00.000001
|
|
4 -1 1899-12-31 23:59:59.999999
|
|
5 2.2 1900-01-01 00:00:00.000002
|
|
6 2.9 1900-01-01 00:00:00.000002
|
|
7 2.2+1 1900-01-01 00:00:00.000002
|
|
8 2+2.2 1900-01-01 00:00:00.000002
|
|
9 5-2 1900-01-01 00:00:00.000005
|
|
10 1e2 1900-01-01 00:00:00.000001
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-01-01 00:00:00.000001
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a SECOND as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-01-01 00:00:01
|
|
3 +1+2 1900-01-01 00:00:01
|
|
4 -1 1899-12-31 23:59:59
|
|
5 2.2 1900-01-01 00:00:02.200000
|
|
6 2.9 1900-01-01 00:00:02.900000
|
|
7 2.2+1 1900-01-01 00:00:02.200000
|
|
8 2+2.2 1900-01-01 00:00:02
|
|
9 5-2 1900-01-01 00:00:05
|
|
10 1e2 1900-01-01 00:01:40
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-01-01 00:00:01
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a MINUTE as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-01-01 00:01:00
|
|
3 +1+2 1900-01-01 00:01:00
|
|
4 -1 1899-12-31 23:59:00
|
|
5 2.2 1900-01-01 00:02:00
|
|
6 2.9 1900-01-01 00:02:00
|
|
7 2.2+1 1900-01-01 00:02:00
|
|
8 2+2.2 1900-01-01 00:02:00
|
|
9 5-2 1900-01-01 00:05:00
|
|
10 1e2 1900-01-01 00:01:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-01-01 00:01:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a HOUR as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-01-01 01:00:00
|
|
3 +1+2 1900-01-01 01:00:00
|
|
4 -1 1899-12-31 23:00:00
|
|
5 2.2 1900-01-01 02:00:00
|
|
6 2.9 1900-01-01 02:00:00
|
|
7 2.2+1 1900-01-01 02:00:00
|
|
8 2+2.2 1900-01-01 02:00:00
|
|
9 5-2 1900-01-01 05:00:00
|
|
10 1e2 1900-01-01 01:00:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-01-01 01:00:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a DAY as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-01-02 00:00:00
|
|
3 +1+2 1900-01-02 00:00:00
|
|
4 -1 1899-12-31 00:00:00
|
|
5 2.2 1900-01-03 00:00:00
|
|
6 2.9 1900-01-03 00:00:00
|
|
7 2.2+1 1900-01-03 00:00:00
|
|
8 2+2.2 1900-01-03 00:00:00
|
|
9 5-2 1900-01-06 00:00:00
|
|
10 1e2 1900-01-02 00:00:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-01-02 00:00:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a WEEK as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-01-08 00:00:00
|
|
3 +1+2 1900-01-08 00:00:00
|
|
4 -1 1899-12-25 00:00:00
|
|
5 2.2 1900-01-15 00:00:00
|
|
6 2.9 1900-01-15 00:00:00
|
|
7 2.2+1 1900-01-15 00:00:00
|
|
8 2+2.2 1900-01-15 00:00:00
|
|
9 5-2 1900-02-05 00:00:00
|
|
10 1e2 1900-01-08 00:00:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-01-08 00:00:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a MONTH as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-02-01 00:00:00
|
|
3 +1+2 1900-02-01 00:00:00
|
|
4 -1 1899-12-01 00:00:00
|
|
5 2.2 1900-03-01 00:00:00
|
|
6 2.9 1900-03-01 00:00:00
|
|
7 2.2+1 1900-03-01 00:00:00
|
|
8 2+2.2 1900-03-01 00:00:00
|
|
9 5-2 1900-06-01 00:00:00
|
|
10 1e2 1900-02-01 00:00:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-02-01 00:00:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a QUARTER as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1900-04-01 00:00:00
|
|
3 +1+2 1900-04-01 00:00:00
|
|
4 -1 1899-10-01 00:00:00
|
|
5 2.2 1900-07-01 00:00:00
|
|
6 2.9 1900-07-01 00:00:00
|
|
7 2.2+1 1900-07-01 00:00:00
|
|
8 2+2.2 1900-07-01 00:00:00
|
|
9 5-2 1901-04-01 00:00:00
|
|
10 1e2 1900-04-01 00:00:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1900-04-01 00:00:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select id, a, "1900-01-01 00:00:00" + INTERVAL a YEAR as result from t order by id ASC;
|
|
id a result
|
|
1 1900-01-01 00:00:00
|
|
2 +1 1901-01-01 00:00:00
|
|
3 +1+2 1901-01-01 00:00:00
|
|
4 -1 1899-01-01 00:00:00
|
|
5 2.2 1902-01-01 00:00:00
|
|
6 2.9 1902-01-01 00:00:00
|
|
7 2.2+1 1902-01-01 00:00:00
|
|
8 2+2.2 1902-01-01 00:00:00
|
|
9 5-2 1905-01-01 00:00:00
|
|
10 1e2 1901-01-01 00:00:00
|
|
11 true 1900-01-01 00:00:00
|
|
12 false 1900-01-01 00:00:00
|
|
13 xxx 1900-01-01 00:00:00
|
|
14 xxx+1 1900-01-01 00:00:00
|
|
15 xxx1 1900-01-01 00:00:00
|
|
16 1 1901-01-01 00:00:00
|
|
17 xxx-1 1900-01-01 00:00:00
|
|
18 9223372036854775808 NULL
|
|
19 -9223372036854775809 NULL
|
|
20 9223372036854775808-02 NULL
|
|
21 -9223372036854775809-02 NULL
|
|
select "1900-01-01 00:00:00" + INTERVAL true MICROSECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL true MICROSECOND
|
|
1900-01-01 00:00:00.000001
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.2" MICROSECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "1.2" MICROSECOND
|
|
1900-01-01 00:00:00.000001
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.9" MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL "1.9" MINUTE
|
|
1900-01-01 00:01:00
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.2 MICROSECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL 1.2 MICROSECOND
|
|
1900-01-01 00:00:00.000001
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.9 MICROSECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL 1.9 MICROSECOND
|
|
1900-01-01 00:00:00.000002
|
|
select "1900-01-01 00:00:00" + INTERVAL true SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL true SECOND
|
|
1900-01-01 00:00:01
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.2 SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL 1.2 SECOND
|
|
1900-01-01 00:00:01.200000
|
|
select "1900-01-01 00:00:00" + INTERVAL "1+2" SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "1+2" SECOND
|
|
1900-01-01 00:00:01
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.2+2" SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "1.2+2" SECOND
|
|
1900-01-01 00:00:01.200000
|
|
select "1900-01-01 00:00:00" + INTERVAL "1+2.2" SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "1+2.2" SECOND
|
|
1900-01-01 00:00:01
|
|
select "1900-01-01 00:00:00" + INTERVAL "0.000001" SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "0.000001" SECOND
|
|
1900-01-01 00:00:00.000001
|
|
select "1900-01-01 00:00:00" + INTERVAL "0.0000009" SECOND;
|
|
"1900-01-01 00:00:00" + INTERVAL "0.0000009" SECOND
|
|
1900-01-01 00:00:00
|
|
select "1900-01-01 00:00:00" + INTERVAL true MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL true MINUTE
|
|
1900-01-01 00:01:00
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.2" MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL "1.2" MINUTE
|
|
1900-01-01 00:01:00
|
|
select "1900-01-01 00:00:00" + INTERVAL "1.9" MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL "1.9" MINUTE
|
|
1900-01-01 00:01:00
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.2 MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL 1.2 MINUTE
|
|
1900-01-01 00:01:00
|
|
select "1900-01-01 00:00:00" + INTERVAL 1.9 MINUTE;
|
|
"1900-01-01 00:00:00" + INTERVAL 1.9 MINUTE
|
|
1900-01-01 00:02:00
|
|
drop table if exists t;
|
|
create table t(a datetime(6), b timestamp);
|
|
insert t values (20010101100000.123456, 20110707101112.123456);
|
|
select * from t;
|
|
a b
|
|
2001-01-01 10:00:00.123456 2011-07-07 10:11:12
|
|
set time_zone = 'CET';
|
|
drop table if exists t;
|
|
create table t(ts timestamp);
|
|
set time_zone = 'UTC';
|
|
insert into t values('2022-03-27 00:30:00');
|
|
insert into t values('2022-10-30 00:30:00');
|
|
insert into t values('2022-10-30 01:30:00');
|
|
set time_zone = 'Europe/Amsterdam';
|
|
insert into t values('2022-03-27 02:30:00');
|
|
Error 1292 (22007): Incorrect timestamp value: '2022-03-27 02:30:00' for column 'ts' at row 1
|
|
select date_add(ts, interval 1 hour) from t order by ts;
|
|
date_add(ts, interval 1 hour)
|
|
2022-03-27 02:30:00
|
|
2022-10-30 03:30:00
|
|
2022-10-30 03:30:00
|
|
set time_zone = default;
|
|
drop table if exists t;
|
|
create table t(a date);
|
|
insert into t values ('2021-08-20');
|
|
select timestampadd(microsecond, 1, a) from t;
|
|
timestampadd(microsecond, 1, a)
|
|
2021-08-20 00:00:00.000001
|
|
select timestampadd(second, 6/4, a) from t;
|
|
timestampadd(second, 6/4, a)
|
|
2021-08-20 00:00:01.500000
|
|
select timestampadd(second, 9.9999e2, a) from t;
|
|
timestampadd(second, 9.9999e2, a)
|
|
2021-08-20 00:16:39.990000
|
|
select timestampadd(second, 1, '2021-08-20 00:00:01.0001');
|
|
timestampadd(second, 1, '2021-08-20 00:00:01.0001')
|
|
2021-08-20 00:00:02.000100
|
|
select timestampadd(minute, 1.5, '2021-08-20 00:00:00');
|
|
timestampadd(minute, 1.5, '2021-08-20 00:00:00')
|
|
2021-08-20 00:02:00
|
|
select timestampadd(minute, 1.5, '2021-08-20 00:00:00.0001');
|
|
timestampadd(minute, 1.5, '2021-08-20 00:00:00.0001')
|
|
2021-08-20 00:02:00.000100
|
|
SELECT timestampadd(year,1.212208e+308,'1995-01-05 06:32:20.859724') as result;
|
|
result
|
|
NULL
|
|
Level Code Message
|
|
Warning 1441 Datetime function: datetime field overflow
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 SECOND_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 SECOND_MICROSECOND)
|
|
2007-03-28 22:08:27.800000
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 MINUTE_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 MINUTE_MICROSECOND)
|
|
2007-03-28 22:08:27.800000
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 HOUR_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 HOUR_MICROSECOND)
|
|
2007-03-28 22:08:27.800000
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 DAY_MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2 DAY_MICROSECOND)
|
|
2007-03-28 22:08:27.800000
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_MINUTE)
|
|
2007-03-29 00:10:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE_SECOND)
|
|
2007-03-28 22:10:30
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR_MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR_MONTH)
|
|
2009-05-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_HOUR)
|
|
2007-03-31 00:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_MINUTE)
|
|
2007-03-29 00:10:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY_SECOND)
|
|
2007-03-28 22:10:30
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR_SECOND)
|
|
2007-03-28 22:10:30
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 SECOND)
|
|
2007-03-28 22:08:30.200000
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 YEAR)
|
|
2009-03-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 QUARTER);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 QUARTER)
|
|
2007-09-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MONTH)
|
|
2007-05-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 WEEK);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 WEEK)
|
|
2007-04-11 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 DAY)
|
|
2007-03-30 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 HOUR)
|
|
2007-03-29 00:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MINUTE)
|
|
2007-03-28 22:10:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL 2.2 MICROSECOND)
|
|
2007-03-28 22:08:28.000002
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_MINUTE)
|
|
2007-03-28 20:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR_MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR_MONTH)
|
|
2005-01-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_HOUR)
|
|
2007-03-26 20:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_MINUTE)
|
|
2007-03-28 20:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 YEAR)
|
|
2005-03-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 QUARTER);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 QUARTER)
|
|
2006-09-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MONTH)
|
|
2007-01-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 WEEK);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 WEEK)
|
|
2007-03-14 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 DAY)
|
|
2007-03-26 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 HOUR)
|
|
2007-03-28 20:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MINUTE)
|
|
2007-03-28 22:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL -2.2 MICROSECOND)
|
|
2007-03-28 22:08:27.999998
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR_MINUTE)
|
|
2007-03-28 20:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MINUTE_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MINUTE_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" YEAR_MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" YEAR_MONTH)
|
|
2005-01-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_HOUR)
|
|
2007-03-26 20:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_MINUTE)
|
|
2007-03-28 20:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" SECOND)
|
|
2007-03-28 22:08:25.800000
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" YEAR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" YEAR)
|
|
2005-03-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" QUARTER);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" QUARTER)
|
|
2006-09-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MONTH)
|
|
2007-01-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" WEEK);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" WEEK)
|
|
2007-03-14 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" DAY)
|
|
2007-03-26 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" HOUR)
|
|
2007-03-28 20:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MINUTE)
|
|
2007-03-28 22:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.2" MICROSECOND)
|
|
2007-03-28 22:08:27.999998
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR_MINUTE)
|
|
2007-03-28 20:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MINUTE_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MINUTE_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" YEAR_MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" YEAR_MONTH)
|
|
2005-01-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_HOUR)
|
|
2007-03-26 20:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_MINUTE)
|
|
2007-03-28 20:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR_SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR_SECOND)
|
|
2007-03-28 22:06:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" SECOND)
|
|
2007-03-28 22:08:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.+2" SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.+2" SECOND)
|
|
2007-03-28 22:08:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.*2" SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.*2" SECOND)
|
|
2007-03-28 22:08:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2./2" SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2./2" SECOND)
|
|
2007-03-28 22:08:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.a2" SECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.a2" SECOND)
|
|
2007-03-28 22:08:26
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" YEAR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" YEAR)
|
|
2005-03-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" QUARTER);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" QUARTER)
|
|
2006-09-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MONTH);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MONTH)
|
|
2007-01-28 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" WEEK);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" WEEK)
|
|
2007-03-14 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" DAY)
|
|
2007-03-26 22:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" HOUR)
|
|
2007-03-28 20:08:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MINUTE);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MINUTE)
|
|
2007-03-28 22:06:28
|
|
select DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MICROSECOND);
|
|
DATE_ADD('2007-03-28 22:08:28',INTERVAL "-2.-2" MICROSECOND)
|
|
2007-03-28 22:08:27.999998
|
|
set @@session.time_zone = 'Europe/Amsterdam';
|
|
drop table if exists t;
|
|
create table t (id int, dt datetime, primary key (id, dt));
|
|
insert into t values (1, date_add('2023-03-26 00:00:00', interval 2 hour));
|
|
insert into t values (4,'2023-03-26 02:00:00');
|
|
select * from t;
|
|
id dt
|
|
1 2023-03-26 02:00:00
|
|
4 2023-03-26 02:00:00
|
|
set time_zone = default;
|
|
drop table if exists t1;
|
|
create table t1(c_time time(5), c_dt datetime(4), c_ts timestamp(3), c_d date, c_str varchar(100));
|
|
insert into t1 values('-800:10:10', '2021-10-10 10:10:10.1234', '2021-10-10 10:10:10.1234', '2021-10-11', '2021-10-10 10:10:10.1234');
|
|
set @@tidb_enable_vectorized_expression = off;
|
|
select greatest(c_time, c_time) from t1;
|
|
greatest(c_time, c_time)
|
|
-800:10:10.00000
|
|
select greatest(c_dt, c_dt) from t1;
|
|
greatest(c_dt, c_dt)
|
|
2021-10-10 10:10:10.1234
|
|
select greatest(c_ts, c_ts) from t1;
|
|
greatest(c_ts, c_ts)
|
|
2021-10-10 10:10:10.123
|
|
select greatest(c_d, c_d) from t1;
|
|
greatest(c_d, c_d)
|
|
2021-10-11
|
|
select greatest(c_str, c_str) from t1;
|
|
greatest(c_str, c_str)
|
|
2021-10-10 10:10:10.1234
|
|
select least(c_time, c_time) from t1;
|
|
least(c_time, c_time)
|
|
-800:10:10.00000
|
|
select least(c_dt, c_dt) from t1;
|
|
least(c_dt, c_dt)
|
|
2021-10-10 10:10:10.1234
|
|
select least(c_ts, c_ts) from t1;
|
|
least(c_ts, c_ts)
|
|
2021-10-10 10:10:10.123
|
|
select least(c_d, c_d) from t1;
|
|
least(c_d, c_d)
|
|
2021-10-11
|
|
select least(c_str, c_str) from t1;
|
|
least(c_str, c_str)
|
|
2021-10-10 10:10:10.1234
|
|
select greatest(c_time, cast('10:01:01' as time)) from t1;
|
|
greatest(c_time, cast('10:01:01' as time))
|
|
10:01:01.00000
|
|
select least(c_time, cast('10:01:01' as time)) from t1;
|
|
least(c_time, cast('10:01:01' as time))
|
|
-800:10:10.00000
|
|
select greatest(c_d, cast('1999-10-10' as date)) from t1;
|
|
greatest(c_d, cast('1999-10-10' as date))
|
|
2021-10-11
|
|
select least(c_d, cast('1999-10-10' as date)) from t1;
|
|
least(c_d, cast('1999-10-10' as date))
|
|
1999-10-10
|
|
select greatest(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
greatest(c_dt, cast('1999-10-10 10:10:10.1234' as datetime))
|
|
2021-10-10 10:10:10.1234
|
|
select least(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
least(c_dt, cast('1999-10-10 10:10:10.1234' as datetime))
|
|
1999-10-10 10:10:10
|
|
set @@tidb_enable_vectorized_expression = on;
|
|
select greatest(c_time, c_time) from t1;
|
|
greatest(c_time, c_time)
|
|
-800:10:10.00000
|
|
select greatest(c_dt, c_dt) from t1;
|
|
greatest(c_dt, c_dt)
|
|
2021-10-10 10:10:10.1234
|
|
select greatest(c_ts, c_ts) from t1;
|
|
greatest(c_ts, c_ts)
|
|
2021-10-10 10:10:10.123
|
|
select greatest(c_d, c_d) from t1;
|
|
greatest(c_d, c_d)
|
|
2021-10-11
|
|
select greatest(c_str, c_str) from t1;
|
|
greatest(c_str, c_str)
|
|
2021-10-10 10:10:10.1234
|
|
select least(c_time, c_time) from t1;
|
|
least(c_time, c_time)
|
|
-800:10:10.00000
|
|
select least(c_dt, c_dt) from t1;
|
|
least(c_dt, c_dt)
|
|
2021-10-10 10:10:10.1234
|
|
select least(c_ts, c_ts) from t1;
|
|
least(c_ts, c_ts)
|
|
2021-10-10 10:10:10.123
|
|
select least(c_d, c_d) from t1;
|
|
least(c_d, c_d)
|
|
2021-10-11
|
|
select least(c_str, c_str) from t1;
|
|
least(c_str, c_str)
|
|
2021-10-10 10:10:10.1234
|
|
select greatest(c_time, cast('10:01:01' as time)) from t1;
|
|
greatest(c_time, cast('10:01:01' as time))
|
|
10:01:01.00000
|
|
select least(c_time, cast('10:01:01' as time)) from t1;
|
|
least(c_time, cast('10:01:01' as time))
|
|
-800:10:10.00000
|
|
select greatest(c_d, cast('1999-10-10' as date)) from t1;
|
|
greatest(c_d, cast('1999-10-10' as date))
|
|
2021-10-11
|
|
select least(c_d, cast('1999-10-10' as date)) from t1;
|
|
least(c_d, cast('1999-10-10' as date))
|
|
1999-10-10
|
|
select greatest(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
greatest(c_dt, cast('1999-10-10 10:10:10.1234' as datetime))
|
|
2021-10-10 10:10:10.1234
|
|
select least(c_dt, cast('1999-10-10 10:10:10.1234' as datetime)) from t1;
|
|
least(c_dt, cast('1999-10-10 10:10:10.1234' as datetime))
|
|
1999-10-10 10:10:10
|
|
set @@tidb_enable_vectorized_expression = default;
|
|
drop table if exists t1;
|
|
create table t1 (d date);
|
|
set sql_mode='traditional';
|
|
insert into t1 (d) select date_add('2000-01-01',interval 8000 year);
|
|
Error 1441 (22008): Datetime function: datetime field overflow
|
|
insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR);
|
|
Error 1441 (22008): Datetime function: datetime field overflow
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 year);
|
|
Error 1441 (22008): Datetime function: datetime field overflow
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 day);
|
|
Error 1441 (22008): Datetime function: datetime field overflow
|
|
set sql_mode='';
|
|
insert into t1 (d) select date_add('2000-01-01',interval 8000 year);
|
|
insert into t1 (d) select date_sub('2000-01-01', INTERVAL 2001 YEAR);
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 year);
|
|
insert into t1 (d) select date_add('9999-12-31',interval 1 day);
|
|
select * from t1;
|
|
d
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
set sql_mode=default;
|
|
select "1000-01-01 00:00:00" + INTERVAL 9223372036854775808 day;
|
|
"1000-01-01 00:00:00" + INTERVAL 9223372036854775808 day
|
|
NULL
|
|
select "1000-01-01 00:00:00" + INTERVAL 18446744073709551616 day;
|
|
"1000-01-01 00:00:00" + INTERVAL 18446744073709551616 day
|
|
NULL
|
|
drop table if exists t1;
|
|
create table t1(a decimal(65, 2));
|
|
insert into t1 (a) values (1), (1.4), (1.5), (1.6), (-1), (-1.4), (-1.5), (-1.6), (-1000.5);
|
|
insert into t1 (a) values (315600000000000000), (9223372036854775808), (18446744073709551615), (18446744073709551616), (-9223372036854775808), (-9223372036854775809);
|
|
set @@tidb_enable_vectorized_expression=0;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a YEAR from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL a YEAR
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 NULL
|
|
-1.60 0998-01-01 00:00:00
|
|
-1.50 0998-01-01 00:00:00
|
|
-1.40 0999-01-01 00:00:00
|
|
-1.00 0999-01-01 00:00:00
|
|
1.00 1001-01-01 00:00:00
|
|
1.40 1001-01-01 00:00:00
|
|
1.50 1002-01-01 00:00:00
|
|
1.60 1002-01-01 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MINUTE from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL a MINUTE
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0999-12-31 07:19:00
|
|
-1.60 0999-12-31 23:58:00
|
|
-1.50 0999-12-31 23:58:00
|
|
-1.40 0999-12-31 23:59:00
|
|
-1.00 0999-12-31 23:59:00
|
|
1.00 1000-01-01 00:01:00
|
|
1.40 1000-01-01 00:01:00
|
|
1.50 1000-01-01 00:02:00
|
|
1.60 1000-01-01 00:02:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MICROSECOND from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL a MICROSECOND
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0999-12-31 23:59:59.998999
|
|
-1.60 0999-12-31 23:59:59.999998
|
|
-1.50 0999-12-31 23:59:59.999998
|
|
-1.40 0999-12-31 23:59:59.999999
|
|
-1.00 0999-12-31 23:59:59.999999
|
|
1.00 1000-01-01 00:00:00.000001
|
|
1.40 1000-01-01 00:00:00.000001
|
|
1.50 1000-01-01 00:00:00.000002
|
|
1.60 1000-01-01 00:00:00.000002
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as char) DAY from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL cast(a as char) DAY
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0997-04-06 00:00:00
|
|
-1.60 0999-12-31 00:00:00
|
|
-1.50 0999-12-31 00:00:00
|
|
-1.40 0999-12-31 00:00:00
|
|
-1.00 0999-12-31 00:00:00
|
|
1.00 1000-01-02 00:00:00
|
|
1.40 1000-01-02 00:00:00
|
|
1.50 1000-01-02 00:00:00
|
|
1.60 1000-01-02 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as signed) DAY from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL cast(a as signed) DAY
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0997-04-05 00:00:00
|
|
-1.60 0999-12-30 00:00:00
|
|
-1.50 0999-12-30 00:00:00
|
|
-1.40 0999-12-31 00:00:00
|
|
-1.00 0999-12-31 00:00:00
|
|
1.00 1000-01-02 00:00:00
|
|
1.40 1000-01-02 00:00:00
|
|
1.50 1000-01-03 00:00:00
|
|
1.60 1000-01-03 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as unsigned) DAY from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL cast(a as unsigned) DAY
|
|
-9223372036854775809.00 1000-01-01 00:00:00
|
|
-9223372036854775808.00 1000-01-01 00:00:00
|
|
-1000.50 1000-01-01 00:00:00
|
|
-1.60 1000-01-01 00:00:00
|
|
-1.50 1000-01-01 00:00:00
|
|
-1.40 1000-01-01 00:00:00
|
|
-1.00 1000-01-01 00:00:00
|
|
1.00 1000-01-02 00:00:00
|
|
1.40 1000-01-02 00:00:00
|
|
1.50 1000-01-03 00:00:00
|
|
1.60 1000-01-03 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
set @@tidb_enable_vectorized_expression=1;
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a YEAR from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL a YEAR
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 NULL
|
|
-1.60 0998-01-01 00:00:00
|
|
-1.50 0998-01-01 00:00:00
|
|
-1.40 0999-01-01 00:00:00
|
|
-1.00 0999-01-01 00:00:00
|
|
1.00 1001-01-01 00:00:00
|
|
1.40 1001-01-01 00:00:00
|
|
1.50 1002-01-01 00:00:00
|
|
1.60 1002-01-01 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MINUTE from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL a MINUTE
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0999-12-31 07:19:00
|
|
-1.60 0999-12-31 23:58:00
|
|
-1.50 0999-12-31 23:58:00
|
|
-1.40 0999-12-31 23:59:00
|
|
-1.00 0999-12-31 23:59:00
|
|
1.00 1000-01-01 00:01:00
|
|
1.40 1000-01-01 00:01:00
|
|
1.50 1000-01-01 00:02:00
|
|
1.60 1000-01-01 00:02:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL a MICROSECOND from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL a MICROSECOND
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0999-12-31 23:59:59.998999
|
|
-1.60 0999-12-31 23:59:59.999998
|
|
-1.50 0999-12-31 23:59:59.999998
|
|
-1.40 0999-12-31 23:59:59.999999
|
|
-1.00 0999-12-31 23:59:59.999999
|
|
1.00 1000-01-01 00:00:00.000001
|
|
1.40 1000-01-01 00:00:00.000001
|
|
1.50 1000-01-01 00:00:00.000002
|
|
1.60 1000-01-01 00:00:00.000002
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as char) DAY from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL cast(a as char) DAY
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0997-04-06 00:00:00
|
|
-1.60 0999-12-31 00:00:00
|
|
-1.50 0999-12-31 00:00:00
|
|
-1.40 0999-12-31 00:00:00
|
|
-1.00 0999-12-31 00:00:00
|
|
1.00 1000-01-02 00:00:00
|
|
1.40 1000-01-02 00:00:00
|
|
1.50 1000-01-02 00:00:00
|
|
1.60 1000-01-02 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as signed) DAY from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL cast(a as signed) DAY
|
|
-9223372036854775809.00 NULL
|
|
-9223372036854775808.00 NULL
|
|
-1000.50 0997-04-05 00:00:00
|
|
-1.60 0999-12-30 00:00:00
|
|
-1.50 0999-12-30 00:00:00
|
|
-1.40 0999-12-31 00:00:00
|
|
-1.00 0999-12-31 00:00:00
|
|
1.00 1000-01-02 00:00:00
|
|
1.40 1000-01-02 00:00:00
|
|
1.50 1000-01-03 00:00:00
|
|
1.60 1000-01-03 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
select a, "1000-01-01 00:00:00" + INTERVAL cast(a as unsigned) DAY from t1 order by a ASC;
|
|
a "1000-01-01 00:00:00" + INTERVAL cast(a as unsigned) DAY
|
|
-9223372036854775809.00 1000-01-01 00:00:00
|
|
-9223372036854775808.00 1000-01-01 00:00:00
|
|
-1000.50 1000-01-01 00:00:00
|
|
-1.60 1000-01-01 00:00:00
|
|
-1.50 1000-01-01 00:00:00
|
|
-1.40 1000-01-01 00:00:00
|
|
-1.00 1000-01-01 00:00:00
|
|
1.00 1000-01-02 00:00:00
|
|
1.40 1000-01-02 00:00:00
|
|
1.50 1000-01-03 00:00:00
|
|
1.60 1000-01-03 00:00:00
|
|
315600000000000000.00 NULL
|
|
9223372036854775808.00 NULL
|
|
18446744073709551615.00 NULL
|
|
18446744073709551616.00 NULL
|
|
create table t2(a decimal(65, 2), d datetime);
|
|
set @old_sql_mode=@@sql_mode;
|
|
set @@sql_mode='';
|
|
insert into t2 values('1', "1000-01-01 00:00:00" + INTERVAL "+1" YEAR);
|
|
insert into t2 values('-1', "1000-01-01 00:00:00" + INTERVAL "-1" YEAR);
|
|
insert into t2 values('0', "1000-01-01 00:00:00" + INTERVAL "XXX" YEAR);
|
|
insert into t2 values('99999', "1000-01-01 00:00:00" + INTERVAL 99999 YEAR);
|
|
insert into t2 values('116777216', "1000-01-01 00:00:00" + INTERVAL 116777216 YEAR);
|
|
insert into t2 values('9223372036854775809', "1000-01-01 00:00:00" + INTERVAL 9223372036854775808 YEAR);
|
|
insert into t2 values('18446744073709551616', "1000-01-01 00:00:00" + INTERVAL 18446744073709551616 YEAR);
|
|
insert into t2 values('-9223372036854775809', "1000-01-01 00:00:00" + INTERVAL -9223372036854775809 YEAR);
|
|
select a, d from t2 order by a ASC;
|
|
a d
|
|
-9223372036854775809.00 NULL
|
|
-1.00 0999-01-01 00:00:00
|
|
0.00 1000-01-01 00:00:00
|
|
1.00 1001-01-01 00:00:00
|
|
99999.00 NULL
|
|
116777216.00 NULL
|
|
9223372036854775809.00 NULL
|
|
18446744073709551616.00 NULL
|
|
truncate table t2;
|
|
set @@sql_mode=@old_sql_mode;
|
|
insert into t2 values('1', "1000-01-01 00:00:00" + INTERVAL "+1" YEAR);
|
|
insert into t2 values('-1', "1000-01-01 00:00:00" + INTERVAL "-1" YEAR);
|
|
insert into t2 values('0', "1000-01-01 00:00:00" + INTERVAL "XXX" YEAR);
|
|
Error 1292 (22007): Truncated incorrect DECIMAL value: 'XXX'
|
|
insert into t2 values('99999', "1000-01-01 00:00:00" + INTERVAL 99999 YEAR);
|
|
Error 1441 (22008): Datetime function: datetime field overflow
|
|
insert into t2 values('116777216', "1000-01-01 00:00:00" + INTERVAL 116777216 YEAR);
|
|
Error 1441 (22008): Datetime function: datetime field overflow
|
|
insert into t2 values('9223372036854775809', "1000-01-01 00:00:00" + INTERVAL 9223372036854775808 YEAR);
|
|
Error 1292 (22007): Incorrect datetime value: '9223372036854775808'
|
|
insert into t2 values('18446744073709551616', "1000-01-01 00:00:00" + INTERVAL 18446744073709551616 YEAR);
|
|
Error 1292 (22007): Truncated incorrect DECIMAL value: '18446744073709551616'
|
|
insert into t2 values('-9223372036854775809', "1000-01-01 00:00:00" + INTERVAL -9223372036854775809 YEAR);
|
|
Error 1292 (22007): Truncated incorrect DECIMAL value: '-9223372036854775809'
|
|
select a, d from t2 order by a ASC;
|
|
a d
|
|
-1.00 0999-01-01 00:00:00
|
|
1.00 1001-01-01 00:00:00
|
|
drop table if exists t1;
|
|
drop table if exists t;
|
|
create table t(col0 date, col1 time, col2 varchar(30));
|
|
insert into t values('2024-11-01', '12:00:01.341300', '12:00:01.341300'), ('2024-11-01', '1 12:00:01.341300', '1 12:00:01.341300'), ('2024-11-01', '-1 12:00:01.341300', '-1 12:00:01.341300'),('1000-01-01', '12:00:01.341300', '12:00:01.341300'), ('9999-12-31', '12:00:01.341300', '12:00:01.341300'), (null, '12:00:01.341300', '12:00:01.341300'), ('2020-11-01', null, null);
|
|
select addtime(date '2024-11-01', '12:00:01.341300');
|
|
addtime(date '2024-11-01', '12:00:01.341300')
|
|
2024-11-01 12:00:01.341300
|
|
select subtime(date '2024-11-01', '12:00:01.341300');
|
|
subtime(date '2024-11-01', '12:00:01.341300')
|
|
2024-10-31 11:59:58.658700
|
|
select addtime(date '2024-11-01', '1 12:00:01.341300');
|
|
addtime(date '2024-11-01', '1 12:00:01.341300')
|
|
2024-11-02 12:00:01.341300
|
|
select subtime(date '2024-11-01', '1 12:00:01.341300');
|
|
subtime(date '2024-11-01', '1 12:00:01.341300')
|
|
2024-10-30 11:59:58.658700
|
|
select addtime(date '2024-11-01', '-1 12:00:01.341300');
|
|
addtime(date '2024-11-01', '-1 12:00:01.341300')
|
|
2024-10-30 11:59:58.658700
|
|
select subtime(date '2024-11-01', '-1 12:00:01.341300');
|
|
subtime(date '2024-11-01', '-1 12:00:01.341300')
|
|
2024-11-02 12:00:01.341300
|
|
select addtime(date '2024-11-01', time '12:00:01.341300');
|
|
addtime(date '2024-11-01', time '12:00:01.341300')
|
|
2024-11-01 12:00:01.341300
|
|
select subtime(date '2024-11-01', time '12:00:01.341300');
|
|
subtime(date '2024-11-01', time '12:00:01.341300')
|
|
2024-10-31 11:59:58.658700
|
|
select addtime(date '2024-11-01', time '1 12:00:01.341300');
|
|
addtime(date '2024-11-01', time '1 12:00:01.341300')
|
|
2024-11-02 12:00:01.341300
|
|
select subtime(date '2024-11-01', time '1 12:00:01.341300');
|
|
subtime(date '2024-11-01', time '1 12:00:01.341300')
|
|
2024-10-30 11:59:58.658700
|
|
select addtime(date '2024-11-01', time '-1 12:00:01.341300');
|
|
addtime(date '2024-11-01', time '-1 12:00:01.341300')
|
|
2024-10-30 11:59:58.658700
|
|
select subtime(date '2024-11-01', time '-1 12:00:01.341300');
|
|
subtime(date '2024-11-01', time '-1 12:00:01.341300')
|
|
2024-11-02 12:00:01.341300
|
|
select addtime(col0, '12:00:01.341300') from t order by col0, col1 asc;
|
|
addtime(col0, '12:00:01.341300')
|
|
NULL
|
|
1000-01-01 12:00:01.341300
|
|
2020-11-01 12:00:01.341300
|
|
2024-11-01 12:00:01.341300
|
|
2024-11-01 12:00:01.341300
|
|
2024-11-01 12:00:01.341300
|
|
9999-12-31 12:00:01.341300
|
|
select subtime(col0, '12:00:01.341300') from t order by col0, col1 asc;
|
|
subtime(col0, '12:00:01.341300')
|
|
NULL
|
|
0999-12-31 11:59:58.658700
|
|
2020-10-31 11:59:58.658700
|
|
2024-10-31 11:59:58.658700
|
|
2024-10-31 11:59:58.658700
|
|
2024-10-31 11:59:58.658700
|
|
9999-12-30 11:59:58.658700
|
|
select addtime(col0, '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
addtime(col0, '1 12:00:01.341300')
|
|
NULL
|
|
1000-01-02 12:00:01.341300
|
|
2020-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
NULL
|
|
select subtime(col0, '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
subtime(col0, '1 12:00:01.341300')
|
|
NULL
|
|
0999-12-30 11:59:58.658700
|
|
2020-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
9999-12-29 11:59:58.658700
|
|
select addtime(col0, '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
addtime(col0, '-1 12:00:01.341300')
|
|
NULL
|
|
0999-12-30 11:59:58.658700
|
|
2020-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
9999-12-29 11:59:58.658700
|
|
select subtime(col0, '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
subtime(col0, '-1 12:00:01.341300')
|
|
NULL
|
|
1000-01-02 12:00:01.341300
|
|
2020-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
NULL
|
|
select addtime(col0, time '12:00:01.341300') from t order by col0, col1 asc;
|
|
addtime(col0, time '12:00:01.341300')
|
|
NULL
|
|
1000-01-01 12:00:01.341300
|
|
2020-11-01 12:00:01.341300
|
|
2024-11-01 12:00:01.341300
|
|
2024-11-01 12:00:01.341300
|
|
2024-11-01 12:00:01.341300
|
|
9999-12-31 12:00:01.341300
|
|
select subtime(col0, time '12:00:01.341300') from t order by col0, col1 asc;
|
|
subtime(col0, time '12:00:01.341300')
|
|
NULL
|
|
0999-12-31 11:59:58.658700
|
|
2020-10-31 11:59:58.658700
|
|
2024-10-31 11:59:58.658700
|
|
2024-10-31 11:59:58.658700
|
|
2024-10-31 11:59:58.658700
|
|
9999-12-30 11:59:58.658700
|
|
select addtime(col0, time '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
addtime(col0, time '1 12:00:01.341300')
|
|
NULL
|
|
1000-01-02 12:00:01.341300
|
|
2020-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
NULL
|
|
select subtime(col0, time '1 12:00:01.341300') from t order by col0, col1 asc;
|
|
subtime(col0, time '1 12:00:01.341300')
|
|
NULL
|
|
0999-12-30 11:59:58.658700
|
|
2020-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
9999-12-29 11:59:58.658700
|
|
select addtime(col0, time '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
addtime(col0, time '-1 12:00:01.341300')
|
|
NULL
|
|
0999-12-30 11:59:58.658700
|
|
2020-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
9999-12-29 11:59:58.658700
|
|
select subtime(col0, time '-1 12:00:01.341300') from t order by col0, col1 asc;
|
|
subtime(col0, time '-1 12:00:01.341300')
|
|
NULL
|
|
1000-01-02 12:00:01.341300
|
|
2020-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
NULL
|
|
select addtime(col0, col1) from t order by col0, col1 asc;
|
|
addtime(col0, col1)
|
|
NULL
|
|
1000-01-01 12:00:01
|
|
NULL
|
|
2024-10-30 11:59:59
|
|
2024-11-01 12:00:01
|
|
2024-11-02 12:00:01
|
|
9999-12-31 12:00:01
|
|
select subtime(col0, col1) from t order by col0, col1 asc;
|
|
subtime(col0, col1)
|
|
NULL
|
|
0999-12-31 11:59:59
|
|
NULL
|
|
2024-11-02 12:00:01
|
|
2024-10-31 11:59:59
|
|
2024-10-30 11:59:59
|
|
9999-12-30 11:59:59
|
|
select addtime(col0, col2) from t order by col0, col1 asc;
|
|
addtime(col0, col2)
|
|
NULL
|
|
1000-01-01 12:00:01.341300
|
|
NULL
|
|
2024-10-30 11:59:58.658700
|
|
2024-11-01 12:00:01.341300
|
|
2024-11-02 12:00:01.341300
|
|
9999-12-31 12:00:01.341300
|
|
select subtime(col0, col2) from t order by col0, col1 asc;
|
|
subtime(col0, col2)
|
|
NULL
|
|
0999-12-31 11:59:58.658700
|
|
NULL
|
|
2024-11-02 12:00:01.341300
|
|
2024-10-31 11:59:58.658700
|
|
2024-10-30 11:59:58.658700
|
|
9999-12-30 11:59:58.658700
|
|
select addtime(col0, null) from t order by col0, col1 asc;
|
|
addtime(col0, null)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
select subtime(col0, null) from t order by col0, col1 asc;
|
|
subtime(col0, null)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
select addtime(null, col1) from t order by col0, col1 asc;
|
|
addtime(null, col1)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
select subtime(null, col1) from t order by col0, col1 asc;
|
|
subtime(null, col1)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
select addtime(null, col2) from t order by col0, col1 asc;
|
|
addtime(null, col2)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
select subtime(null, col2) from t order by col0, col1 asc;
|
|
subtime(null, col2)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
NULL
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(date '0-0-0', '12:00:01.341300');
|
|
addtime(date '0-0-0', '12:00:01.341300')
|
|
NULL
|
|
select addtime(date '0-0-0', time '12:00:01.341300');
|
|
addtime(date '0-0-0', time '12:00:01.341300')
|
|
NULL
|
|
select addtime(date '0-0-0', '1 12:00:01.341300');
|
|
addtime(date '0-0-0', '1 12:00:01.341300')
|
|
NULL
|
|
select addtime(date '0-0-0', time '1 12:00:01.341300');
|
|
addtime(date '0-0-0', time '1 12:00:01.341300')
|
|
NULL
|
|
select subtime(date '0-0-0', '12:00:01.341300');
|
|
subtime(date '0-0-0', '12:00:01.341300')
|
|
NULL
|
|
select subtime(date '0-0-0', time '12:00:01.341300');
|
|
subtime(date '0-0-0', time '12:00:01.341300')
|
|
NULL
|
|
select subtime(date '0-0-0', '1 12:00:01.341300');
|
|
subtime(date '0-0-0', '1 12:00:01.341300')
|
|
NULL
|
|
select subtime(date '0-0-0', time '1 12:00:01.341300');
|
|
subtime(date '0-0-0', time '1 12:00:01.341300')
|
|
NULL
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t;
|
|
create table t(col0 date, col1 time, col2 varchar(30));
|
|
insert into t values ('0-0-0', '1:1:1', '1:1:1'), ('0-0-0', '0:0:0', '0:0:0'), ('2024-0-1', '1:1:1', '1:1:1'), ('2020-1-1', '-1 1:1:1', '-1 1:1:1'), ('2024-1-0', '1:1:1', '1:1:1'), ('2024-0-1', '1:1:1', '1:1:1');
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t;
|
|
create table t(col0 datetime, col1 time, col2 varchar(30));
|
|
insert into t values ('0-0-0', '1:1:1', '1:1:1'), ('0-0-0', '0:0:0', '0:0:0'), ('2024-0-1', '1:1:1', '1:1:1'), ('2020-1-1', '-1 1:1:1', '-1 1:1:1'), ('2024-1-0', '1:1:1', '1:1:1'), ('2024-0-1', '1:1:1', '1:1:1');
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
|
|
select addtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col1) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col1)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|
|
select addtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
addtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2019-12-30 22:58:59
|
|
2023-12-01 01:01:01
|
|
2023-12-01 01:01:01
|
|
2023-12-31 01:01:01
|
|
select subtime(col0, col2) from t order by col0, col1, col2 asc;
|
|
subtime(col0, col2)
|
|
NULL
|
|
NULL
|
|
2020-01-02 01:01:01
|
|
2023-11-30 22:58:59
|
|
2023-11-30 22:58:59
|
|
2023-12-30 22:58:59
|