github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_unixtime.result (about) 1 set time_zone="+00:00"; 2 select from_unixtime(2147483647); 3 from_unixtime(2147483647) 4 2038-01-19 03:14:07 5 select from_unixtime(2147483648); 6 from_unixtime(2147483648) 7 2038-01-19 03:14:08 8 select from_unixtime(0); 9 from_unixtime(0) 10 1970-01-01 00:00:00 11 select from_unixtime(-1); 12 from_unixtime(-1) 13 null 14 select from_unixtime(-2147483648); 15 from_unixtime(-2147483648) 16 null 17 select unix_timestamp(from_unixtime(2147483647)); 18 unix_timestamp(from_unixtime(2147483647)) 19 2147483647 20 select unix_timestamp(from_unixtime(2147483648)); 21 unix_timestamp(from_unixtime(2147483648)) 22 2147483648 23 select unix_timestamp('2039-01-20 01:00:00'); 24 unix_timestamp(2039-01-20 01:00:00) 25 2179098000 26 select unix_timestamp('1968-01-20 01:00:00'); 27 unix_timestamp(1968-01-20 01:00:00) 28 null 29 select unix_timestamp('2038-02-10 01:00:00'); 30 unix_timestamp(2038-02-10 01:00:00) 31 2149376400 32 select unix_timestamp('1969-11-20 01:00:00'); 33 unix_timestamp(1969-11-20 01:00:00) 34 null 35 select unix_timestamp('2038-01-20 01:00:00'); 36 unix_timestamp(2038-01-20 01:00:00) 37 2147562000 38 select unix_timestamp('1969-12-30 01:00:00'); 39 unix_timestamp(1969-12-30 01:00:00) 40 null 41 select unix_timestamp('2038-01-17 12:00:00'); 42 unix_timestamp(2038-01-17 12:00:00) 43 2147342400 44 select unix_timestamp('1970-01-01 03:00:01'); 45 unix_timestamp(1970-01-01 03:00:01) 46 10801 47 select unix_timestamp('2038-01-19 07:14:07'); 48 unix_timestamp(2038-01-19 07:14:07) 49 2147498047 50 set time_zone="+01:00"; 51 select unix_timestamp('1970-01-01 01:00:00'), 52 unix_timestamp('1970-01-01 01:00:01'), 53 unix_timestamp('2038-01-19 04:14:07'), 54 unix_timestamp('2038-01-19 04:14:08'), 55 unix_timestamp('2021-02-29 04:14:08'); 56 unix_timestamp(1970-01-01 01:00:00) unix_timestamp(1970-01-01 01:00:01) unix_timestamp(2038-01-19 04:14:07) unix_timestamp(2038-01-19 04:14:08) unix_timestamp(2021-02-29 04:14:08) 57 0 1 2147483647 2147483648 null 58 SET time_zone='+00:00'; 59 CREATE TABLE t1 (a DECIMAL(20,7)); 60 INSERT INTO t1 VALUES 61 (32536771199.999999), 62 (32536771199.9999990), 63 (32536771199.9999991), 64 (32536771199.9999992), 65 (32536771199.9999993), 66 (32536771199.9999994), 67 (32536771199.9999995), 68 (32536771199.9999996), 69 (32536771199.9999997), 70 (32536771199.9999998), 71 (32536771199.9999999), 72 (32536771199.1234567), 73 (2147483648.1234567), 74 (1447430881.1234567), 75 (1451606400.123456), 76 (2147483647.123456), 77 (2147483647.999999), 78 (2147483647.9999999); 79 SELECT a, FROM_UNIXTIME(a) FROM t1; 80 a from_unixtime(a) 81 32536771199.9999990 null 82 32536771199.9999990 null 83 32536771199.9999991 null 84 32536771199.9999992 null 85 32536771199.9999993 null 86 32536771199.9999994 null 87 32536771199.9999995 null 88 32536771199.9999996 null 89 32536771199.9999997 null 90 32536771199.9999998 null 91 32536771199.9999999 null 92 32536771199.1234567 null 93 2147483648.1234567 2038-01-19 03:14:08.123456000 94 1447430881.1234567 2015-11-13 16:08:01.123457000 95 1451606400.1234560 2016-01-01 00:00:00.123456000 96 2147483647.1234560 2038-01-19 03:14:07.123456000 97 2147483647.9999990 2038-01-19 03:14:07.999999000 98 2147483647.9999999 2038-01-19 03:14:08 99 DROP TABLE t1; 100 SET time_zone='+00:00'; 101 SELECT 102 FROM_UNIXTIME(2147483647) AS c1, 103 FROM_UNIXTIME(2147483648) AS c2, 104 FROM_UNIXTIME(2147483647.9999999) AS c3, 105 FROM_UNIXTIME(32536771199) AS c4, 106 FROM_UNIXTIME(32536771199.9999999) AS c5; 107 c1 c2 c3 c4 c5 108 2038-01-19 03:14:07 2038-01-19 03:14:08 2038-01-19 03:14:08 3001-01-18 23:59:59 null 109 SET time_zone = '+08:00'; 110 SELECT FROM_UNIXTIME(32536771199); 111 from_unixtime(32536771199) 112 3001-01-19 07:59:59 113 SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59'); 114 unix_timestamp(3001-01-18 23:59:59) 115 32536742399 116 SELECT FROM_UNIXTIME(32536771200); 117 from_unixtime(32536771200) 118 null 119 SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00'); 120 unix_timestamp(3001-01-19 00:00:00) 121 32536742400 122 SET time_zone = "+00:00"; 123 SELECT FROM_UNIXTIME(32536771200); 124 from_unixtime(32536771200) 125 null 126 SELECT UNIX_TIMESTAMP('3001-01-19 00:00:00'); 127 unix_timestamp(3001-01-19 00:00:00) 128 32536771200 129 SET time_zone = "+01:00"; 130 SELECT FROM_UNIXTIME(32536771199); 131 from_unixtime(32536771199) 132 3001-01-19 00:59:59 133 SELECT UNIX_TIMESTAMP('3001-01-19 00:59:59'); 134 unix_timestamp(3001-01-19 00:59:59) 135 32536771199 136 SELECT FROM_UNIXTIME(32536771200); 137 from_unixtime(32536771200) 138 null 139 SELECT UNIX_TIMESTAMP('3001-01-19 01:00:00'); 140 unix_timestamp(3001-01-19 01:00:00) 141 32536771200 142 SET time_zone = "+09:00"; 143 SELECT FROM_UNIXTIME(32536771199); 144 from_unixtime(32536771199) 145 3001-01-19 08:59:59 146 SELECT UNIX_TIMESTAMP("3001-01-19 08:59:59"); 147 unix_timestamp(3001-01-19 08:59:59) 148 32536771199 149 SELECT UNIX_TIMESTAMP("3001-01-19 09:00:00"); 150 unix_timestamp(3001-01-19 09:00:00) 151 32536771200 152 SET time_zone = "-01:00"; 153 SELECT FROM_UNIXTIME(32536771199); 154 from_unixtime(32536771199) 155 3001-01-18 22:59:59 156 SELECT UNIX_TIMESTAMP('3001-01-18 22:59:59'); 157 unix_timestamp(3001-01-18 22:59:59) 158 32536771199 159 SELECT FROM_UNIXTIME(32536771200); 160 from_unixtime(32536771200) 161 null 162 SELECT UNIX_TIMESTAMP('3001-01-18 23:59:59'); 163 unix_timestamp(3001-01-18 23:59:59) 164 32536774799 165 SET TIME_ZONE = "-07:00"; 166 SELECT FROM_UNIXTIME(32536771199); 167 from_unixtime(32536771199) 168 3001-01-18 16:59:59 169 SELECT UNIX_TIMESTAMP('3001-01-18 15:59:59'); 170 unix_timestamp(3001-01-18 15:59:59) 171 32536767599 172 SELECT UNIX_TIMESTAMP('3001-01-18 16:00:00'); 173 unix_timestamp(3001-01-18 16:00:00) 174 32536767600 175 SELECT FROM_UNIXTIME(9223372036854775807); 176 from_unixtime(9223372036854775807) 177 null 178 SELECT FROM_UNIXTIME(-9223372036854775808); 179 from_unixtime(-9223372036854775808) 180 null 181 SELECT FROM_UNIXTIME(9223372036854775808); 182 from_unixtime(9223372036854775808) 183 null 184 SELECT FROM_UNIXTIME(99999999999999999999999999999999999999999999999999999999999999999); 185 invalid input: 99999999999999999999999999999999999999999999999999999999999999999 beyond the range, can't be converted to Decimal128. 186 select count(unix_timestamp()); 187 count(unix_timestamp()) 188 1