github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_timediff.test (about)

     1  #time
     2  select timediff(cast('22:22:22' as time), cast('11:11:11' as time));
     3  select timediff(cast('22:22:22' as time), cast('-11:11:11' as time));
     4  select timediff(cast('-22:22:22' as time), cast('11:11:11' as time));
     5  select timediff(cast('-22:22:22' as time), cast('-11:11:11' as time));
     6  select timediff(cast('11:11:11' as time) , cast('22:22:22' as time));
     7  select timediff(cast('11:11:11' as time) , cast('-22:22:22' as time));
     8  select timediff(cast('-11:11:11' as time) , cast('22:22:22' as time));
     9  select timediff(cast('-11:11:11' as time) , cast('-22:22:22' as time));
    10  select timediff(cast('-838:59:59' as time) , cast('838:59:59' as time));
    11  select timediff(cast('838:59:59' as time) , cast('-838:59:59' as time));
    12  select timediff(cast('838:59:59' as time) , cast('838:59:59' as time));
    13  
    14  #invalid time
    15  select timediff(cast('22:22:22' as time), null);
    16  select timediff(null, cast('11:11:11' as time));
    17  select timediff(null, null);
    18  
    19  #datetime
    20  select timediff(CAST('2017-08-08 22:22:22' as datetime), CAST('2000-01-02 11:00:00' as datetime));
    21  select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-12-12 11:11:11' as datetime));
    22  select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-12-12 22:22:22' as datetime));
    23  select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2000-12-12 11:11:11' as datetime));
    24  select timediff(CAST('2000-12-12 11:11:11' as datetime), CAST('2012-12-12 22:22:22' as datetime));
    25  select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-10-10 11:11:11' as datetime));
    26  select timediff(CAST('2012-10-10 11:11:11' as datetime), CAST('2012-12-12 22:22:22' as datetime));
    27  select timediff(CAST('2012-12-12 22:22:22' as datetime), CAST('2012-12-10 11:11:11' as datetime));
    28  select timediff(CAST('2012-12-10 11:11:11' as datetime), CAST('2012-12-12 22:22:22' as datetime));
    29  select timediff(CAST('2012-12-10 11:11:11' as datetime), CAST('2012-12-10 11:11:11' as datetime));
    30  
    31  #invalid datetime
    32  SELECT TIMEDIFF(CAST('2012-12-12 11:11:11' AS DATETIME), NULL);
    33  SELECT TIMEDIFF(NULL, CAST('2012-12-12 11:11:11' AS DATETIME));
    34  SELECT TIMEDIFF(NULL, NULL);
    35  
    36  #different input types
    37  select timediff(CAST('2017-08-08 22:22:22' as datetime), cast('11:11:11' as time));
    38  select timediff(cast('11:11:11' as time), cast('2017-08-08 22:22:22' as datetime));
    39  select timediff(CAST('2017-08-08 22:22:22' as datetime), 1);
    40  
    41  drop table if exists t1;
    42  create table t1(a INT,  b time);
    43  insert into t1 values(1, '22:22:22');
    44  insert into t1 values(2, '11:11:11');
    45  insert into t1 values(3, '-22:22:22');
    46  insert into t1 values(4, '-11:11:11');
    47  insert into t1 values(5, '838:59:59');
    48  insert into t1 values(6, '-838:59:59');
    49  insert into t1 values(7, '00:00:00');
    50  select a, timediff(cast('22:22:22' as time), b) from t1;
    51  select a, timediff(b, cast('22:22:22' as time)) from t1;
    52  drop table t1;
    53  
    54  drop table if exists t2;
    55  create table t2(a INT,  b datetime);
    56  insert into t2 values(1, '2012-12-12 23:22:22');
    57  insert into t2 values(2, '2012-12-10 11:11:11');
    58  insert into t2 values(3, '2012-12-14 11:11:11');
    59  insert into t2 values(4, '2012-12-20 11:11:11');
    60  insert into t2 values(2, '2012-10-10 11:11:11');
    61  select a, timediff(cast('2012-12-12 22:22:21' as datetime), b) from t2;
    62  select a, timediff(b, cast('2012-12-12 22:22:21' as datetime)) from t2;
    63  drop table t2;
    64  
    65  select timediff('20',NULL);
    66  select timediff(NULL,'24:59:09');
    67  select timediff('20','24:59:09');
    68  select timediff('12:00','24:59:09');
    69  select timediff('-838:59:59','-1122');
    70  select timediff('12:00:00','24:59:09');
    71  
    72  drop table if exists  time_01;
    73  create table time_01(t1 time,t2 time,t3 time);
    74  insert into time_01 values("-838:59:59.0000","838:59:59.00","22:00:00");
    75  insert into time_01 values("0:00:00.0000","0","0:00");
    76  insert into time_01 values(null,NULL,null);
    77  insert into time_01 values("23","1122","-1122");
    78  insert into time_01 values("101412","4","-101219");
    79  insert into time_01 values("24:59:09.932823","24:02:00.93282332424","24:20:34.00000000");
    80  insert into time_01 values("2022-09-08 12:00:01","019","23403");
    81  select timediff(t1,t2) from time_01;
    82  drop table time_01;