github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/interval (about) 1 # test we store various types with precision correctly. 2 subtest interval_type_storage 3 4 statement ok 5 CREATE TABLE interval_duration_type ( 6 id INTEGER PRIMARY KEY, 7 regular INTERVAL, 8 regular_precision INTERVAL(3), 9 second INTERVAL SECOND, 10 second_precision INTERVAL SECOND(3), 11 minute INTERVAL MINUTE, 12 minute_to_second_precision INTERVAL MINUTE TO SECOND(3) 13 ) 14 15 statement ok 16 INSERT INTO interval_duration_type (id, regular, regular_precision, second, second_precision, minute, minute_to_second_precision) VALUES 17 (1, '12:34:56.123456', '12:34:56.123456', '12:34:56.123456', '12:34:56.123456', '12:34:56.123456', '12:34:56.123456'), 18 (2, '12:56.123456', '12:56.123456', '12:56.123456', '12:56.123456', '12:56.123456', '12:56.123456'), 19 (3, '366 12:34:56.123456', '366 12:34:56.123456', '366 12:34:56.123456', '366 12:34:56.123456', '366 12:34:56.123456', '366 12:34:56.123456'), 20 (4, '1-2 3.1', '1-2 3.1', '1-2 3.1', '1-2 3.1', '1-2 3.1', '1-2 3.1') 21 22 query ITTTTTT 23 select * from interval_duration_type order by id asc 24 ---- 25 1 12:34:56.123456 12:34:56.123 12:34:56.123456 12:34:56.123 12:34:00 12:34:56.123 26 2 00:12:56.123456 00:12:56.123 00:12:56.123456 00:12:56.123 00:12:00 00:12:56.123 27 3 366 days 12:34:56.123456 366 days 12:34:56.123 366 days 12:34:56.123456 366 days 12:34:56.123 366 days 12:34:00 366 days 12:34:56.123 28 4 1 year 2 mons 00:00:03.1 1 year 2 mons 00:00:03.1 1 year 2 mons 00:00:03.1 1 year 2 mons 00:00:03.1 1 year 2 mons 00:03:00 1 year 2 mons 00:00:03.1 29 30 subtest interval_extract_tests 31 32 query R 33 SELECT extract('second', interval '10:55:01.456') 34 ---- 35 1.456 36 37 query R 38 SELECT extract(minute from interval '10:55:01.456') 39 ---- 40 55 41 42 # tests various typmods of intervals 43 # matches subset of tests in src/test/regress/expected/interval.out 44 subtest interval_postgres_duration_type_tests 45 46 # oversize leading field is ok 47 query T 48 SELECT interval '999' second 49 ---- 50 00:16:39 51 52 query T 53 SELECT interval '999' minute 54 ---- 55 16:39:00 56 57 query T 58 SELECT interval '999' hour 59 ---- 60 999:00:00 61 62 query T 63 SELECT interval '999' day 64 ---- 65 999 days 66 67 query T 68 SELECT interval '999' month 69 ---- 70 83 years 3 mons 71 72 # test SQL-spec syntaxes for restricted field sets 73 74 query T 75 SELECT interval '1' year 76 ---- 77 1 year 78 79 query T 80 SELECT interval '2' month 81 ---- 82 2 mons 83 84 query T 85 SELECT interval '3' day 86 ---- 87 3 days 88 89 query T 90 SELECT interval '4' hour 91 ---- 92 04:00:00 93 94 query T 95 SELECT interval '5' minute 96 ---- 97 00:05:00 98 99 query T 100 SELECT interval '6' second 101 ---- 102 00:00:06 103 104 query T 105 SELECT interval '1' year to month 106 ---- 107 1 mon 108 109 query T 110 SELECT interval '1-2' year to month 111 ---- 112 1 year 2 mons 113 114 query T 115 SELECT interval '1 2' day to hour 116 ---- 117 1 day 02:00:00 118 119 query T 120 SELECT interval '1 2:03' day to hour 121 ---- 122 1 day 02:00:00 123 124 query T 125 SELECT interval '1 2:03:04' day to hour 126 ---- 127 1 day 02:00:00 128 129 query error could not parse "1 2" as type interval 130 SELECT interval '1 2' day to minute 131 132 query T 133 SELECT interval '1 2:03' day to minute 134 ---- 135 1 day 02:03:00 136 137 query T 138 SELECT interval '1 2:03:04' day to minute 139 ---- 140 1 day 02:03:00 141 142 query error could not parse "1 2" as type interval 143 SELECT interval '1 2' day to second 144 145 query T 146 SELECT interval '1 2:03' day to second 147 ---- 148 1 day 02:03:00 149 150 query T 151 SELECT interval '1 2:03:04' day to second 152 ---- 153 1 day 02:03:04 154 155 query error could not parse "1 2" as type interval 156 SELECT interval '1 2' hour to minute 157 158 query T 159 SELECT interval '1 2:03' hour to minute 160 ---- 161 1 day 02:03:00 162 163 query T 164 SELECT interval '1 2:03:04' hour to minute 165 ---- 166 1 day 02:03:00 167 168 query error could not parse "1 2" as type interval 169 SELECT interval '1 2' hour to second 170 171 query T 172 SELECT interval '1 2:03' hour to second 173 ---- 174 1 day 02:03:00 175 176 query T 177 SELECT interval '1 2:03:04' hour to second 178 ---- 179 1 day 02:03:04 180 181 query error could not parse "1 2" as type interval 182 SELECT interval '1 2' minute to second 183 184 query T 185 SELECT interval '1 2:03' minute to second 186 ---- 187 1 day 00:02:03 188 189 query T 190 SELECT interval '1 2:03:04' minute to second 191 ---- 192 1 day 02:03:04 193 194 query T 195 SELECT interval '1 +2:03' minute to second 196 ---- 197 1 day 00:02:03 198 199 query T 200 SELECT interval '1 +2:03:04' minute to second 201 ---- 202 1 day 02:03:04 203 204 query T 205 SELECT interval '1 -2:03' minute to second 206 ---- 207 1 day -00:02:03 208 209 query T 210 SELECT interval '1 -2:03:04' minute to second 211 ---- 212 1 day -02:03:04 213 214 query T 215 SELECT interval '123 11' day to hour 216 ---- 217 123 days 11:00:00 218 219 query error could not parse "123 11" as type interval 220 SELECT interval '123 11' day 221 222 query error could not parse "123 11" as type interval 223 SELECT interval '123 11' 224 225 # not ok, redundant hh:mm fields 226 query error could not parse "123 2:03 -2:04" as type interval 227 SELECT interval '123 2:03 -2:04' 228 229 # test syntaxes for restricted precision 230 query T 231 SELECT interval(0) '1 day 01:23:45.6789' 232 ---- 233 1 day 01:23:46 234 235 query T 236 SELECT interval(2) '1 day 01:23:45.6789' 237 ---- 238 1 day 01:23:45.68 239 240 query T 241 SELECT interval '12:34.5678' minute to second(2) 242 ---- 243 00:12:34.57 244 245 query T 246 SELECT interval '1.234' second 247 ---- 248 00:00:01.234 249 250 query T 251 SELECT interval '1.234' second(2) 252 ---- 253 00:00:01.23 254 255 query error could not parse "1 2.345" as type interval 256 SELECT interval '1 2.345' day to second(2) 257 258 query T 259 SELECT interval '1 2:03' day to second(2) 260 ---- 261 1 day 02:03:00 262 263 query T 264 SELECT interval '1 2:03.4567' day to second(2) 265 ---- 266 1 day 00:02:03.46 267 268 query T 269 SELECT interval '1 2:03:04.5678' day to second(2) 270 ---- 271 1 day 02:03:04.57 272 273 query error could not parse "1 2.345" as type interval 274 SELECT interval '1 2.345' hour to second(2) 275 276 query T 277 SELECT interval '1 2:03.45678' hour to second(2) 278 ---- 279 1 day 00:02:03.46 280 281 query T 282 SELECT interval '1 2:03:04.5678' hour to second(2) 283 ---- 284 1 day 02:03:04.57 285 286 query error could not parse "1 2.3456" as type interval 287 SELECT interval '1 2.3456' minute to second(2) 288 289 query T 290 SELECT interval '1 2:03.5678' minute to second(2) 291 ---- 292 1 day 00:02:03.57 293 294 query T 295 SELECT interval '1 2:03:04.5678' minute to second(2) 296 ---- 297 1 day 02:03:04.57 298 299 # Extra regression tests found when fixing this bug. 300 subtest regression_43074 301 302 query T 303 SELECT interval '1:02.123456' 304 ---- 305 00:01:02.123456 306 307 query T 308 SELECT interval '-1:02.123456' 309 ---- 310 -00:01:02.123456 311 312 subtest regression_43079 313 314 query T 315 SELECT interval '1-2 3' year 316 ---- 317 4 years 318 319 query T 320 SELECT interval '1-2 3' day 321 ---- 322 1 year 2 mons 3 days 323 324 query T 325 SELECT interval '2.1 00:' 326 ---- 327 2 days 02:24:00 328 329 query T 330 SELECT interval ' 5 ' year 331 ---- 332 5 years 333 334 # Check default types and expressions get truncated on insert / update. 335 subtest regression_44774 336 337 statement ok 338 CREATE TABLE regression_44774 ( 339 a interval(3) DEFAULT '1:2:3.123456' 340 ) 341 342 statement ok 343 INSERT INTO regression_44774 VALUES (default), ('4:5:6.123456') 344 345 query T 346 SELECT a FROM regression_44774 ORDER BY a 347 ---- 348 01:02:03.123 349 04:05:06.123 350 351 statement ok 352 UPDATE regression_44774 353 SET a = '13:14:15.123456'::interval + '1 sec'::interval 354 WHERE 1 = 1 355 356 query T 357 SELECT a FROM regression_44774 ORDER BY a 358 ---- 359 13:14:16.123 360 13:14:16.123 361 362 statement ok 363 DROP TABLE regression_44774