sqlglot.time
1import typing as t 2import datetime 3 4# The generic time format is based on python time.strftime. 5# https://docs.python.org/3/library/time.html#time.strftime 6from sqlglot.trie import TrieResult, in_trie, new_trie 7 8 9def format_time( 10 string: str, mapping: t.Dict[str, str], trie: t.Optional[t.Dict] = None 11) -> t.Optional[str]: 12 """ 13 Converts a time string given a mapping. 14 15 Examples: 16 >>> format_time("%Y", {"%Y": "YYYY"}) 17 'YYYY' 18 19 Args: 20 mapping: dictionary of time format to target time format. 21 trie: optional trie, can be passed in for performance. 22 23 Returns: 24 The converted time string. 25 """ 26 if not string: 27 return None 28 29 start = 0 30 end = 1 31 size = len(string) 32 trie = trie or new_trie(mapping) 33 current = trie 34 chunks = [] 35 sym = None 36 37 while end <= size: 38 chars = string[start:end] 39 result, current = in_trie(current, chars[-1]) 40 41 if result == TrieResult.FAILED: 42 if sym: 43 end -= 1 44 chars = sym 45 sym = None 46 else: 47 chars = chars[0] 48 end = start + 1 49 50 start += len(chars) 51 chunks.append(chars) 52 current = trie 53 elif result == TrieResult.EXISTS: 54 sym = chars 55 56 end += 1 57 58 if result != TrieResult.FAILED and end > size: 59 chunks.append(chars) 60 61 return "".join(mapping.get(chars, chars) for chars in chunks) 62 63 64TIMEZONES = { 65 tz.lower() 66 for tz in ( 67 "Africa/Abidjan", 68 "Africa/Accra", 69 "Africa/Addis_Ababa", 70 "Africa/Algiers", 71 "Africa/Asmara", 72 "Africa/Asmera", 73 "Africa/Bamako", 74 "Africa/Bangui", 75 "Africa/Banjul", 76 "Africa/Bissau", 77 "Africa/Blantyre", 78 "Africa/Brazzaville", 79 "Africa/Bujumbura", 80 "Africa/Cairo", 81 "Africa/Casablanca", 82 "Africa/Ceuta", 83 "Africa/Conakry", 84 "Africa/Dakar", 85 "Africa/Dar_es_Salaam", 86 "Africa/Djibouti", 87 "Africa/Douala", 88 "Africa/El_Aaiun", 89 "Africa/Freetown", 90 "Africa/Gaborone", 91 "Africa/Harare", 92 "Africa/Johannesburg", 93 "Africa/Juba", 94 "Africa/Kampala", 95 "Africa/Khartoum", 96 "Africa/Kigali", 97 "Africa/Kinshasa", 98 "Africa/Lagos", 99 "Africa/Libreville", 100 "Africa/Lome", 101 "Africa/Luanda", 102 "Africa/Lubumbashi", 103 "Africa/Lusaka", 104 "Africa/Malabo", 105 "Africa/Maputo", 106 "Africa/Maseru", 107 "Africa/Mbabane", 108 "Africa/Mogadishu", 109 "Africa/Monrovia", 110 "Africa/Nairobi", 111 "Africa/Ndjamena", 112 "Africa/Niamey", 113 "Africa/Nouakchott", 114 "Africa/Ouagadougou", 115 "Africa/Porto-Novo", 116 "Africa/Sao_Tome", 117 "Africa/Timbuktu", 118 "Africa/Tripoli", 119 "Africa/Tunis", 120 "Africa/Windhoek", 121 "America/Adak", 122 "America/Anchorage", 123 "America/Anguilla", 124 "America/Antigua", 125 "America/Araguaina", 126 "America/Argentina/Buenos_Aires", 127 "America/Argentina/Catamarca", 128 "America/Argentina/ComodRivadavia", 129 "America/Argentina/Cordoba", 130 "America/Argentina/Jujuy", 131 "America/Argentina/La_Rioja", 132 "America/Argentina/Mendoza", 133 "America/Argentina/Rio_Gallegos", 134 "America/Argentina/Salta", 135 "America/Argentina/San_Juan", 136 "America/Argentina/San_Luis", 137 "America/Argentina/Tucuman", 138 "America/Argentina/Ushuaia", 139 "America/Aruba", 140 "America/Asuncion", 141 "America/Atikokan", 142 "America/Atka", 143 "America/Bahia", 144 "America/Bahia_Banderas", 145 "America/Barbados", 146 "America/Belem", 147 "America/Belize", 148 "America/Blanc-Sablon", 149 "America/Boa_Vista", 150 "America/Bogota", 151 "America/Boise", 152 "America/Buenos_Aires", 153 "America/Cambridge_Bay", 154 "America/Campo_Grande", 155 "America/Cancun", 156 "America/Caracas", 157 "America/Catamarca", 158 "America/Cayenne", 159 "America/Cayman", 160 "America/Chicago", 161 "America/Chihuahua", 162 "America/Ciudad_Juarez", 163 "America/Coral_Harbour", 164 "America/Cordoba", 165 "America/Costa_Rica", 166 "America/Creston", 167 "America/Cuiaba", 168 "America/Curacao", 169 "America/Danmarkshavn", 170 "America/Dawson", 171 "America/Dawson_Creek", 172 "America/Denver", 173 "America/Detroit", 174 "America/Dominica", 175 "America/Edmonton", 176 "America/Eirunepe", 177 "America/El_Salvador", 178 "America/Ensenada", 179 "America/Fort_Nelson", 180 "America/Fort_Wayne", 181 "America/Fortaleza", 182 "America/Glace_Bay", 183 "America/Godthab", 184 "America/Goose_Bay", 185 "America/Grand_Turk", 186 "America/Grenada", 187 "America/Guadeloupe", 188 "America/Guatemala", 189 "America/Guayaquil", 190 "America/Guyana", 191 "America/Halifax", 192 "America/Havana", 193 "America/Hermosillo", 194 "America/Indiana/Indianapolis", 195 "America/Indiana/Knox", 196 "America/Indiana/Marengo", 197 "America/Indiana/Petersburg", 198 "America/Indiana/Tell_City", 199 "America/Indiana/Vevay", 200 "America/Indiana/Vincennes", 201 "America/Indiana/Winamac", 202 "America/Indianapolis", 203 "America/Inuvik", 204 "America/Iqaluit", 205 "America/Jamaica", 206 "America/Jujuy", 207 "America/Juneau", 208 "America/Kentucky/Louisville", 209 "America/Kentucky/Monticello", 210 "America/Knox_IN", 211 "America/Kralendijk", 212 "America/La_Paz", 213 "America/Lima", 214 "America/Los_Angeles", 215 "America/Louisville", 216 "America/Lower_Princes", 217 "America/Maceio", 218 "America/Managua", 219 "America/Manaus", 220 "America/Marigot", 221 "America/Martinique", 222 "America/Matamoros", 223 "America/Mazatlan", 224 "America/Mendoza", 225 "America/Menominee", 226 "America/Merida", 227 "America/Metlakatla", 228 "America/Mexico_City", 229 "America/Miquelon", 230 "America/Moncton", 231 "America/Monterrey", 232 "America/Montevideo", 233 "America/Montreal", 234 "America/Montserrat", 235 "America/Nassau", 236 "America/New_York", 237 "America/Nipigon", 238 "America/Nome", 239 "America/Noronha", 240 "America/North_Dakota/Beulah", 241 "America/North_Dakota/Center", 242 "America/North_Dakota/New_Salem", 243 "America/Nuuk", 244 "America/Ojinaga", 245 "America/Panama", 246 "America/Pangnirtung", 247 "America/Paramaribo", 248 "America/Phoenix", 249 "America/Port-au-Prince", 250 "America/Port_of_Spain", 251 "America/Porto_Acre", 252 "America/Porto_Velho", 253 "America/Puerto_Rico", 254 "America/Punta_Arenas", 255 "America/Rainy_River", 256 "America/Rankin_Inlet", 257 "America/Recife", 258 "America/Regina", 259 "America/Resolute", 260 "America/Rio_Branco", 261 "America/Rosario", 262 "America/Santa_Isabel", 263 "America/Santarem", 264 "America/Santiago", 265 "America/Santo_Domingo", 266 "America/Sao_Paulo", 267 "America/Scoresbysund", 268 "America/Shiprock", 269 "America/Sitka", 270 "America/St_Barthelemy", 271 "America/St_Johns", 272 "America/St_Kitts", 273 "America/St_Lucia", 274 "America/St_Thomas", 275 "America/St_Vincent", 276 "America/Swift_Current", 277 "America/Tegucigalpa", 278 "America/Thule", 279 "America/Thunder_Bay", 280 "America/Tijuana", 281 "America/Toronto", 282 "America/Tortola", 283 "America/Vancouver", 284 "America/Virgin", 285 "America/Whitehorse", 286 "America/Winnipeg", 287 "America/Yakutat", 288 "America/Yellowknife", 289 "Antarctica/Casey", 290 "Antarctica/Davis", 291 "Antarctica/DumontDUrville", 292 "Antarctica/Macquarie", 293 "Antarctica/Mawson", 294 "Antarctica/McMurdo", 295 "Antarctica/Palmer", 296 "Antarctica/Rothera", 297 "Antarctica/South_Pole", 298 "Antarctica/Syowa", 299 "Antarctica/Troll", 300 "Antarctica/Vostok", 301 "Arctic/Longyearbyen", 302 "Asia/Aden", 303 "Asia/Almaty", 304 "Asia/Amman", 305 "Asia/Anadyr", 306 "Asia/Aqtau", 307 "Asia/Aqtobe", 308 "Asia/Ashgabat", 309 "Asia/Ashkhabad", 310 "Asia/Atyrau", 311 "Asia/Baghdad", 312 "Asia/Bahrain", 313 "Asia/Baku", 314 "Asia/Bangkok", 315 "Asia/Barnaul", 316 "Asia/Beirut", 317 "Asia/Bishkek", 318 "Asia/Brunei", 319 "Asia/Calcutta", 320 "Asia/Chita", 321 "Asia/Choibalsan", 322 "Asia/Chongqing", 323 "Asia/Chungking", 324 "Asia/Colombo", 325 "Asia/Dacca", 326 "Asia/Damascus", 327 "Asia/Dhaka", 328 "Asia/Dili", 329 "Asia/Dubai", 330 "Asia/Dushanbe", 331 "Asia/Famagusta", 332 "Asia/Gaza", 333 "Asia/Harbin", 334 "Asia/Hebron", 335 "Asia/Ho_Chi_Minh", 336 "Asia/Hong_Kong", 337 "Asia/Hovd", 338 "Asia/Irkutsk", 339 "Asia/Istanbul", 340 "Asia/Jakarta", 341 "Asia/Jayapura", 342 "Asia/Jerusalem", 343 "Asia/Kabul", 344 "Asia/Kamchatka", 345 "Asia/Karachi", 346 "Asia/Kashgar", 347 "Asia/Kathmandu", 348 "Asia/Katmandu", 349 "Asia/Khandyga", 350 "Asia/Kolkata", 351 "Asia/Krasnoyarsk", 352 "Asia/Kuala_Lumpur", 353 "Asia/Kuching", 354 "Asia/Kuwait", 355 "Asia/Macao", 356 "Asia/Macau", 357 "Asia/Magadan", 358 "Asia/Makassar", 359 "Asia/Manila", 360 "Asia/Muscat", 361 "Asia/Nicosia", 362 "Asia/Novokuznetsk", 363 "Asia/Novosibirsk", 364 "Asia/Omsk", 365 "Asia/Oral", 366 "Asia/Phnom_Penh", 367 "Asia/Pontianak", 368 "Asia/Pyongyang", 369 "Asia/Qatar", 370 "Asia/Qostanay", 371 "Asia/Qyzylorda", 372 "Asia/Rangoon", 373 "Asia/Riyadh", 374 "Asia/Saigon", 375 "Asia/Sakhalin", 376 "Asia/Samarkand", 377 "Asia/Seoul", 378 "Asia/Shanghai", 379 "Asia/Singapore", 380 "Asia/Srednekolymsk", 381 "Asia/Taipei", 382 "Asia/Tashkent", 383 "Asia/Tbilisi", 384 "Asia/Tehran", 385 "Asia/Tel_Aviv", 386 "Asia/Thimbu", 387 "Asia/Thimphu", 388 "Asia/Tokyo", 389 "Asia/Tomsk", 390 "Asia/Ujung_Pandang", 391 "Asia/Ulaanbaatar", 392 "Asia/Ulan_Bator", 393 "Asia/Urumqi", 394 "Asia/Ust-Nera", 395 "Asia/Vientiane", 396 "Asia/Vladivostok", 397 "Asia/Yakutsk", 398 "Asia/Yangon", 399 "Asia/Yekaterinburg", 400 "Asia/Yerevan", 401 "Atlantic/Azores", 402 "Atlantic/Bermuda", 403 "Atlantic/Canary", 404 "Atlantic/Cape_Verde", 405 "Atlantic/Faeroe", 406 "Atlantic/Faroe", 407 "Atlantic/Jan_Mayen", 408 "Atlantic/Madeira", 409 "Atlantic/Reykjavik", 410 "Atlantic/South_Georgia", 411 "Atlantic/St_Helena", 412 "Atlantic/Stanley", 413 "Australia/ACT", 414 "Australia/Adelaide", 415 "Australia/Brisbane", 416 "Australia/Broken_Hill", 417 "Australia/Canberra", 418 "Australia/Currie", 419 "Australia/Darwin", 420 "Australia/Eucla", 421 "Australia/Hobart", 422 "Australia/LHI", 423 "Australia/Lindeman", 424 "Australia/Lord_Howe", 425 "Australia/Melbourne", 426 "Australia/NSW", 427 "Australia/North", 428 "Australia/Perth", 429 "Australia/Queensland", 430 "Australia/South", 431 "Australia/Sydney", 432 "Australia/Tasmania", 433 "Australia/Victoria", 434 "Australia/West", 435 "Australia/Yancowinna", 436 "Brazil/Acre", 437 "Brazil/DeNoronha", 438 "Brazil/East", 439 "Brazil/West", 440 "CET", 441 "CST6CDT", 442 "Canada/Atlantic", 443 "Canada/Central", 444 "Canada/Eastern", 445 "Canada/Mountain", 446 "Canada/Newfoundland", 447 "Canada/Pacific", 448 "Canada/Saskatchewan", 449 "Canada/Yukon", 450 "Chile/Continental", 451 "Chile/EasterIsland", 452 "Cuba", 453 "EET", 454 "EST", 455 "EST5EDT", 456 "Egypt", 457 "Eire", 458 "Etc/GMT", 459 "Etc/GMT+0", 460 "Etc/GMT+1", 461 "Etc/GMT+10", 462 "Etc/GMT+11", 463 "Etc/GMT+12", 464 "Etc/GMT+2", 465 "Etc/GMT+3", 466 "Etc/GMT+4", 467 "Etc/GMT+5", 468 "Etc/GMT+6", 469 "Etc/GMT+7", 470 "Etc/GMT+8", 471 "Etc/GMT+9", 472 "Etc/GMT-0", 473 "Etc/GMT-1", 474 "Etc/GMT-10", 475 "Etc/GMT-11", 476 "Etc/GMT-12", 477 "Etc/GMT-13", 478 "Etc/GMT-14", 479 "Etc/GMT-2", 480 "Etc/GMT-3", 481 "Etc/GMT-4", 482 "Etc/GMT-5", 483 "Etc/GMT-6", 484 "Etc/GMT-7", 485 "Etc/GMT-8", 486 "Etc/GMT-9", 487 "Etc/GMT0", 488 "Etc/Greenwich", 489 "Etc/UCT", 490 "Etc/UTC", 491 "Etc/Universal", 492 "Etc/Zulu", 493 "Europe/Amsterdam", 494 "Europe/Andorra", 495 "Europe/Astrakhan", 496 "Europe/Athens", 497 "Europe/Belfast", 498 "Europe/Belgrade", 499 "Europe/Berlin", 500 "Europe/Bratislava", 501 "Europe/Brussels", 502 "Europe/Bucharest", 503 "Europe/Budapest", 504 "Europe/Busingen", 505 "Europe/Chisinau", 506 "Europe/Copenhagen", 507 "Europe/Dublin", 508 "Europe/Gibraltar", 509 "Europe/Guernsey", 510 "Europe/Helsinki", 511 "Europe/Isle_of_Man", 512 "Europe/Istanbul", 513 "Europe/Jersey", 514 "Europe/Kaliningrad", 515 "Europe/Kiev", 516 "Europe/Kirov", 517 "Europe/Kyiv", 518 "Europe/Lisbon", 519 "Europe/Ljubljana", 520 "Europe/London", 521 "Europe/Luxembourg", 522 "Europe/Madrid", 523 "Europe/Malta", 524 "Europe/Mariehamn", 525 "Europe/Minsk", 526 "Europe/Monaco", 527 "Europe/Moscow", 528 "Europe/Nicosia", 529 "Europe/Oslo", 530 "Europe/Paris", 531 "Europe/Podgorica", 532 "Europe/Prague", 533 "Europe/Riga", 534 "Europe/Rome", 535 "Europe/Samara", 536 "Europe/San_Marino", 537 "Europe/Sarajevo", 538 "Europe/Saratov", 539 "Europe/Simferopol", 540 "Europe/Skopje", 541 "Europe/Sofia", 542 "Europe/Stockholm", 543 "Europe/Tallinn", 544 "Europe/Tirane", 545 "Europe/Tiraspol", 546 "Europe/Ulyanovsk", 547 "Europe/Uzhgorod", 548 "Europe/Vaduz", 549 "Europe/Vatican", 550 "Europe/Vienna", 551 "Europe/Vilnius", 552 "Europe/Volgograd", 553 "Europe/Warsaw", 554 "Europe/Zagreb", 555 "Europe/Zaporozhye", 556 "Europe/Zurich", 557 "GB", 558 "GB-Eire", 559 "GMT", 560 "GMT+0", 561 "GMT-0", 562 "GMT0", 563 "Greenwich", 564 "HST", 565 "Hongkong", 566 "Iceland", 567 "Indian/Antananarivo", 568 "Indian/Chagos", 569 "Indian/Christmas", 570 "Indian/Cocos", 571 "Indian/Comoro", 572 "Indian/Kerguelen", 573 "Indian/Mahe", 574 "Indian/Maldives", 575 "Indian/Mauritius", 576 "Indian/Mayotte", 577 "Indian/Reunion", 578 "Iran", 579 "Israel", 580 "Jamaica", 581 "Japan", 582 "Kwajalein", 583 "Libya", 584 "MET", 585 "MST", 586 "MST7MDT", 587 "Mexico/BajaNorte", 588 "Mexico/BajaSur", 589 "Mexico/General", 590 "NZ", 591 "NZ-CHAT", 592 "Navajo", 593 "PRC", 594 "PST8PDT", 595 "Pacific/Apia", 596 "Pacific/Auckland", 597 "Pacific/Bougainville", 598 "Pacific/Chatham", 599 "Pacific/Chuuk", 600 "Pacific/Easter", 601 "Pacific/Efate", 602 "Pacific/Enderbury", 603 "Pacific/Fakaofo", 604 "Pacific/Fiji", 605 "Pacific/Funafuti", 606 "Pacific/Galapagos", 607 "Pacific/Gambier", 608 "Pacific/Guadalcanal", 609 "Pacific/Guam", 610 "Pacific/Honolulu", 611 "Pacific/Johnston", 612 "Pacific/Kanton", 613 "Pacific/Kiritimati", 614 "Pacific/Kosrae", 615 "Pacific/Kwajalein", 616 "Pacific/Majuro", 617 "Pacific/Marquesas", 618 "Pacific/Midway", 619 "Pacific/Nauru", 620 "Pacific/Niue", 621 "Pacific/Norfolk", 622 "Pacific/Noumea", 623 "Pacific/Pago_Pago", 624 "Pacific/Palau", 625 "Pacific/Pitcairn", 626 "Pacific/Pohnpei", 627 "Pacific/Ponape", 628 "Pacific/Port_Moresby", 629 "Pacific/Rarotonga", 630 "Pacific/Saipan", 631 "Pacific/Samoa", 632 "Pacific/Tahiti", 633 "Pacific/Tarawa", 634 "Pacific/Tongatapu", 635 "Pacific/Truk", 636 "Pacific/Wake", 637 "Pacific/Wallis", 638 "Pacific/Yap", 639 "Poland", 640 "Portugal", 641 "ROC", 642 "ROK", 643 "Singapore", 644 "Turkey", 645 "UCT", 646 "US/Alaska", 647 "US/Aleutian", 648 "US/Arizona", 649 "US/Central", 650 "US/East-Indiana", 651 "US/Eastern", 652 "US/Hawaii", 653 "US/Indiana-Starke", 654 "US/Michigan", 655 "US/Mountain", 656 "US/Pacific", 657 "US/Samoa", 658 "UTC", 659 "Universal", 660 "W-SU", 661 "WET", 662 "Zulu", 663 ) 664} 665 666 667def subsecond_precision(timestamp_literal: str) -> int: 668 """ 669 Given an ISO-8601 timestamp literal, eg '2023-01-01 12:13:14.123456+00:00' 670 figure out its subsecond precision so we can construct types like DATETIME(6) 671 672 Note that in practice, this is either 3 or 6 digits (3 = millisecond precision, 6 = microsecond precision) 673 - 6 is the maximum because strftime's '%f' formats to microseconds and almost every database supports microsecond precision in timestamps 674 - Except Presto/Trino which in most cases only supports millisecond precision but will still honour '%f' and format to microseconds (replacing the remaining 3 digits with 0's) 675 - Python prior to 3.11 only supports 0, 3 or 6 digits in a timestamp literal. Any other amounts will throw a 'ValueError: Invalid isoformat string:' error 676 """ 677 try: 678 parsed = datetime.datetime.fromisoformat(timestamp_literal) 679 subsecond_digit_count = len(str(parsed.microsecond).rstrip("0")) 680 precision = 0 681 if subsecond_digit_count > 3: 682 precision = 6 683 elif subsecond_digit_count > 0: 684 precision = 3 685 return precision 686 except ValueError: 687 return 0
def
format_time( string: str, mapping: Dict[str, str], trie: Optional[Dict] = None) -> Optional[str]:
10def format_time( 11 string: str, mapping: t.Dict[str, str], trie: t.Optional[t.Dict] = None 12) -> t.Optional[str]: 13 """ 14 Converts a time string given a mapping. 15 16 Examples: 17 >>> format_time("%Y", {"%Y": "YYYY"}) 18 'YYYY' 19 20 Args: 21 mapping: dictionary of time format to target time format. 22 trie: optional trie, can be passed in for performance. 23 24 Returns: 25 The converted time string. 26 """ 27 if not string: 28 return None 29 30 start = 0 31 end = 1 32 size = len(string) 33 trie = trie or new_trie(mapping) 34 current = trie 35 chunks = [] 36 sym = None 37 38 while end <= size: 39 chars = string[start:end] 40 result, current = in_trie(current, chars[-1]) 41 42 if result == TrieResult.FAILED: 43 if sym: 44 end -= 1 45 chars = sym 46 sym = None 47 else: 48 chars = chars[0] 49 end = start + 1 50 51 start += len(chars) 52 chunks.append(chars) 53 current = trie 54 elif result == TrieResult.EXISTS: 55 sym = chars 56 57 end += 1 58 59 if result != TrieResult.FAILED and end > size: 60 chunks.append(chars) 61 62 return "".join(mapping.get(chars, chars) for chars in chunks)
Converts a time string given a mapping.
Examples:
>>> format_time("%Y", {"%Y": "YYYY"}) 'YYYY'
Args: mapping: dictionary of time format to target time format. trie: optional trie, can be passed in for performance.
Returns: The converted time string.
TIMEZONES =
{'america/aruba', 'america/montserrat', 'america/sitka', 'america/cordoba', 'africa/bangui', 'atlantic/cape_verde', 'etc/universal', 'antarctica/syowa', 'asia/qostanay', 'america/argentina/comodrivadavia', 'atlantic/faeroe', 'america/port_of_spain', 'africa/conakry', 'pacific/wallis', 'australia/lhi', 'america/argentina/tucuman', 'america/curacao', 'america/indiana/vincennes', 'europe/stockholm', 'africa/dar_es_salaam', 'america/bahia_banderas', 'pacific/kanton', 'etc/gmt-6', 'atlantic/stanley', 'europe/budapest', 'america/miquelon', 'us/pacific', 'asia/rangoon', 'us/east-indiana', 'canada/eastern', 'iran', 'asia/tokyo', 'america/blanc-sablon', 'antarctica/dumontdurville', 'asia/ashkhabad', 'antarctica/davis', 'antarctica/palmer', 'europe/sarajevo', 'america/metlakatla', 'etc/gmt+5', 'pacific/kwajalein', 'europe/dublin', 'africa/asmara', 'america/rosario', 'pacific/norfolk', 'europe/skopje', 'america/mendoza', 'atlantic/reykjavik', 'europe/tallinn', 'america/swift_current', 'prc', 'canada/mountain', 'asia/choibalsan', 'europe/prague', 'asia/dacca', 'us/hawaii', 'etc/gmt-11', 'europe/vilnius', 'america/argentina/cordoba', 'europe/busingen', 'atlantic/jan_mayen', 'asia/yerevan', 'africa/blantyre', 'africa/kigali', 'america/el_salvador', 'america/grand_turk', 'antarctica/casey', 'america/rio_branco', 'asia/beirut', 'indian/antananarivo', 'pacific/guadalcanal', 'america/bahia', 'america/ciudad_juarez', 'asia/qatar', 'america/noronha', 'pacific/tarawa', 'asia/barnaul', 'africa/juba', 'etc/gmt+12', 'america/lower_princes', 'america/godthab', 'europe/istanbul', 'asia/omsk', 'pacific/johnston', 'pacific/marquesas', 'asia/srednekolymsk', 'asia/baku', 'asia/gaza', 'chile/continental', 'pacific/honolulu', 'navajo', 'america/ojinaga', 'asia/hovd', 'asia/tomsk', 'pacific/tahiti', 'america/campo_grande', 'asia/makassar', 'indian/chagos', 'asia/saigon', 'pacific/efate', 'america/caracas', 'america/argentina/jujuy', 'america/belem', 'africa/harare', 'america/kentucky/louisville', 'pacific/easter', 'europe/vienna', 'asia/tehran', 'asia/kathmandu', 'america/lima', 'europe/samara', 'etc/gmt+10', 'america/indiana/vevay', 'asia/urumqi', 'etc/gmt+11', 'indian/mahe', 'brazil/west', 'asia/dhaka', 'africa/brazzaville', 'etc/gmt+6', 'pacific/noumea', 'pacific/yap', 'america/mexico_city', 'america/santarem', 'asia/taipei', 'america/guatemala', 'europe/bratislava', 'pacific/palau', 'asia/damascus', 'africa/accra', 'america/new_york', 'europe/kyiv', 'america/atikokan', 'australia/sydney', 'america/la_paz', 'egypt', 'antarctica/rothera', 'atlantic/st_helena', 'etc/gmt-0', 'america/havana', 'australia/brisbane', 'america/adak', 'eire', 'america/port-au-prince', 'etc/zulu', 'america/argentina/mendoza', 'america/martinique', 'asia/chongqing', 'europe/san_marino', 'america/scoresbysund', 'africa/nairobi', 'europe/zagreb', 'america/porto_velho', 'asia/bangkok', 'asia/ulan_bator', 'indian/mauritius', 'europe/uzhgorod', 'brazil/denoronha', 'canada/atlantic', 'america/knox_in', 'europe/guernsey', 'asia/harbin', 'america/juneau', 'us/aleutian', 'canada/yukon', 'america/rankin_inlet', 'israel', 'europe/astrakhan', 'africa/timbuktu', 'america/chicago', 'america/hermosillo', 'asia/thimphu', 'asia/yekaterinburg', 'america/managua', 'uct', 'pacific/saipan', 'australia/north', 'europe/athens', 'indian/cocos', 'pacific/niue', 'asia/anadyr', 'africa/bissau', 'australia/hobart', 'america/argentina/ushuaia', 'asia/novokuznetsk', 'africa/maseru', 'america/argentina/san_juan', 'pacific/kosrae', 'asia/nicosia', 'america/yakutat', 'australia/broken_hill', 'europe/warsaw', 'pst8pdt', 'us/alaska', 'africa/casablanca', 'america/moncton', 'asia/chita', 'roc', 'africa/mbabane', 'europe/gibraltar', 'africa/djibouti', 'asia/dili', 'pacific/rarotonga', 'america/anchorage', 'asia/karachi', 'africa/bamako', 'atlantic/canary', 'america/tijuana', 'gmt', 'asia/ulaanbaatar', 'pacific/majuro', 'europe/isle_of_man', 'america/yellowknife', 'america/iqaluit', 'us/eastern', 'asia/jakarta', 'greenwich', 'cuba', 'america/inuvik', 'asia/krasnoyarsk', 'iceland', 'asia/baghdad', 'asia/qyzylorda', 'etc/gmt+9', 'asia/kamchatka', 'america/punta_arenas', 'asia/novosibirsk', 'america/asuncion', 'america/guyana', 'brazil/east', 'asia/bishkek', 'etc/gmt+4', 'america/merida', 'asia/kuwait', 'africa/addis_ababa', 'jamaica', 'etc/utc', 'europe/kirov', 'america/detroit', 'america/indianapolis', 'asia/famagusta', 'europe/moscow', 'etc/gmt+3', 'atlantic/faroe', 'america/nassau', 'africa/nouakchott', 'asia/atyrau', 'pacific/wake', 'america/menominee', 'turkey', 'america/virgin', 'america/creston', 'asia/brunei', 'asia/kolkata', 'asia/samarkand', 'antarctica/troll', 'asia/phnom_penh', 'america/costa_rica', 'gmt-0', 'america/antigua', 'asia/ujung_pandang', 'etc/uct', 'us/indiana-starke', 'pacific/port_moresby', 'america/rainy_river', 'america/st_vincent', 'america/indiana/marengo', 'america/paramaribo', 'america/los_angeles', 'brazil/acre', 'utc', 'etc/gmt-12', 'gmt+0', 'america/chihuahua', 'us/samoa', 'europe/ljubljana', 'australia/yancowinna', 'asia/dushanbe', 'asia/yakutsk', 'mst7mdt', 'america/cancun', 'america/denver', 'africa/libreville', 'africa/lubumbashi', 'poland', 'asia/aqtau', 'etc/gmt-7', 'asia/amman', 'pacific/pitcairn', 'australia/south', 'pacific/gambier', 'america/st_kitts', 'america/regina', 'etc/greenwich', 'est5edt', 'europe/monaco', 'africa/kinshasa', 'asia/yangon', 'america/thunder_bay', 'america/winnipeg', 'europe/kiev', 'america/dawson_creek', 'europe/lisbon', 'europe/belgrade', 'etc/gmt-3', 'cst6cdt', 'asia/vientiane', 'asia/ho_chi_minh', 'asia/hong_kong', 'universal', 'africa/abidjan', 'indian/reunion', 'europe/amsterdam', 'africa/maputo', 'europe/sofia', 'asia/kuala_lumpur', 'europe/jersey', 'america/belize', 'asia/ashgabat', 'america/guayaquil', 'america/santiago', 'australia/nsw', 'asia/kuching', 'etc/gmt-4', 'etc/gmt-8', 'pacific/enderbury', 'pacific/guam', 'asia/dubai', 'asia/ust-nera', 'asia/vladivostok', 'atlantic/south_georgia', 'etc/gmt+0', 'america/jujuy', 'america/argentina/la_rioja', 'america/dominica', 'etc/gmt-1', 'asia/tel_aviv', 'africa/lome', 'mexico/general', 'atlantic/bermuda', 'america/cayenne', 'asia/manila', 'europe/belfast', 'pacific/fiji', 'pacific/nauru', 'america/louisville', 'gb', 'america/cuiaba', 'america/argentina/salta', 'us/arizona', 'asia/shanghai', 'america/puerto_rico', 'america/nuuk', 'america/guadeloupe', 'asia/jerusalem', 'europe/simferopol', 'pacific/bougainville', 'africa/porto-novo', 'africa/tunis', 'etc/gmt-9', 'europe/berlin', 'mst', 'singapore', 'met', 'australia/tasmania', 'america/marigot', 'asia/aden', 'america/atka', 'america/fort_nelson', 'america/indiana/petersburg', 'asia/aqtobe', 'america/manaus', 'africa/khartoum', 'europe/malta', 'europe/tirane', 'pacific/ponape', 'europe/vaduz', 'europe/paris', 'pacific/chuuk', 'europe/rome', 'europe/saratov', 'pacific/truk', 'asia/jayapura', 'america/cambridge_bay', 'america/matamoros', 'asia/macau', 'asia/riyadh', 'australia/melbourne', 'zulu', 'australia/darwin', 'etc/gmt+2', 'asia/colombo', 'america/santa_isabel', 'america/argentina/san_luis', 'africa/douala', 'america/buenos_aires', 'indian/maldives', 'us/mountain', 'america/boa_vista', 'america/argentina/rio_gallegos', 'america/indiana/indianapolis', 'asia/irkutsk', 'cet', 'africa/ceuta', 'wet', 'america/halifax', 'america/st_lucia', 'asia/bahrain', 'europe/podgorica', 'america/sao_paulo', 'canada/saskatchewan', 'portugal', 'asia/hebron', 'africa/bujumbura', 'america/catamarca', 'america/vancouver', 'europe/chisinau', 'africa/niamey', 'africa/windhoek', 'africa/mogadishu', 'america/porto_acre', 'canada/pacific', 'etc/gmt', 'canada/central', 'australia/eucla', 'europe/oslo', 'atlantic/azores', 'europe/luxembourg', 'africa/cairo', 'us/central', 'etc/gmt-2', 'gmt0', 'africa/el_aaiun', 'mexico/bajasur', 'pacific/fakaofo', 'australia/canberra', 'pacific/galapagos', 'etc/gmt+7', 'europe/helsinki', 'pacific/auckland', 'pacific/samoa', 'us/michigan', 'etc/gmt-10', 'australia/perth', 'africa/sao_tome', 'europe/andorra', 'america/kralendijk', 'america/north_dakota/center', 'asia/kashgar', 'asia/seoul', 'america/st_thomas', 'europe/vatican', 'africa/monrovia', 'arctic/longyearbyen', 'hongkong', 'america/recife', 'africa/gaborone', 'pacific/pohnpei', 'africa/algiers', 'america/santo_domingo', 'africa/lagos', 'america/maceio', 'europe/copenhagen', 'africa/lusaka', 'america/edmonton', 'africa/johannesburg', 'america/montevideo', 'indian/mayotte', 'america/cayman', 'eet', 'etc/gmt-14', 'w-su', 'canada/newfoundland', 'pacific/funafuti', 'asia/tbilisi', 'nz-chat', 'america/panama', 'antarctica/macquarie', 'est', 'australia/west', 'asia/tashkent', 'america/monterrey', 'europe/london', 'america/anguilla', 'america/montreal', 'europe/zurich', 'asia/pyongyang', 'asia/almaty', 'europe/riga', 'europe/mariehamn', 'america/argentina/buenos_aires', 'america/resolute', 'america/indiana/tell_city', 'america/tegucigalpa', 'america/fort_wayne', 'america/indiana/knox', 'america/coral_harbour', 'asia/istanbul', 'asia/muscat', 'asia/magadan', 'australia/lindeman', 'asia/oral', 'etc/gmt+8', 'libya', 'mexico/bajanorte', 'gb-eire', 'africa/malabo', 'america/nome', 'pacific/chatham', 'africa/asmera', 'antarctica/vostok', 'australia/adelaide', 'kwajalein', 'america/fortaleza', 'pacific/apia', 'america/st_barthelemy', 'america/araguaina', 'asia/pontianak', 'america/eirunepe', 'etc/gmt-13', 'pacific/tongatapu', 'america/dawson', 'america/whitehorse', 'hst', 'asia/macao', 'etc/gmt-5', 'rok', 'africa/kampala', 'indian/christmas', 'europe/minsk', 'america/north_dakota/new_salem', 'europe/tiraspol', 'america/boise', 'asia/khandyga', 'america/tortola', 'america/jamaica', 'africa/ndjamena', 'australia/currie', 'japan', 'antarctica/south_pole', 'america/kentucky/monticello', 'america/shiprock', 'etc/gmt+1', 'australia/act', 'asia/katmandu', 'europe/nicosia', 'africa/luanda', 'antarctica/mcmurdo', 'asia/thimbu', 'america/danmarkshavn', 'etc/gmt0', 'america/glace_bay', 'america/argentina/catamarca', 'asia/calcutta', 'indian/comoro', 'europe/bucharest', 'america/grenada', 'america/north_dakota/beulah', 'pacific/kiritimati', 'america/ensenada', 'america/goose_bay', 'chile/easterisland', 'america/bogota', 'nz', 'australia/victoria', 'america/pangnirtung', 'africa/tripoli', 'antarctica/mawson', 'africa/banjul', 'america/phoenix', 'pacific/pago_pago', 'europe/zaporozhye', 'america/toronto', 'asia/singapore', 'europe/ulyanovsk', 'indian/kerguelen', 'america/st_johns', 'europe/kaliningrad', 'australia/lord_howe', 'america/indiana/winamac', 'europe/madrid', 'australia/queensland', 'europe/volgograd', 'asia/kabul', 'europe/brussels', 'atlantic/madeira', 'africa/ouagadougou', 'asia/chungking', 'pacific/midway', 'asia/sakhalin', 'america/barbados', 'america/nipigon', 'america/mazatlan', 'africa/dakar', 'africa/freetown', 'america/thule'}
def
subsecond_precision(timestamp_literal: str) -> int:
668def subsecond_precision(timestamp_literal: str) -> int: 669 """ 670 Given an ISO-8601 timestamp literal, eg '2023-01-01 12:13:14.123456+00:00' 671 figure out its subsecond precision so we can construct types like DATETIME(6) 672 673 Note that in practice, this is either 3 or 6 digits (3 = millisecond precision, 6 = microsecond precision) 674 - 6 is the maximum because strftime's '%f' formats to microseconds and almost every database supports microsecond precision in timestamps 675 - Except Presto/Trino which in most cases only supports millisecond precision but will still honour '%f' and format to microseconds (replacing the remaining 3 digits with 0's) 676 - Python prior to 3.11 only supports 0, 3 or 6 digits in a timestamp literal. Any other amounts will throw a 'ValueError: Invalid isoformat string:' error 677 """ 678 try: 679 parsed = datetime.datetime.fromisoformat(timestamp_literal) 680 subsecond_digit_count = len(str(parsed.microsecond).rstrip("0")) 681 precision = 0 682 if subsecond_digit_count > 3: 683 precision = 6 684 elif subsecond_digit_count > 0: 685 precision = 3 686 return precision 687 except ValueError: 688 return 0
Given an ISO-8601 timestamp literal, eg '2023-01-01 12:13:14.123456+00:00' figure out its subsecond precision so we can construct types like DATETIME(6)
Note that in practice, this is either 3 or 6 digits (3 = millisecond precision, 6 = microsecond precision)
- 6 is the maximum because strftime's '%f' formats to microseconds and almost every database supports microsecond precision in timestamps
- Except Presto/Trino which in most cases only supports millisecond precision but will still honour '%f' and format to microseconds (replacing the remaining 3 digits with 0's)
- Python prior to 3.11 only supports 0, 3 or 6 digits in a timestamp literal. Any other amounts will throw a 'ValueError: Invalid isoformat string:' error