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

     1  SELECT TIMESTAMPDIFF( MICROSECOND, '2017-12-01 12:15:12','2018-01-01 7:18:20');
     2  SELECT TIMESTAMPDIFF( SECOND, '2017-12-01 12:15:12','2018-01-01 7:18:20');
     3  SELECT TIMESTAMPDIFF( MINUTE, '2017-12-01 12:15:12','2018-01-01 7:18:20');
     4  SELECT TIMESTAMPDIFF( HOUR, '2017-12-01 12:15:12','2018-01-01 7:18:20');
     5  SELECT TIMESTAMPDIFF( DAY, '2017-12-01 12:15:12','2018-01-01 7:18:20');
     6  SELECT TIMESTAMPDIFF( WEEK, '2018-01-01 12:15:12','2018-01-08 12:15:12');
     7  SELECT TIMESTAMPDIFF( MONTH, '2017-11-01 12:15:12','2018-01-01 12:15:12');
     8  SELECT TIMESTAMPDIFF( QUARTER,  '2017-01-01 12:15:12','2018-01-01 12:15:12');
     9  SELECT TIMESTAMPDIFF( YEAR, '2017-01-01 12:15:12','2018-01-01 12:15:12');
    10  
    11  SELECT TIMESTAMPDIFF( MICROSECOND, '2018-01-01 7:18:20','2017-12-01 12:15:12');
    12  SELECT TIMESTAMPDIFF( SECOND, '2018-01-01 7:18:20', '2017-12-01 12:15:12');
    13  SELECT TIMESTAMPDIFF( MINUTE, '2018-01-01 7:18:20','2017-12-01 12:15:12');
    14  SELECT TIMESTAMPDIFF( HOUR, '2018-01-01 7:18:20','2017-12-01 12:15:12');
    15  SELECT TIMESTAMPDIFF( DAY, '2018-01-01 7:18:20', '2017-12-01 12:15:12');
    16  SELECT TIMESTAMPDIFF( WEEK, '2018-01-08 12:15:12', '2018-01-01 12:15:12');
    17  SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2017-11-01 12:15:12');
    18  SELECT TIMESTAMPDIFF( QUARTER,  '2018-01-01 12:15:12', '2017-01-01 12:15:12');
    19  SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2017-01-01 12:15:12');
    20  
    21  SELECT TIMESTAMPDIFF( MICROSECOND, NULL,'2017-12-01 12:15:12');
    22  SELECT TIMESTAMPDIFF( SECOND, '2018-01-01 7:18:20', NULL);
    23  SELECT TIMESTAMPDIFF( HOUR, NULL,'2017-12-01 12:15:12');
    24  SELECT TIMESTAMPDIFF( DAY, '2018-01-01 7:18:20', NULL);
    25  SELECT TIMESTAMPDIFF( MONTH, NULL, '2017-11-01 12:15:12');
    26  SELECT TIMESTAMPDIFF( QUARTER,  '2018-01-01 12:15:12', NULL);
    27  
    28  SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2019-11-01 12:15:12');
    29  SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2019-10-01 12:15:12');
    30  SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2020-10-01 12:15:12');
    31  SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2021-11-01 12:15:12');
    32  SELECT TIMESTAMPDIFF( MONTH, '2018-01-01 12:15:12', '2022-01-01 12:15:12');
    33  SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2019-11-01 12:15:12');
    34  SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2019-10-01 12:15:12');
    35  SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2020-10-01 12:15:12');
    36  SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2021-11-01 12:15:12');
    37  SELECT TIMESTAMPDIFF( QUARTER, '2018-01-01 12:15:12', '2022-01-01 12:15:12');
    38  SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2019-11-01 12:15:12');
    39  SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2019-10-01 12:15:12');
    40  SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2020-10-01 12:15:12');
    41  SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2021-11-01 12:15:12');
    42  SELECT TIMESTAMPDIFF( YEAR, '2018-01-01 12:15:12', '2022-01-01 12:15:12');
    43  
    44  SELECT TIMESTAMPDIFF( MONTH, '2019-11-01 12:15:12', '2018-01-01 12:15:12');
    45  SELECT TIMESTAMPDIFF( MONTH, '2019-10-01 12:15:12', '2018-01-01 12:15:12');
    46  SELECT TIMESTAMPDIFF( MONTH, '2020-10-01 12:15:12', '2018-01-01 12:15:12');
    47  SELECT TIMESTAMPDIFF( MONTH, '2021-11-01 12:15:12', '2018-01-01 12:15:12');
    48  SELECT TIMESTAMPDIFF( MONTH, '2022-01-01 12:15:12', '2018-01-01 12:15:12');
    49  SELECT TIMESTAMPDIFF( QUARTER, '2019-11-01 12:15:12', '2018-01-01 12:15:12');
    50  SELECT TIMESTAMPDIFF( QUARTER, '2019-10-01 12:15:12', '2018-01-01 12:15:12');
    51  SELECT TIMESTAMPDIFF( QUARTER, '2020-10-01 12:15:12', '2018-01-01 12:15:12');
    52  SELECT TIMESTAMPDIFF( QUARTER, '2021-11-01 12:15:12', '2018-01-01 12:15:12');
    53  SELECT TIMESTAMPDIFF( QUARTER, '2022-01-01 12:15:12', '2018-01-01 12:15:12');
    54  SELECT TIMESTAMPDIFF( YEAR, '2019-11-01 12:15:12', '2018-01-01 12:15:12');
    55  SELECT TIMESTAMPDIFF( YEAR, '2019-10-01 12:15:12', '2018-01-01 12:15:12');
    56  SELECT TIMESTAMPDIFF( YEAR, '2020-10-01 12:15:12', '2018-01-01 12:15:12');
    57  SELECT TIMESTAMPDIFF( YEAR, '2021-11-01 12:15:12', '2018-01-01 12:15:12');
    58  SELECT TIMESTAMPDIFF( YEAR, '2022-01-01 12:15:12', '2018-01-01 12:15:12');
    59  
    60  drop table if exists t1;
    61  create table t1(a date,  b date);
    62  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
    63  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
    64  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
    65  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
    66  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
    67  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
    68  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
    69  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
    70  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
    71  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
    72  SELECT a, b, TIMESTAMPDIFF(MICROSECOND, a, b) from t1;
    73  drop table t1;
    74  
    75  drop table if exists t1;
    76  create table t1(a date,  b date);
    77  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
    78  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
    79  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
    80  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
    81  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
    82  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
    83  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
    84  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
    85  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
    86  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
    87  SELECT a, b, TIMESTAMPDIFF(SECOND, a, b) from t1;
    88  drop table t1;
    89  
    90  drop table if exists t1;
    91  create table t1(a date,  b date);
    92  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
    93  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
    94  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
    95  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
    96  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
    97  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
    98  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
    99  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   100  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   101  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   102  SELECT a, b, TIMESTAMPDIFF(MINUTE, a, b) from t1;
   103  drop table t1;
   104  
   105  drop table if exists t1;
   106  create table t1(a date,  b date);
   107  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
   108  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
   109  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
   110  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
   111  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
   112  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
   113  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
   114  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   115  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   116  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   117  SELECT a, b, TIMESTAMPDIFF(HOUR, a, b) from t1;
   118  drop table t1;
   119  
   120  drop table if exists t1;
   121  create table t1(a date,  b date);
   122  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
   123  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
   124  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
   125  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
   126  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
   127  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
   128  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
   129  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   130  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   131  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   132  SELECT a, b, TIMESTAMPDIFF(DAY, a, b) from t1;
   133  drop table t1;
   134  
   135  drop table if exists t1;
   136  create table t1(a date,  b date);
   137  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
   138  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
   139  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
   140  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
   141  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
   142  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
   143  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
   144  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   145  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   146  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   147  SELECT a, b, TIMESTAMPDIFF(WEEK, a, b) from t1;
   148  drop table t1;
   149  
   150  drop table if exists t1;
   151  create table t1(a date,  b date);
   152  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
   153  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
   154  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
   155  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
   156  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
   157  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
   158  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
   159  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   160  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   161  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   162  SELECT a, b, TIMESTAMPDIFF(MONTH, a, b) from t1;
   163  drop table t1;
   164  
   165  drop table if exists t1;
   166  create table t1(a date,  b date);
   167  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
   168  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
   169  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
   170  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
   171  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
   172  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
   173  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
   174  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   175  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   176  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   177  SELECT a, b, TIMESTAMPDIFF(QUARTER, a, b) from t1;
   178  drop table t1;
   179  
   180  drop table if exists t1;
   181  create table t1(a date,  b date);
   182  insert into t1 values('2019-11-01 12:15:12', '2018-01-01 12:15:12');
   183  insert into t1 values('2019-10-01 12:15:12', '2018-01-01 12:15:12');
   184  insert into t1 values('2020-10-01 12:15:12', '2018-01-01 12:15:12');
   185  insert into t1 values('2021-11-01 12:15:12', '2018-01-01 12:15:12');
   186  insert into t1 values('2022-01-01 12:15:12', '2018-01-01 12:15:12');
   187  insert into t1 values('2018-01-01 12:15:12', '2019-11-01 12:15:12');
   188  insert into t1 values( '2018-01-01 12:15:12', '2019-10-01 12:15:12');
   189  insert into t1 values( '2018-01-01 12:15:12', '2020-10-01 12:15:12');
   190  insert into t1 values( '2018-01-01 12:15:12', '2021-11-01 12:15:12');
   191  insert into t1 values( '2018-01-01 12:15:12', '2022-01-01 12:15:12');
   192  SELECT a, b, TIMESTAMPDIFF(YEAR, a, b) from t1;
   193  drop table t1;
   194  
   195  drop table if exists t1;
   196  create table t1(a date,  b date);
   197  insert into t1 values('2019-11-01 12:15:12', '2019-11-01 12:15:12');
   198  insert into t1 values('2019-10-01 12:15:12', '2019-10-01 12:15:12');
   199  insert into t1 values('2020-10-01 12:15:12', '2020-10-01 12:15:12');
   200  insert into t1 values('2021-11-01 12:15:12', '2021-11-01 12:15:12');
   201  insert into t1 values('2022-01-01 12:15:12', '2022-01-01 12:15:12');
   202  insert into t1 values('2018-01-01 12:15:12', '2018-01-01 12:15:12');
   203  insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12');
   204  insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12');
   205  insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12');
   206  insert into t1 values( '2018-01-01 12:15:12', '2018-01-01 12:15:12');
   207  SELECT a, b, TIMESTAMPDIFF(YEAR, a, b) from t1;
   208  drop table t1;