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

     1  #SELECT
     2  set time_zone="+00:00";
     3  
     4  select from_unixtime(2147483647);
     5  select from_unixtime(2147483648);
     6  select from_unixtime(0);
     7  
     8  select from_unixtime(-1);
     9  select from_unixtime(-2147483648);
    10  
    11  
    12  #嵌套
    13  select unix_timestamp(from_unixtime(2147483647));
    14  select unix_timestamp(from_unixtime(2147483648));
    15  
    16  # EXTREME VALUE
    17  # bad year
    18  select unix_timestamp('2039-01-20 01:00:00');
    19  select unix_timestamp('1968-01-20 01:00:00');
    20  # bad month
    21  select unix_timestamp('2038-02-10 01:00:00');
    22  select unix_timestamp('1969-11-20 01:00:00');
    23  # bad day
    24  select unix_timestamp('2038-01-20 01:00:00');
    25  select unix_timestamp('1969-12-30 01:00:00');
    26  #
    27  # Check negative shift (we subtract several days for boundary dates during
    28  # conversion).
    29  select unix_timestamp('2038-01-17 12:00:00');
    30  #
    31  # Check positive shift. (it happens only on
    32  # platfroms with unsigned time_t, such as QNX)
    33  #
    34  select unix_timestamp('1970-01-01 03:00:01');
    35  # check bad date, close to the boundary (we cut them off in the very end)
    36  select unix_timestamp('2038-01-19 07:14:07');
    37  
    38  #time zone
    39  set time_zone="+01:00";
    40  select unix_timestamp('1970-01-01 01:00:00'),
    41  unix_timestamp('1970-01-01 01:00:01'),
    42  unix_timestamp('2038-01-19 04:14:07'),
    43  unix_timestamp('2038-01-19 04:14:08'),
    44  unix_timestamp('2021-02-29 04:14:08');
    45  
    46  SET time_zone='+00:00';
    47  CREATE TABLE t1 (a DECIMAL(20,7));
    48  INSERT INTO t1 VALUES
    49  (32536771199.999999),
    50  (32536771199.9999990),
    51  (32536771199.9999991),
    52  (32536771199.9999992),
    53  (32536771199.9999993),
    54  (32536771199.9999994),
    55  (32536771199.9999995),
    56  (32536771199.9999996),
    57  (32536771199.9999997),
    58  (32536771199.9999998),
    59  (32536771199.9999999),
    60  (32536771199.1234567),
    61  (2147483648.1234567),
    62  (1447430881.1234567),
    63  (1451606400.123456),
    64  (2147483647.123456),
    65  (2147483647.999999),
    66  (2147483647.9999999);
    67  SELECT a, FROM_UNIXTIME(a) FROM t1;
    68  DROP TABLE t1;
    69  
    70  SET time_zone='+00:00';
    71  SELECT
    72  FROM_UNIXTIME(2147483647) AS c1,
    73  FROM_UNIXTIME(2147483648) AS c2,
    74  FROM_UNIXTIME(2147483647.9999999) AS c3,
    75  FROM_UNIXTIME(32536771199) AS c4,
    76  FROM_UNIXTIME(32536771199.9999999) AS c5;
    77  
    78  #SET TIMESTAMP=0;
    79  SET time_zone = '+08:00';
    80  
    81  SELECT FROM_UNIXTIME(32536771199);
    82  SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
    83  SELECT FROM_UNIXTIME(32536771200);
    84  
    85  SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
    86  SET time_zone = "+00:00";
    87  SELECT FROM_UNIXTIME(32536771200);
    88  SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00');
    89  SET time_zone = "+01:00";
    90  # East of UTC
    91  SELECT FROM_UNIXTIME(32536771199);
    92  SELECT UNIX_TIMESTAMP('3001-01-19 00:59:59');
    93  SELECT FROM_UNIXTIME(32536771200);
    94  SELECT UNIX_TIMESTAMP('3001-01-19 01:00:00');
    95  SET time_zone = "+09:00";
    96  SELECT FROM_UNIXTIME(32536771199);
    97  SELECT UNIX_TIMESTAMP("3001-01-19 08:59:59");
    98  SELECT UNIX_TIMESTAMP("3001-01-19 09:00:00");
    99  SET time_zone = "-01:00";
   100  # West of UTC
   101  SELECT FROM_UNIXTIME(32536771199);
   102  SELECT UNIX_TIMESTAMP('3001-01-18 22:59:59');
   103  # Overflow:
   104  SELECT FROM_UNIXTIME(32536771200);
   105  SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59');
   106  SET TIME_ZONE = "-07:00";
   107  SELECT FROM_UNIXTIME(32536771199);
   108  SELECT UNIX_TIMESTAMP('3001-01-18 15:59:59');
   109  SELECT UNIX_TIMESTAMP('3001-01-18 16:00:00');
   110  #Extreme values
   111  SELECT FROM_UNIXTIME(9223372036854775807);
   112  SELECT FROM_UNIXTIME(-9223372036854775808);
   113  SELECT FROM_UNIXTIME(9223372036854775808);
   114  
   115  SELECT FROM_UNIXTIME(99999999999999999999999999999999999999999999999999999999999999999);
   116  select count(unix_timestamp());