Edit on GitHub

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 = {'etc/gmt-6', 'asia/yangon', 'asia/chungking', 'america/caracas', 'australia/canberra', 'europe/isle_of_man', 'europe/riga', 'atlantic/jan_mayen', 'pacific/truk', 'pacific/guam', 'america/indiana/tell_city', 'pacific/yap', 'america/sitka', 'america/montreal', 'asia/srednekolymsk', 'atlantic/canary', 'america/nome', 'america/aruba', 'europe/budapest', 'africa/nairobi', 'pacific/nauru', 'america/indiana/indianapolis', 'america/manaus', 'africa/accra', 'america/sao_paulo', 'africa/dar_es_salaam', 'us/eastern', 'etc/gmt+5', 'australia/victoria', 'america/argentina/mendoza', 'africa/niamey', 'asia/hovd', 'africa/algiers', 'america/araguaina', 'etc/gmt-9', 'america/fortaleza', 'america/indiana/marengo', 'mexico/bajasur', 'america/detroit', 'est', 'america/moncton', 'europe/astrakhan', 'america/dominica', 'america/glace_bay', 'asia/kuching', 'indian/antananarivo', 'america/argentina/cordoba', 'prc', 'asia/urumqi', 'asia/phnom_penh', 'europe/moscow', 'gmt-0', 'asia/qyzylorda', 'indian/mayotte', 'asia/macao', 'america/santarem', 'asia/ulan_bator', 'eet', 'pacific/palau', 'america/puerto_rico', 'america/catamarca', 'canada/mountain', 'asia/colombo', 'africa/malabo', 'etc/gmt', 'europe/samara', 'asia/chongqing', 'asia/ujung_pandang', 'cuba', 'asia/bangkok', 'etc/gmt-10', 'america/dawson_creek', 'asia/dili', 'europe/lisbon', 'australia/adelaide', 'europe/ljubljana', 'asia/ust-nera', 'america/monterrey', 'europe/brussels', 'africa/conakry', 'poland', 'america/regina', 'africa/luanda', 'america/merida', 'america/vancouver', 'america/atikokan', 'europe/rome', 'america/winnipeg', 'indian/chagos', 'asia/shanghai', 'etc/gmt-2', 'etc/gmt-4', 'mst', 'america/virgin', 'antarctica/south_pole', 'america/belem', 'etc/gmt+9', 'america/st_lucia', 'america/buenos_aires', 'pacific/bougainville', 'america/adak', 'australia/currie', 'america/coral_harbour', 'wet', 'america/porto_velho', 'america/tegucigalpa', 'africa/djibouti', 'pacific/pago_pago', 'america/argentina/rio_gallegos', 'asia/khandyga', 'antarctica/syowa', 'indian/reunion', 'atlantic/faeroe', 'america/campo_grande', 'america/kentucky/louisville', 'africa/addis_ababa', 'europe/warsaw', 'brazil/acre', 'pacific/wallis', 'europe/gibraltar', 'america/iqaluit', 'us/east-indiana', 'america/matamoros', 'europe/tirane', 'africa/johannesburg', 'america/eirunepe', 'america/cordoba', 'us/samoa', 'universal', 'america/marigot', 'asia/tbilisi', 'america/scoresbysund', 'asia/tehran', 'pacific/port_moresby', 'etc/gmt+2', 'us/indiana-starke', 'etc/universal', 'asia/kolkata', 'asia/rangoon', 'africa/ndjamena', 'asia/brunei', 'america/cayman', 'roc', 'america/los_angeles', 'us/michigan', 'america/indiana/vevay', 'pacific/norfolk', 'america/lower_princes', 'asia/baghdad', 'america/asuncion', 'europe/copenhagen', 'asia/thimphu', 'asia/manila', 'africa/gaborone', 'america/phoenix', 'gb-eire', 'america/halifax', 'asia/aden', 'asia/dacca', 'europe/malta', 'atlantic/stanley', 'europe/skopje', 'asia/gaza', 'turkey', 'asia/pyongyang', 'america/argentina/la_rioja', 'asia/vladivostok', 'asia/thimbu', 'australia/south', 'pacific/saipan', 'america/cambridge_bay', 'america/argentina/catamarca', 'america/rainy_river', 'africa/douala', 'asia/muscat', 'asia/jayapura', 'america/fort_nelson', 'america/pangnirtung', 'us/mountain', 'america/toronto', 'europe/jersey', 'brazil/east', 'europe/uzhgorod', 'america/montevideo', 'pacific/funafuti', 'america/port_of_spain', 'america/porto_acre', 'portugal', 'america/menominee', 'europe/andorra', 'america/chicago', 'asia/jakarta', 'africa/casablanca', 'us/pacific', 'pacific/honolulu', 'africa/asmara', 'etc/uct', 'europe/athens', 'indian/cocos', 'america/ensenada', 'canada/eastern', 'est5edt', 'etc/gmt+4', 'pacific/efate', 'japan', 'africa/bujumbura', 'gb', 'america/indianapolis', 'australia/broken_hill', 'america/paramaribo', 'australia/melbourne', 'africa/libreville', 'asia/dushanbe', 'atlantic/south_georgia', 'europe/london', 'america/indiana/winamac', 'europe/vilnius', 'america/costa_rica', 'asia/novokuznetsk', 'w-su', 'asia/hebron', 'asia/bishkek', 'america/boise', 'america/lima', 'antarctica/troll', 'chile/easterisland', 'indian/comoro', 'africa/abidjan', 'asia/sakhalin', 'pacific/fakaofo', 'pacific/kwajalein', 'africa/kampala', 'pacific/johnston', 'america/indiana/knox', 'america/recife', 'america/indiana/petersburg', 'etc/gmt0', 'pacific/pitcairn', 'etc/gmt-13', 'europe/belgrade', 'pacific/wake', 'europe/vaduz', 'america/argentina/jujuy', 'europe/paris', 'greenwich', 'africa/brazzaville', 'pacific/guadalcanal', 'us/central', 'america/antigua', 'america/whitehorse', 'europe/zagreb', 'australia/darwin', 'zulu', 'atlantic/reykjavik', 'america/rio_branco', 'asia/dubai', 'america/metlakatla', 'antarctica/rothera', 'america/yellowknife', 'europe/san_marino', 'pacific/majuro', 'canada/atlantic', 'america/nuuk', 'africa/maputo', 'europe/ulyanovsk', 'europe/nicosia', 'us/hawaii', 'brazil/denoronha', 'mexico/general', 'europe/luxembourg', 'met', 'atlantic/cape_verde', 'australia/queensland', 'europe/tallinn', 'africa/nouakchott', 'asia/kuwait', 'utc', 'asia/amman', 'iceland', 'africa/harare', 'singapore', 'asia/atyrau', 'pacific/marquesas', 'etc/gmt+1', 'europe/simferopol', 'america/santiago', 'america/knox_in', 'pacific/tarawa', 'africa/banjul', 'asia/irkutsk', 'etc/gmt+12', 'etc/gmt+10', 'australia/sydney', 'america/kentucky/monticello', 'america/mazatlan', 'mexico/bajanorte', 'america/barbados', 'asia/yerevan', 'america/guadeloupe', 'antarctica/dumontdurville', 'europe/stockholm', 'us/aleutian', 'america/bahia_banderas', 'etc/gmt-8', 'europe/prague', 'europe/zaporozhye', 'africa/lubumbashi', 'canada/saskatchewan', 'africa/bissau', 'iran', 'australia/lhi', 'pacific/chatham', 'africa/lagos', 'america/juneau', 'america/blanc-sablon', 'europe/mariehamn', 'pacific/enderbury', 'america/edmonton', 'asia/pontianak', 'europe/busingen', 'america/chihuahua', 'america/havana', 'etc/gmt+11', 'hst', 'us/alaska', 'gmt0', 'uct', 'asia/tokyo', 'antarctica/mawson', 'pacific/pohnpei', 'etc/gmt-7', 'pacific/rarotonga', 'arctic/longyearbyen', 'america/martinique', 'etc/gmt+3', 'america/grenada', 'america/denver', 'asia/famagusta', 'canada/yukon', 'etc/gmt-14', 'america/north_dakota/beulah', 'america/curacao', 'america/santo_domingo', 'australia/act', 'america/north_dakota/new_salem', 'asia/katmandu', 'asia/aqtobe', 'europe/minsk', 'pacific/kosrae', 'asia/omsk', 'america/argentina/salta', 'america/hermosillo', 'america/argentina/tucuman', 'asia/bahrain', 'europe/zurich', 'antarctica/palmer', 'asia/almaty', 'america/port-au-prince', 'asia/baku', 'africa/tunis', 'europe/belfast', 'america/atka', 'america/shiprock', 'america/santa_isabel', 'asia/tashkent', 'pacific/tahiti', 'europe/chisinau', 'indian/christmas', 'america/kralendijk', 'america/st_barthelemy', 'asia/ashkhabad', 'canada/pacific', 'jamaica', 'america/bogota', 'atlantic/madeira', 'etc/gmt-1', 'america/swift_current', 'kwajalein', 'asia/qostanay', 'america/thule', 'antarctica/davis', 'america/yakutat', 'america/jamaica', 'america/fort_wayne', 'africa/dakar', 'pacific/midway', 'america/jujuy', 'pacific/tongatapu', 'europe/monaco', 'europe/guernsey', 'australia/perth', 'europe/sarajevo', 'indian/kerguelen', 'america/maceio', 'asia/choibalsan', 'america/guyana', 'america/argentina/san_luis', 'america/guayaquil', 'asia/yakutsk', 'africa/kigali', 'asia/yekaterinburg', 'asia/kashgar', 'america/mexico_city', 'asia/singapore', 'pacific/fiji', 'navajo', 'asia/kabul', 'africa/asmera', 'asia/ulaanbaatar', 'america/rankin_inlet', 'america/thunder_bay', 'asia/chita', 'asia/kathmandu', 'america/managua', 'africa/blantyre', 'nz', 'asia/macau', 'etc/gmt+0', 'europe/kirov', 'america/cuiaba', 'america/argentina/san_juan', 'atlantic/st_helena', 'europe/saratov', 'antarctica/casey', 'america/boa_vista', 'pacific/kiritimati', 'etc/gmt-11', 'america/panama', 'gmt+0', 'america/st_thomas', 'africa/freetown', 'pacific/easter', 'america/belize', 'antarctica/mcmurdo', 'america/ciudad_juarez', 'america/north_dakota/center', 'america/mendoza', 'asia/damascus', 'europe/kyiv', 'europe/bucharest', 'atlantic/faroe', 'africa/el_aaiun', 'asia/jerusalem', 'atlantic/bermuda', 'africa/ceuta', 'europe/kaliningrad', 'america/miquelon', 'africa/sao_tome', 'africa/tripoli', 'australia/north', 'asia/nicosia', 'australia/yancowinna', 'indian/maldives', 'europe/helsinki', 'australia/hobart', 'america/montserrat', 'europe/kiev', 'europe/vienna', 'pacific/ponape', 'us/arizona', 'america/guatemala', 'australia/west', 'america/godthab', 'africa/bangui', 'america/tortola', 'europe/dublin', 'asia/seoul', 'pacific/apia', 'egypt', 'asia/beirut', 'america/indiana/vincennes', 'pacific/chuuk', 'africa/bamako', 'africa/maseru', 'asia/taipei', 'australia/eucla', 'eire', 'america/louisville', 'canada/newfoundland', 'pacific/kanton', 'rok', 'pacific/samoa', 'cet', 'america/creston', 'america/anguilla', 'pacific/galapagos', 'etc/gmt-12', 'america/danmarkshavn', 'pacific/gambier', 'america/argentina/ushuaia', 'australia/lord_howe', 'asia/makassar', 'asia/vientiane', 'europe/bratislava', 'asia/barnaul', 'america/nipigon', 'africa/mbabane', 'america/argentina/comodrivadavia', 'europe/madrid', 'australia/tasmania', 'etc/gmt-3', 'america/punta_arenas', 'asia/magadan', 'america/ojinaga', 'atlantic/azores', 'hongkong', 'africa/juba', 'america/st_kitts', 'asia/ashgabat', 'asia/calcutta', 'australia/lindeman', 'africa/lusaka', 'asia/riyadh', 'america/st_vincent', 'america/cayenne', 'europe/amsterdam', 'asia/saigon', 'asia/ho_chi_minh', 'nz-chat', 'europe/podgorica', 'america/bahia', 'europe/sofia', 'asia/oral', 'africa/timbuktu', 'america/new_york', 'asia/aqtau', 'america/grand_turk', 'antarctica/vostok', 'asia/kamchatka', 'africa/lome', 'america/goose_bay', 'america/noronha', 'africa/monrovia', 'canada/central', 'chile/continental', 'asia/krasnoyarsk', 'cst6cdt', 'africa/windhoek', 'asia/samarkand', 'asia/novosibirsk', 'asia/dhaka', 'asia/karachi', 'america/el_salvador', 'libya', 'asia/hong_kong', 'indian/mauritius', 'africa/khartoum', 'etc/greenwich', 'africa/kinshasa', 'europe/oslo', 'america/argentina/buenos_aires', 'gmt', 'asia/kuala_lumpur', 'america/nassau', 'asia/anadyr', 'etc/zulu', 'brazil/west', 'etc/gmt+8', 'mst7mdt', 'asia/harbin', 'europe/berlin', 'etc/gmt-5', 'antarctica/macquarie', 'africa/mogadishu', 'america/la_paz', 'europe/istanbul', 'america/tijuana', 'asia/tomsk', 'asia/qatar', 'america/st_johns', 'israel', 'america/anchorage', 'pacific/niue', 'america/inuvik', 'australia/nsw', 'europe/volgograd', 'america/dawson', 'etc/gmt+6', 'america/cancun', 'europe/vatican', 'etc/gmt-0', 'africa/cairo', 'indian/mahe', 'etc/utc', 'pacific/auckland', 'asia/istanbul', 'africa/porto-novo', 'africa/ouagadougou', 'pacific/noumea', 'america/rosario', 'america/resolute', 'etc/gmt+7', 'europe/tiraspol', 'pst8pdt', 'asia/tel_aviv', 'australia/brisbane'}
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