# FineType v2 — Datetime Domain Definitions # # Hierarchy: domain.category.type (locale is a field, not in the key) # Full label at inference time: domain.category.type.LOCALE # # Each definition is a transformation contract: # - broad_type: Target DuckDB type # - format_string: DuckDB strptime format (null if not strptime-based) # - transform: DuckDB SQL expression ({col} = column placeholder) # - transform_ext: Enhanced transform requiring a DuckDB extension # - validation: JSON Schema fragment for data quality checks # - tier: Path from root to parent in the inference graph # - decompose: Optional struct expansion for multi-field output # # v1 migration notes are in `aliases` and `notes` fields. # ───────────────────────────────────────────────────────────────────── # ═════════════════════════════════════════════════════════════════════ # CATEGORY: timestamp # # Combined date + time formats. All resolve to DuckDB TIMESTAMP or # TIMESTAMPTZ. These are the most common datetime formats encountered # in analytics pipelines. # ═════════════════════════════════════════════════════════════════════ datetime.timestamp.iso_8601: title: ISO 8601 description: > Standard international datetime format with UTC indicator. The most widely used machine-readable datetime format. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%dT%H:%M:%SZ" format_string: "%Y-%m-%dT%H:%M:%SZ" format_string_alt: "%Y-%m-%dT%H:%M:%S.%gZ" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%SZ')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}(\\.\\d{1,6})?Z$" minLength: 20 maxLength: 27 tier: [TIMESTAMP, timestamp] aliases: [big_endian] samples: - "2024-01-15T10:30:00Z" - "2019-12-31T23:59:59Z" - "2023-06-01T00:00:00Z" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601-1:2019" link: "https://www.iso.org/standard/70907.html" - title: "W3C Date and Time Formats" link: "https://www.w3.org/TR/NOTE-datetime" notes: null datetime.timestamp.iso_8601_compact: title: ISO 8601 Compact description: > Compact ISO 8601 without separators. Common in filenames, log identifiers, and systems with character constraints. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y%m%dT%H%M%S" format_string: "%Y%m%dT%H%M%S" transform: "strptime({col}, '%Y%m%dT%H%M%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{8}T\\d{6}$" minLength: 15 maxLength: 15 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "20240115T103000" - "20191231T235959" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601-1:2019" link: "https://www.iso.org/standard/70907.html" notes: null datetime.timestamp.iso_8601_microseconds: title: ISO 8601 with Microseconds description: > Extended ISO 8601 with microsecond precision and UTC indicator. Common in high-precision logging and scientific data. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%dT%H:%M:%S.%fZ" format_string: "%Y-%m-%dT%H:%M:%S.%fZ" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S.%fZ')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{6}Z$" minLength: 27 maxLength: 27 tier: [TIMESTAMP, timestamp] aliases: [iso_8601_ext] samples: - "2024-01-15T10:30:00.123456Z" - "2019-12-31T23:59:59.000000Z" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601-1:2019" link: "https://www.iso.org/standard/70907.html" notes: "v1 name: datetime.iso_8601_ext" datetime.timestamp.iso_8601_offset: title: ISO 8601 with Timezone Offset description: > ISO 8601 datetime with explicit timezone offset instead of Z. Preserves the original timezone information. The offset is a sign, an hour and optional minutes. The colon form (+05:30) or the hour alone (+05) is canonical; the form without a colon (+0530) and a one-digit hour (+5:30) are accepted with a note. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%Y-%m-%dT%H:%M:%S%z" format_string: "%Y-%m-%dT%H:%M:%S%z" transform: "CAST(regexp_replace(upper({col}), '([+-])([0-9]):([0-9]{2})$', '\\10\\2:\\3') AS TIMESTAMPTZ)" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}[Tt]\\d{2}:\\d{2}:\\d{2}[+-](?:\\d{2}(?::?\\d{2})?|\\d:\\d{2})$" validation_notes: canonical: kind: nonstandard name: extended-format offset pattern: "^\\d{4}-\\d{2}-\\d{2}[Tt]\\d{2}:\\d{2}:\\d{2}[+-]\\d{2}(?::\\d{2})?$" tier: [TIMESTAMPTZ, timestamp] aliases: [iso_8601_with_time_zone_name] samples: - "2024-01-15T10:30:00+05:00" - "2023-12-29T10:45:30-05:00" - "2024-06-15T12:00:00+00:00" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601-1:2019" link: "https://www.iso.org/standard/70907.html" notes: > v1 name: datetime.iso_8601_with_time_zone_name. The published format strings describe the upper-case-T, colon-offset form and do not parse lower-case t, an offset without a colon or a one-digit hour; those cast through the transform. Graded: lower-case t and the hour-only offset (+05) conform; noted are the offset without a colon (+0530) and a one-digit hour (+5:30). datetime.timestamp.rfc_2822: title: RFC 2822 description: > Email header datetime format. Used in SMTP, HTTP headers, and RSS feeds. Includes abbreviated weekday and month names. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%a, %d %b %Y %H:%M:%S %z" format_string: "%a, %d %b %Y %H:%M:%S %z" transform: "strptime({col}, '%a, %d %b %Y %H:%M:%S %z')" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]{2}, \\d{2} [A-Z][a-z]{2} \\d{4} \\d{2}:\\d{2}:\\d{2} .+$" tier: [TIMESTAMPTZ, timestamp] aliases: [] samples: - "Tue, 15 Jan 2024 10:30:00 +0000" - "Sun, 31 Dec 2023 23:59:59 +0000" icon: clock canonical: "https://www.rfc-editor.org/rfc/rfc2822" references: - title: "RFC 2822 - Internet Message Format" link: "https://www.rfc-editor.org/rfc/rfc2822" notes: null datetime.timestamp.rfc_3339: title: RFC 3339 (Space Separator) description: > Internet datetime format, a profile of ISO 8601 that allows a space separator between date and time (RFC 3339 §5.6). Used in Atom feeds, JSON APIs, and modern web services. Distinguished from iso_8601_offset by the space separator: RFC 3339 uses space, ISO 8601 requires T. Disambiguation: Tier 2 timestamp model detects T vs space. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%Y-%m-%d %H:%M:%S%z" format_string: "%Y-%m-%d %H:%M:%S%z" format_string_alt: "%Y-%m-%d %H:%M:%SZ" transform: "CAST(regexp_replace(upper({col}), ' ([+-]|Z)', '\\1') AS TIMESTAMPTZ)" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}(?:\\.\\d+)? ?(?:[Zz]|[+-]\\d{2}:?\\d{2})$" validation_notes: canonical: kind: nonstandard name: RFC 3339 offset pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}(?:\\.\\d+)?(?:[Zz]|[+-]\\d{2}:\\d{2})$" tier: [TIMESTAMPTZ, timestamp] aliases: [] samples: - "2024-01-15 10:30:00+00:00" - "2019-12-31 23:59:59-05:00" - "2023-06-01 12:00:00+09:00" - "2024-03-15 10:30:00Z" icon: clock canonical: "https://www.rfc-editor.org/rfc/rfc3339" references: - title: "RFC 3339 - Date and Time on the Internet" link: "https://www.rfc-editor.org/rfc/rfc3339" notes: > RFC 3339 §5.6 allows both T and space as date/time separator. For classification purposes, space-separated = rfc_3339, T-separated = iso_8601_offset. This enables Tier 2 disambiguation via a simple character check. format_string_alt handles the Z (UTC) timezone indicator; primary format_string handles numeric offsets. DuckDB %z handles +HH:MM but not Z, hence the alt format. The transform casts instead, after upper-casing z and closing up a space before the offset or Z. The published format strings do not parse a fraction of a second, a lower-case z or a space before the offset or Z; those cast through the transform. Graded: a fraction and a lower-case z conform, as RFC 3339 allows them; noted are an offset without a colon (+0530) and a space before the offset or Z. datetime.timestamp.sql_standard: title: SQL Standard description: > Common SQL datetime format used in PostgreSQL, MySQL, and DuckDB. Space-separated date and time without timezone. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%d %H:%M:%S" format_string: "%Y-%m-%d %H:%M:%S" transform: "strptime({col}, '%Y-%m-%d %H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}$" minLength: 19 maxLength: 19 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2024-01-15 10:30:00" - "2019-12-31 23:59:59" icon: clock canonical: "https://www.iso.org/standard/76583.html" references: - title: "SQL Standard (ISO/IEC 9075)" link: "https://www.iso.org/standard/76583.html" notes: null datetime.timestamp.sql_minutes: title: SQL Minute-Precision Datetime description: > Space-separated date and time to minute precision, without seconds or timezone (e.g. 2024-01-15 10:30). The minute-precision sibling of sql_standard; DuckDB parses it natively as a TIMESTAMP (padding :00). Common in spreadsheet and database exports that drop the seconds field. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%d %H:%M" format_string: "%Y-%m-%d %H:%M" transform: "strptime({col}, '%Y-%m-%d %H:%M')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}$" minLength: 16 maxLength: 16 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2024-01-15 10:30" - "2019-12-31 23:59" icon: clock canonical: "https://www.iso.org/standard/76583.html" references: - title: "SQL Standard (ISO/IEC 9075)" link: "https://www.iso.org/standard/76583.html" notes: > Added 2026-07-11 (minute-precision timestamp family completion). Add-not- broaden: sql_standard keeps its seconds-required pattern so round-trip transforms stay correct; this leaf owns the zoneless minute form. Recovered deterministically by datetime_format_refinement (the 244-dim model does not predict it). Sibling of dmy_hm (slash/day-first minute) and iso_minutes (ISO-T minute). datetime.timestamp.mdy_12h: title: MDY 12-Hour Datetime description: > Month/Day/Year datetime with 12-hour clock and AM/PM indicator. Month-first ordering (MM/DD/YYYY). designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%m/%d/%Y %I:%M %p" format_string: "%m/%d/%Y %I:%M %p" transform: "strptime({col}, '%m/%d/%Y %I:%M %p')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{4} \\d{2}:\\d{2} [AP]M$" tier: [TIMESTAMP, timestamp] aliases: [middle_endian] samples: - "01/15/2024 10:30 AM" - "12/31/2019 11:59 PM" icon: clock references: - title: "Date format by country - United States" link: "https://en.wikipedia.org/wiki/Date_format_by_country#United_States" notes: null datetime.timestamp.mdy_24h: title: MDY 24-Hour Datetime description: > Month/Day/Year date ordering (MM/DD/YYYY) with 24-hour time. Common in US military, healthcare, and technical systems. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%m/%d/%Y %H:%M:%S" format_string: "%m/%d/%Y %H:%M:%S" transform: "strptime({col}, '%m/%d/%Y %H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{4} \\d{2}:\\d{2}:\\d{2}$" tier: [TIMESTAMP, timestamp] aliases: [formatted_datetime] samples: - "01/15/2024 10:30:00" - "12/31/2019 23:59:59" icon: clock references: null notes: "v1 name: datetime.formatted_datetime" datetime.timestamp.dmy_hm: title: DMY Hour-Minute Datetime description: > Day/Month/Year datetime with 24-hour clock. Day-first ordering (DD/MM/YYYY). Used across the EU and many Commonwealth countries. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%d/%m/%Y %H:%M" format_string: "%d/%m/%Y %H:%M" transform: "strptime({col}, '%d/%m/%Y %H:%M')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{4} \\d{2}:\\d{2}$" tier: [TIMESTAMP, timestamp] aliases: [little_endian] samples: - "15/01/2024 10:30" - "31/12/2019 23:59" icon: clock references: - title: "Date format by country - Europe" link: "https://en.wikipedia.org/wiki/Date_format_by_country#Europe" notes: null datetime.timestamp.iso_microseconds: title: ISO Datetime with Microseconds (no timezone) description: > ISO 8601-like format with microsecond precision but no timezone indicator. Common in database exports and log files. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%dT%H:%M:%S.%f" format_string: "%Y-%m-%dT%H:%M:%S.%f" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S.%f')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{6}$" minLength: 26 maxLength: 26 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2024-01-15T10:30:00.123456" - "2019-12-31T23:59:59.000000" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: "v1 name: datetime.datetime. Distinct from iso_8601_microseconds (has no trailing Z)." datetime.timestamp.iso_seconds: title: ISO Datetime, second precision (no timezone) description: > ISO 8601-like format with second precision and no timezone indicator (e.g. 2013-06-04T01:02:03). Common in database exports and log files. The zoneless sibling of iso_8601 (which requires a trailing Z). designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%dT%H:%M:%S" format_string: "%Y-%m-%dT%H:%M:%S" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}$" minLength: 19 maxLength: 19 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2013-06-04T01:02:03" - "2024-01-15T10:30:00" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: > New in v0.6.36 (spec 2026-06-19-zoneless-iso-datetime-leaves). Zoneless second-precision sibling of iso_8601, mirroring iso_microseconds. Add-not- broaden: the zoned leaves keep their Z-required patterns so round-trip transforms stay correct. Distinct from epoch.unix_seconds (numeric epoch, not an ISO string). datetime.timestamp.iso_minutes: title: ISO Datetime, minute precision (no timezone) description: > ISO 8601-like format with minute precision (T separator) and no timezone indicator (e.g. 2013-06-04T01:02). The minute-precision sibling of iso_seconds; common in exports that drop the seconds field. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%dT%H:%M" format_string: "%Y-%m-%dT%H:%M" transform: "strptime({col}, '%Y-%m-%dT%H:%M')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}$" minLength: 16 maxLength: 16 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2013-06-04T01:02" - "2024-01-15T10:30" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: > Added 2026-07-11 (minute-precision timestamp family completion). Zoneless minute-precision sibling of iso_seconds, mirroring the seconds/millis/ micros ladder. Add-not-broaden: the zoned iso_8601 leaves keep their Z-required patterns. Recovered deterministically by datetime_format_refinement (the 244-dim model does not predict it). datetime.timestamp.iso_milliseconds: title: ISO Datetime with Milliseconds (no timezone) description: > ISO 8601-like format with millisecond precision but no timezone indicator (e.g. 2013-06-04T01:02:03.123). Common in database exports and log files. The zoneless sibling of iso_8601_milliseconds. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "any" format_string: "%Y-%m-%dT%H:%M:%S.%g" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S.%g')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{3}$" minLength: 23 maxLength: 23 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2013-06-04T01:02:03.123" - "2024-01-15T10:30:00.500" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: > New in v0.6.36 (spec 2026-06-19-zoneless-iso-datetime-leaves). Zoneless millisecond sibling of iso_8601_milliseconds, mirroring iso_microseconds. Distinct from iso_8601_milliseconds (has the trailing Z). datetime.timestamp.rfc_2822_ordinal: title: RFC 2822 with Ordinal Indicators description: > RFC 2822 variant with ordinal day suffixes (1st, 2nd, 3rd, 4th). Non-standard but encountered in human-readable email headers and legacy systems. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "any" format_string: null transform: "strptime(regexp_replace({col}, '(\\d+)(?:st|nd|rd|th)', '\\1'), '%d %b %Y %H:%M:%S %z')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{1,2}(?:st|nd|rd|th) [A-Z][a-z]{2} \\d{4} \\d{2}:\\d{2}:\\d{2} [+-]\\d{4}$" tier: [TIMESTAMPTZ, timestamp] aliases: [rfc_2822_with_ordinals] samples: - "15th Jan 2024 10:30:00 +0000" - "1st Jan 2020 00:00:00 +0000" - "22nd Jul 2028 21:48:59 +0000" icon: clock canonical: "https://www.rfc-editor.org/rfc/rfc2822" references: - title: "RFC 2822 - Internet Message Format" link: "https://www.rfc-editor.org/rfc/rfc2822" notes: > No direct strptime support — ordinal suffix requires preprocessing. Transform strips ordinal suffixes (st/nd/rd/th) via regexp_replace before parsing with strptime. # ═════════════════════════════════════════════════════════════════════ # CATEGORY: date # # Date-only formats (no time component). All resolve to DuckDB DATE. # Separator style and component ordering are the key discriminators. # ═════════════════════════════════════════════════════════════════════ datetime.date.iso: title: ISO Date description: > ISO 8601 date format. The universal default for date representation in databases, APIs, and data interchange. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%Y-%m-%d" format_string: "%Y-%m-%d" transform: "strptime({col}, '%Y-%m-%d')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "2024-01-15" - "2019-12-31" - "2000-01-01" icon: calendar canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601-1:2019" link: "https://www.iso.org/standard/70907.html" notes: "v1 name: datetime.date" datetime.date.mdy_slash: title: MDY Date (slash-separated) description: > Month/Day/Year with slash separators. Standard US date format. Ambiguous with DMY format for days 1-12 — column-mode inference uses value distribution to disambiguate. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%m/%d/%Y" format_string: "%m/%d/%Y" transform: "strptime({col}, '%m/%d/%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{4}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [formatted_date] samples: - "01/15/2024" - "12/31/2019" icon: calendar references: null notes: "v1 name: datetime.formatted_date. Ambiguous with dmy_slash when day <= 12." datetime.date.dmy_slash: title: DMY Date (slash-separated) description: > Day/Month/Year with slash separators. Standard format across Europe, Australia, and much of Asia. Ambiguous with MDY format for days 1-12. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d/%m/%Y" format_string: "%d/%m/%Y" transform: "strptime({col}, '%d/%m/%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{4}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "15/01/2024" - "31/12/2019" icon: calendar references: null notes: > NEW in v2. Critical for non-US data. Ambiguous with mdy_slash when day <= 12 — column-mode inference resolves by checking if any value has first component > 12. datetime.date.dmy_dot: title: DMY Date (dot-separated) description: > Day.Month.Year with dot separators. Common in Germany, Austria, Switzerland, and Eastern European countries. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d.%m.%Y" format_string: "%d.%m.%Y" transform: "strptime({col}, '%d.%m.%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}\\.\\d{2}\\.\\d{4}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "15.01.2024" - "31.12.2019" icon: calendar references: null notes: "NEW in v2. Common in German-speaking and Eastern European data." datetime.date.short_dmy: title: Short Date (Day-Month-Year, 2-digit year) description: > Two-digit year, day-first. Dash-separated. Common in legacy systems and short-form data entry. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d-%m-%y" format_string: "%d-%m-%y" transform: "strptime({col}, '%d-%m-%y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}-\\d{2}-\\d{2}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "15-01-24" - "31-12-99" icon: calendar references: null notes: > Ambiguous with short_mdy in single-value mode (same regex pattern). Column-mode disambiguation: if any value has first component > 12, it's DMY. COLUMN_MODE_DISAMBIGUATION_TARGET: short_dmy vs short_mdy datetime.date.short_mdy: title: Short Date (Month-Day-Year, 2-digit year) description: > Two-digit year, month-first (US ordering). Dash-separated. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%m-%d-%y" format_string: "%m-%d-%y" transform: "strptime({col}, '%m-%d-%y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}-\\d{2}-\\d{2}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "01-15-24" - "12-31-99" icon: calendar references: null notes: > Same regex as short_dmy — requires column-mode inference to disambiguate. COLUMN_MODE_DISAMBIGUATION_TARGET: short_dmy vs short_mdy datetime.date.short_ymd: title: Short Date (Year-Month-Day, 2-digit year) description: > Two-digit year, year-first (ISO-like ordering). Dash-separated. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%y-%m-%d" format_string: "%y-%m-%d" transform: "strptime({col}, '%y-%m-%d')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}-\\d{2}-\\d{2}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "24-01-15" - "99-12-31" icon: calendar references: null notes: null datetime.date.compact_ymd: title: Compact Date (YYYYMMDD) description: > Eight-digit date with no separators, year-first. Common in file naming, partition keys, and data warehousing (e.g. 20240115). designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%Y%m%d" format_string: "%Y%m%d" transform: "strptime({col}, '%Y%m%d')::DATE" transform_ext: null decompose: null validation: type: string # Range-carrying, not shape-only. `^\d{8}$` confirmed EVERY eight-digit # token, so an eight-digit financial figure (grossProfit 71132000, # sharesOutstanding 25012600) and an eight-digit surrogate key # (NBA GAME_ID 21601092, PostId 18502653) validated as a date. The MONTH # and DAY windows carry the whole discrimination — month 01-12, day 01-31. # # The year stays `\d{4}` ON PURPOSE. A century window (`(19|20)\d{2}`) # rejects genuine historical dates: an archival YYYYMMDD column of 1865- # 1872 values loses the label, the strptime transform is stripped, and it # ships as a confident integer — the same confident-and-wrong failure this # validator exists to stop, pointed the other way. The sibling # `compact_ym` has likewise always been `^\d{4}(0[1-9]|1[0-2])$`: month # window only, no year window. # # Deliberately NOT per-month day counts — 20240230 still validates; a # calendar-exact regex buys ~nothing over the windows. pattern: "^\\d{4}(0[1-9]|1[0-2])(0[1-9]|[12]\\d|3[01])$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [numeric_ymd] samples: - "20240115" - "20191231" icon: calendar references: null notes: "v1 name: date.numeric_ymd" datetime.date.compact_dmy: title: Compact Date (DDMMYYYY) description: > Eight-digit date with no separators, day-first. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d%m%Y" format_string: "%d%m%Y" transform: "strptime({col}, '%d%m%Y')::DATE" transform_ext: null decompose: null validation: type: string # Range-carrying, not shape-only — the same discipline the year-first # sibling `compact_ymd` took, applied to the day-first leaf. # # Tightening ONLY `compact_ymd` did not remove the eight-digit false # positive, it RELOCATED it. Under a shape-only `^\d{8}$` here the # day-first leaf confirmed every eight-digit token, so the columns the # year-first tightening pushed off `compact_ymd` landed on this label # instead — and landed there with HIGHER confidence than the integer # reading they had before. Profiled through the CLI: the twenty corpus # values `compact_ymd`'s own REJECT set is built from typed # `representation.numeric.integer_number` at 0.53 before that change and # `datetime.date.compact_dmy` at 0.91 after it, with a `%d%m%Y` strptime # transform attached. A low-confidence integer became a high-confidence # date; for those values the fix was worse than the defect. # # DAY window 01-31 (leading pair), MONTH window 01-12 (middle pair). # Those two carry the whole discrimination. # # The year stays `\d{4}` ON PURPOSE, for the reason a reviewer proved on # `compact_ymd`: a century window (`(19|20)\d{2}`) hard-vetoes genuine # nineteenth-century dates off the label, strips the strptime transform, # and ships an archival date column as a confident integer. That is the # same confident-and-wrong failure pointed the other way. `compact_ym` is # likewise `^\d{4}(0[1-9]|1[0-2])$` — month window only, no year window. # # Deliberately NOT per-month day counts — 31021999 still validates; a # calendar-exact regex buys ~nothing over the windows. pattern: "^(0[1-9]|[12]\\d|3[01])(0[1-9]|1[0-2])\\d{4}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [numeric_dmy] samples: - "15012024" - "31122019" icon: calendar references: null notes: > v1 name: date.numeric_dmy. Same regex as compact_ymd/compact_mdy. COLUMN_MODE_DISAMBIGUATION_TARGET: compact_dmy vs compact_mdy vs compact_ymd datetime.date.compact_mdy: title: Compact Date (MMDDYYYY) description: > Eight-digit date with no separators, month-first (US ordering). designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%m%d%Y" format_string: "%m%d%Y" transform: "strptime({col}, '%m%d%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{8}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [numeric_mdy] samples: - "01152024" - "12312019" icon: calendar references: null notes: > v1 name: date.numeric_mdy. Same regex as compact_ymd/compact_dmy. COLUMN_MODE_DISAMBIGUATION_TARGET: compact_dmy vs compact_mdy vs compact_ymd datetime.date.abbreviated_month: title: Abbreviated Month Date description: > Date with an abbreviated month name, month-first (Feb 4, 2023) or day-first (4 Feb 2023, 4 Feb 23). Locale-specific because month abbreviations vary by language (Jan vs janv. vs ene). The month token is any run of three or more Latin letters with an optional full stop, which takes the abbreviations of Unicode CLDR in en (Feb), en-GB (Sept), fr (janv.) and the other locales, and the dotted AP-style form (Feb. 4, 2023). designation: locale_specific locales: - EN - FR - DE - ES - IT - PT - NL - SV - DA - "NO" - PL - CS - RU - JA - ZH broad_type: DATE frictionless: type: date format: "%b %d, %Y" format_string: "%b %d, %Y" transform: "strptime(regexp_replace({col}, '\\bSept\\b', 'Sep'), ['%b %d, %Y', '%b. %d, %Y', '%d %b %y', '%d %b. %y', '%d %b %Y', '%d %b. %Y'])::DATE" transform_ext: null decompose: null validation: type: string pattern: "^(?:[A-Za-z\u00c0-\u00d6\u00d8-\u00f6\u00f8-\u00ff]{3,}\\.? \\d{1,2},? \\d{4}|\\d{1,2}\\.? [A-Za-z\u00c0-\u00d6\u00d8-\u00f6\u00f8-\u00ff]{3,}\\.? (?:\\d{4}|\\d{2}))$" tier: [DATE, date] aliases: [] samples: - "Jan 15, 2024" - "15 Jan 2024" - "Dec 31, 2019" icon: calendar references: null notes: > The month token is letter-based, so French and other non-English abbreviations are recognised but not castable: the transform uses DuckDB strptime, whose %b reads English month names, so a column of 4 janv. 2023 validates and stays text. The transform first rewrites Sept (en-GB) to Sep. Per-locale samples needed for non-EN locales. datetime.date.long_full_month: title: Long Date with Full Month Name description: > Date with full month name. Locale-specific because month names vary by language (January vs janvier vs Januar). designation: locale_specific locales: - EN - FR - DE - ES - IT - PT - NL - SV - DA - "NO" - PL - CS - RU broad_type: DATE frictionless: type: date format: "%B %d, %Y" format_string: "%B %d, %Y" transform: "strptime({col}, '%B %d, %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^(?:[A-Za-z]+ \\d{1,2},? \\d{4}|\\d{1,2}\\.? [A-Za-z]+ \\d{4})$" tier: [DATE, date] aliases: [long_full_month_name] samples: - "January 15, 2024" - "June 1, 2000" - "December 31, 2019" icon: calendar references: null notes: "v1 name: date.long_full_month_name" datetime.date.weekday_abbreviated_month: title: Full Weekday with Abbreviated Month description: > Full weekday name followed by abbreviated month. Locale-specific for both weekday names and month abbreviations. designation: locale_specific locales: - EN - FR - DE - ES - IT - PT broad_type: DATE frictionless: type: date format: "%A, %b %d, %Y" format_string: "%A, %b %d, %Y" transform: "strptime({col}, ['%A, %b %d, %Y', '%A, %d %b %Y'])::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Za-z]+[, ]+(?:[A-Za-z]{3,} \\d{1,2},? \\d{4}|\\d{1,2}\\.? [A-Za-z]{3,} \\d{4})$" tier: [DATE, date] aliases: [full_weekday_abbreviated_month] samples: - "Monday, Jan 15, 2024" - "Tuesday, 31 Dec 2019" icon: calendar references: null notes: "v1 name: date.full_weekday_abbreviated_month" datetime.date.weekday_full_month: title: Full Weekday with Full Month Name description: > Full weekday name followed by full month name. The most human-readable date format. Locale-specific. designation: locale_specific locales: - EN - FR - DE - ES - IT - PT broad_type: DATE frictionless: type: date format: "%A, %B %d, %Y" format_string: "%A, %B %d, %Y" transform: "strptime({col}, '%A, %B %d, %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Za-z]+[, ]+(?:[A-Za-z]+ \\d{1,2},? \\d{4}|\\d{1,2}\\.? [A-Za-z]+ \\d{4})$" tier: [DATE, date] aliases: [long_weekday_month_name] samples: - "Monday, January 15, 2024" - "Tuesday, December 31, 2019" icon: calendar references: null notes: "v1 name: date.long_weekday_month_name" datetime.date.ordinal: title: Ordinal Date description: > ISO 8601 ordinal date: year and day-of-year (1-366). Used in astronomy, military, and some industrial systems. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%Y-%j" format_string: "%Y-%j" transform: "strptime({col}, '%Y-%j')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{3}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "2024-015" - "2019-365" - "2020-366" icon: calendar references: - title: "ISO 8601-1:2019" link: "https://www.iso.org/standard/70907.html" notes: null datetime.date.julian: title: Julian Date description: > Short-form Julian date with 2-digit year and 3-digit day-of-year. Common in manufacturing, logistics, and food expiration dates. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: string format_string: null transform: "strptime(concat('20', {col}), '%Y-%j')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}-\\d{3}$" minLength: 6 maxLength: 6 tier: [DATE, date] aliases: [] samples: - "24-015" - "23-365" icon: calendar references: - title: "Julian Day Number" link: "https://en.wikipedia.org/wiki/Julian_day" notes: > No direct strptime support for 2-digit year + day-of-year. Transform prepends '20' to get 4-digit year, then parses with %Y-%j. datetime.date.iso_week: title: ISO Week Date description: > ISO 8601 week date format (YYYY-Www). Used in business planning, fiscal calendars, and European retail analytics. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%G-W%V" format_string: "%G-W%V" transform: "strptime({col} || '-1', '%G-W%V-%u')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-W\\d{1,2}$" tier: [DATE, date] aliases: [week_date] samples: - "2024-W03" - "2019-W52" icon: calendar canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601 Week Date" link: "https://en.wikipedia.org/wiki/ISO_week_date" notes: > v1 name: datetime.week_date. Transform appends '-1' (Monday) to produce a full week-date that strptime can parse. # ═════════════════════════════════════════════════════════════════════ # CATEGORY: time # # Time-only formats (no date component). Resolve to DuckDB TIME. # ═════════════════════════════════════════════════════════════════════ datetime.time.iso: title: ISO Time with Microseconds description: > 24-hour time with microsecond precision. Common in high-frequency data, scientific instruments, and log files. designation: universal locales: [UNIVERSAL] broad_type: TIME frictionless: type: time format: "%H:%M:%S.%f" format_string: "%H:%M:%S.%f" transform: "strptime({col}, '%H:%M:%S.%f')::TIME" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}:\\d{2}:\\d{2}\\.\\d{1,9}$" minLength: 10 maxLength: 18 tier: [TIME, time] aliases: [] samples: - "10:30:00.123456" - "23:59:59.000000" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: "v1 name: datetime.time" datetime.time.hms_24h: title: 24-Hour Time (HH:MM:SS) description: > Standard 24-hour time without fractional seconds. The most common time representation in data systems. designation: universal locales: [UNIVERSAL] broad_type: TIME frictionless: type: time format: "%H:%M:%S" format_string: "%H:%M:%S" transform: "strptime({col}, '%H:%M:%S')::TIME" transform_ext: null decompose: null validation: type: string pattern: "^\\d{1,2}:\\d{2}:\\d{2}(\\.\\d{1,6})?$" minLength: 7 maxLength: 15 tier: [TIME, time] aliases: [formatted_time] samples: - "10:30:00" - "23:59:59" - "00:00:00" icon: clock references: null notes: "v1 name: datetime.formatted_time" datetime.time.hm_24h: title: 24-Hour Time (HH:MM) description: > Hours and minutes only, no seconds. Common in schedules, timetables, and user-facing applications. designation: universal locales: [UNIVERSAL] broad_type: TIME frictionless: type: time format: "%H:%M" format_string: "%H:%M" transform: "strptime({col}, '%H:%M')::TIME" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}:\\d{2}$" minLength: 5 maxLength: 5 tier: [TIME, time] aliases: [] samples: - "10:30" - "23:59" - "00:00" icon: clock references: null notes: "NEW in v2." datetime.time.hms_12h: title: 12-Hour Time with AM/PM description: > 12-hour time with AM/PM indicator. Standard in US and some Commonwealth countries. designation: universal locales: [UNIVERSAL] broad_type: TIME frictionless: type: time format: "%I:%M:%S %p" format_string: "%I:%M:%S %p" transform: "strptime({col}, '%I:%M:%S %p')::TIME" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}:\\d{2}:\\d{2} [AP]M$" minLength: 11 maxLength: 11 tier: [TIME, time] aliases: [] samples: - "10:30:00 AM" - "11:59:59 PM" - "12:00:00 AM" icon: clock references: null notes: "NEW in v2." datetime.time.hm_12h: title: 12-Hour Time (HH:MM AM/PM) description: > Hours and minutes with AM/PM, no seconds. Common in US scheduling applications and user interfaces. designation: universal locales: [UNIVERSAL] broad_type: TIME frictionless: type: time format: "%I:%M %p" format_string: "%I:%M %p" transform: "strptime({col}, '%I:%M %p')::TIME" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}:\\d{2} [AP]M$" minLength: 8 maxLength: 8 tier: [TIME, time] aliases: [] samples: - "10:30 AM" - "11:59 PM" icon: clock references: null notes: "NEW in v2." # ═════════════════════════════════════════════════════════════════════ # CATEGORY: epoch # # Numeric timestamp representations. Input is an integer string, # output is TIMESTAMP via arithmetic conversion (not strptime). # ═════════════════════════════════════════════════════════════════════ datetime.epoch.unix_seconds: title: Unix Timestamp (seconds) description: > Seconds since 1970-01-01 00:00:00 UTC. The standard epoch representation used in most programming languages and systems. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: integer format_string: null transform: "to_timestamp({col}::BIGINT)" transform_ext: null decompose: null validation: type: string pattern: "^\\d{10}$" minLength: 10 maxLength: 10 tier: [BIGINT, epoch] aliases: [unix_timestamp, timestamp] samples: - "1705312200" - "1577836799" - "1000000000" icon: clock canonical: "https://pubs.opengroup.org/onlinepubs/9699919799/" references: - title: "The Open Group Base Specifications - Epoch" link: "https://pubs.opengroup.org/onlinepubs/9699919799/" notes: > v1 names: datetime.unix_timestamp, datetime.timestamp. Tier 0 is BIGINT (looks like a number) but broad_type output is TIMESTAMP after transformation. datetime.epoch.unix_milliseconds: title: Unix Timestamp (milliseconds) description: > Milliseconds since epoch. Common in JavaScript (Date.now()), Java (System.currentTimeMillis()), and many APIs. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: integer format_string: null transform: "to_timestamp({col}::BIGINT / 1000)" transform_ext: null decompose: null validation: type: string pattern: "^\\d{13}$" minLength: 13 maxLength: 13 tier: [BIGINT, epoch] aliases: [unix_epoch_in_milliseconds] samples: - "1705312200000" - "1577836799999" icon: clock canonical: "https://pubs.opengroup.org/onlinepubs/9699919799/" references: - title: "The Open Group Base Specifications - Epoch" link: "https://pubs.opengroup.org/onlinepubs/9699919799/" notes: "v1 name: datetime.unix_epoch_in_milliseconds" datetime.epoch.unix_microseconds: title: Unix Timestamp (microseconds) description: > Microseconds since epoch. Used in high-frequency trading, distributed tracing (Jaeger, Zipkin), and InfluxDB. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: integer format_string: null transform: "to_timestamp({col}::BIGINT / 1000000)" transform_ext: null decompose: null validation: type: string pattern: "^\\d{16}$" minLength: 16 maxLength: 16 tier: [BIGINT, epoch] aliases: [] samples: - "1705312200000000" - "1577836799999999" icon: clock canonical: "https://pubs.opengroup.org/onlinepubs/9699919799/" references: null notes: "NEW in v2." # ═════════════════════════════════════════════════════════════════════ # CATEGORY: offset # # Timezone and UTC offset representations. These identify a timezone # but don't represent a point in time on their own. # ═════════════════════════════════════════════════════════════════════ datetime.offset.iana: title: IANA Timezone Name description: > Timezone identifier from the IANA Time Zone Database (tz database). Used in most programming languages and operating systems. designation: universal locales: [UNIVERSAL] broad_type: VARCHAR frictionless: type: string format_string: null transform: "{col}" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-zA-Z]+/[A-Za-z_]+(/[A-Za-z_]+)?$" tier: [VARCHAR, offset] aliases: [timezone] samples: - "America/New_York" - "Europe/London" - "Asia/Tokyo" icon: world canonical: "https://www.iana.org/time-zones" references: - title: "IANA Time Zone Database" link: "https://www.iana.org/time-zones" notes: > v1 name: datetime.timezone. Validation is pattern-only: the tz database has 500+ zones and ships several releases a year, so any hardcoded enum goes stale and silently rejects newly-added zones. The Region/City pattern is the stable structural invariant. Add an enum only if generated from tzdata at build time, never hand-maintained. datetime.offset.timezone_abbreviation: title: Timezone Abbreviation description: > Short alphabetic timezone abbreviation (EST, EDT, CEST, GMT, UTC, …) — the human-readable shorthand distinct from a full IANA zone name (America/New_York) or an explicit UTC offset (UTC +05:00). designation: universal locales: [UNIVERSAL] broad_type: VARCHAR frictionless: type: string format_string: null transform: "{col}" transform_ext: null decompose: null validation: type: string pattern: "^(UTC|GMT|EST|EDT|CST|CDT|MST|MDT|PST|PDT|AKST|AKDT|HST|HAST|HADT|AST|ADT|NST|NDT|CET|CEST|EET|EEST|WET|WEST|BST|IST|WAT|CAT|EAT|SAST|MSK|GST|PKT|ICT|WIB|SGT|HKT|JST|KST|AEST|AEDT|ACST|ACDT|AWST|NZST|NZDT)$" tier: [VARCHAR, offset] aliases: [tz_abbrev, timezone_short] samples: - "EST" - "CEST" - "GMT" icon: world references: null notes: > Unlike IANA zone names, the common timezone abbreviations are a small, structurally-bounded closed set, so an enum-style pattern is the right validator here (the iana note's anti-enum warning applies to the 500+ IANA zones, not these ~40 abbreviations). The abbreviations EST/CST/PST overlap non-timezone uses (estimate/cost), so the Sharpen recovery (structured_string_refinement) gates on a residual source label + >=90% closed-set match + a timezone-ish header — precision lives in the recovery gating, not a looser pattern. Mined from the word/iana residual (spec 2026-06-25-timezone-abbreviation-type): 1,504 distinct corpus datasets. UPPERCASE-ONLY by design: corpus measurement found 1,442/1,444 tz columns uppercase, only 2 lowercase — and case is itself a precision signal ("WET"=Western European Time vs "wet" the word; "EST" vs "estimate"), so case-insensitivity would over-emit far more than the ~2 columns of recall it buys. datetime.offset.utc: title: UTC Offset description: > Explicit UTC offset string (e.g., "UTC +05:00"). Used in configuration files and human-readable timezone displays. designation: universal locales: [UNIVERSAL] broad_type: VARCHAR frictionless: type: string format_string: null transform: "{col}" transform_ext: null decompose: null validation: type: string pattern: "^UTC [+-]\\d{2}:\\d{2}$" tier: [VARCHAR, offset] aliases: [gmt_offset] samples: - "UTC +05:00" - "UTC -08:00" - "UTC +00:00" icon: world canonical: "https://www.iso.org/standard/70907.html" references: null notes: "v1 name: datetime.gmt_offset" # ═════════════════════════════════════════════════════════════════════ # CATEGORY: duration # # Time intervals and durations. Resolve to DuckDB INTERVAL. # ═════════════════════════════════════════════════════════════════════ datetime.duration.iso_8601: title: ISO 8601 Duration description: > Duration in ISO 8601 format (PnYnMnDTnHnMnS or PnW for weeks). Used in iCalendar, XML Schema, YouTube API, Google Calendar, and many APIs. Covers both compact (PT30M) and verbose (P1Y2M3DT4H5M6S) forms. designation: universal locales: [UNIVERSAL] broad_type: INTERVAL frictionless: type: duration format_string: null transform: > CAST( COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)Y', 1), '') || ' years ', '') || COALESCE(NULLIF(REGEXP_EXTRACT({col}, 'P[^T]*?(\d+)M', 1), '') || ' months ', '') || COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)W', 1), '') || ' weeks ', '') || COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)D', 1), '') || ' days ', '') || COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+)H', 1), '') || ' hours ', '') || COALESCE(NULLIF(REGEXP_EXTRACT({col}, 'T.*?(\d+)M', 1), '') || ' minutes ', '') || COALESCE(NULLIF(REGEXP_EXTRACT({col}, '(\d+(?:\.\d+)?)S', 1), '') || ' seconds', '0 seconds') AS INTERVAL) transform_ext: null decompose: null validation: type: string pattern: "^-?P(?=\\d|T\\d)(?:(\\d+)Y)?(?:(\\d+)M)?(?:(\\d+)[DW])?(?:T(?:(\\d+)H)?(?:(\\d+)M)?(?:(\\d+(?:\\.\\d+)?)S)?)?$" tier: [INTERVAL, duration] aliases: [duration, iso_8601_verbose] samples: - "PT30M" - "PT1H30M" - "P1DT12H" - "P1Y6M" - "P2W" - "P1Y2M3DT4H5M6S" icon: hourglass canonical: "https://www.iso.org/standard/70907.html" references: - title: "ISO 8601-1:2019 Durations" link: "https://www.iso.org/standard/70907.html" notes: > v1 name: datetime.duration. Updated to cover full ISO 8601 duration spec including weeks (PnW), negative durations (-P...), and fractional seconds (PT1.5S). Alias iso_8601_verbose added — no separate type needed since same regex covers both compact and verbose forms. # ═════════════════════════════════════════════════════════════════════ # CATEGORY: component # # Individual datetime components. These are fragments, not complete # date/time values. Useful for validation and context, but often # ambiguous in isolation (column-mode inference recommended). # ═════════════════════════════════════════════════════════════════════ datetime.component.year: title: Calendar Year description: > Four-digit year. Ambiguous as a standalone value — could be any integer. Column-mode inference checks range and distribution. designation: broad_numbers locales: [UNIVERSAL] broad_type: SMALLINT frictionless: type: year format_string: null transform: "{col}::SMALLINT" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}(\\.0+)?$" minimum: 1000 maximum: 2100 tier: [INTEGER, component] aliases: [] samples: - "2024" - "1999" - "2000" icon: calendar references: null notes: "v1 name: datetime.year. Was designation: duplicate." datetime.component.day_of_week: title: Day of Week Name description: > Full weekday name. Locale-specific (Monday vs lundi vs Montag). designation: locale_specific locales: - EN - FR - DE - ES - IT - PT - AR - BG - CS - DA - EL - ET - FI - HR - HU - LT - LV - NL - "NO" - PL - RO - RU - SK - SL - SV - TR - UK broad_type: VARCHAR frictionless: type: string format_string: null transform: "{col}" transform_ext: null decompose: null validation: type: string enum: - "Monday" - "Tuesday" - "Wednesday" - "Thursday" - "Friday" - "Saturday" - "Sunday" # CLDR EN abbreviated weekday names — common in tabular data # (`Mon`/`Tue`/.../`Sun`). Case-folded per ac-02. - "Mon" - "Tue" - "Wed" - "Thu" - "Fri" - "Sat" - "Sun" validation_by_locale: # Source: Unicode CLDR (cldr-dates-full, wide day names) # License: Unicode License (permissive, attribution) # Each locale lists the 7 full weekday names in that language. EN: type: string enum: ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"] FR: type: string enum: ["lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi", "dimanche"] DE: type: string enum: ["Montag", "Dienstag", "Mittwoch", "Donnerstag", "Freitag", "Samstag", "Sonntag"] ES: type: string enum: ["lunes", "martes", "miércoles", "jueves", "viernes", "sábado", "domingo"] IT: type: string enum: ["lunedì", "martedì", "mercoledì", "giovedì", "venerdì", "sabato", "domenica"] PT: type: string enum: ["domingo", "segunda-feira", "terça-feira", "quarta-feira", "quinta-feira", "sexta-feira", "sábado"] AR: type: string enum: ["الاثنين", "الثلاثاء", "الأربعاء", "الخميس", "الجمعة", "السبت", "الأحد"] BG: type: string enum: ["понеделник", "вторник", "сряда", "четвъртък", "петък", "събота", "неделя"] CS: type: string enum: ["pondělí", "úterý", "středa", "čtvrtek", "pátek", "sobota", "neděle"] DA: type: string enum: ["mandag", "tirsdag", "onsdag", "torsdag", "fredag", "lørdag", "søndag"] EL: type: string enum: ["Δευτέρα", "Τρίτη", "Τετάρτη", "Πέμπτη", "Παρασκευή", "Σάββατο", "Κυριακή"] ET: type: string enum: ["esmaspäev", "teisipäev", "kolmapäev", "neljapäev", "reede", "laupäev", "pühapäev"] FI: type: string enum: ["maanantaina", "tiistaina", "keskiviikkona", "torstaina", "perjantaina", "lauantaina", "sunnuntai"] HR: type: string enum: ["ponedjeljak", "utorak", "srijeda", "četvrtak", "petak", "subota", "nedjelja"] HU: type: string enum: ["hétfő", "kedd", "szerda", "csütörtök", "péntek", "szombat", "vasárnap"] LT: type: string enum: ["pirmadienis", "antradienis", "trečiadienis", "ketvirtadienis", "penktadienis", "šeštadienis", "sekmadienis"] LV: type: string enum: ["pirmdiena", "otrdiena", "trešdiena", "ceturtdiena", "piektdiena", "sestdiena", "svētdiena"] NL: type: string enum: ["maandag", "dinsdag", "woensdag", "donderdag", "vrijdag", "zaterdag", "zondag"] "NO": type: string enum: ["mandag", "tirsdag", "onsdag", "torsdag", "fredag", "lørdag", "søndag"] PL: type: string enum: ["poniedziałek", "wtorek", "środa", "czwartek", "piątek", "sobota", "niedziela"] RO: type: string enum: ["luni", "marți", "miercuri", "joi", "vineri", "sâmbătă", "duminică"] RU: type: string enum: ["понедельник", "вторник", "среда", "четверг", "пятница", "суббота", "воскресенье"] SK: type: string enum: ["pondelok", "utorok", "streda", "štvrtok", "piatok", "sobota", "nedeľa"] SL: type: string enum: ["ponedeljek", "torek", "sreda", "četrtek", "petek", "sobota", "nedelja"] SV: type: string enum: ["måndag", "tisdag", "onsdag", "torsdag", "fredag", "lördag", "söndag"] TR: type: string enum: ["Pazartesi", "Salı", "Çarşamba", "Perşembe", "Cuma", "Cumartesi", "Pazar"] UK: type: string enum: ["понеділок", "вівторок", "середа", "четвер", "пʼятниця", "субота", "неділя"] tier: [VARCHAR, component] aliases: [] samples: - "Monday" - "Friday" - "Sunday" icon: calendar references: null notes: "v1 name: datetime.day_of_week. Enum shown is EN only — each locale has its own enum." datetime.component.month_name: title: Full Month Name description: > Full month name. Locale-specific (January vs janvier vs Januar). designation: locale_specific locales: - EN - FR - DE - ES - IT - PT - AR - BG - CS - DA - EL - ET - FI - HR - HU - LT - LV - NL - "NO" - PL - RO - RU - SK - SL - SV - TR - UK broad_type: VARCHAR frictionless: type: string format_string: null transform: "{col}" transform_ext: null decompose: null validation: type: string enum: - "January" - "February" - "March" - "April" - "May" - "June" - "July" - "August" - "September" - "October" - "November" - "December" validation_by_locale: # Source: Unicode CLDR (cldr-dates-full, wide month names) # License: Unicode License (permissive, attribution) # Each locale lists the 12 full month names in that language. EN: type: string enum: ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"] FR: type: string enum: ["janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "décembre"] DE: type: string enum: ["Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"] ES: type: string enum: ["enero", "febrero", "marzo", "abril", "mayo", "junio", "julio", "agosto", "septiembre", "octubre", "noviembre", "diciembre"] IT: type: string enum: ["gennaio", "febbraio", "marzo", "aprile", "maggio", "giugno", "luglio", "agosto", "settembre", "ottobre", "novembre", "dicembre"] PT: type: string enum: ["janeiro", "fevereiro", "março", "abril", "maio", "junho", "julho", "agosto", "setembro", "outubro", "novembro", "dezembro"] AR: type: string enum: ["يناير", "فبراير", "مارس", "أبريل", "مايو", "يونيو", "يوليو", "أغسطس", "سبتمبر", "أكتوبر", "نوفمبر", "ديسمبر"] BG: type: string enum: ["януари", "февруари", "март", "април", "май", "юни", "юли", "август", "септември", "октомври", "ноември", "декември"] CS: type: string enum: ["ledna", "února", "března", "dubna", "května", "června", "července", "srpna", "září", "října", "listopadu", "prosince"] DA: type: string enum: ["januar", "februar", "marts", "april", "maj", "juni", "juli", "august", "september", "oktober", "november", "december"] EL: type: string enum: ["Ιανουαρίου", "Φεβρουαρίου", "Μαρτίου", "Απριλίου", "Μαΐου", "Ιουνίου", "Ιουλίου", "Αυγούστου", "Σεπτεμβρίου", "Οκτωβρίου", "Νοεμβρίου", "Δεκεμβρίου"] ET: type: string enum: ["jaanuar", "veebruar", "märts", "aprill", "mai", "juuni", "juuli", "august", "september", "oktoober", "november", "detsember"] FI: type: string enum: ["tammikuuta", "helmikuuta", "maaliskuuta", "huhtikuuta", "toukokuuta", "kesäkuuta", "heinäkuuta", "elokuuta", "syyskuuta", "lokakuuta", "marraskuuta", "joulukuuta"] HR: type: string enum: ["siječnja", "veljače", "ožujka", "travnja", "svibnja", "lipnja", "srpnja", "kolovoza", "rujna", "listopada", "studenoga", "prosinca"] HU: type: string enum: ["január", "február", "március", "április", "május", "június", "július", "augusztus", "szeptember", "október", "november", "december"] LT: type: string enum: ["sausio", "vasario", "kovo", "balandžio", "gegužės", "birželio", "liepos", "rugpjūčio", "rugsėjo", "spalio", "lapkričio", "gruodžio"] LV: type: string enum: ["janvāris", "februāris", "marts", "aprīlis", "maijs", "jūnijs", "jūlijs", "augusts", "septembris", "oktobris", "novembris", "decembris"] NL: type: string enum: ["januari", "februari", "maart", "april", "mei", "juni", "juli", "augustus", "september", "oktober", "november", "december"] "NO": type: string enum: ["januar", "februar", "mars", "april", "mai", "juni", "juli", "august", "september", "oktober", "november", "desember"] PL: type: string enum: ["stycznia", "lutego", "marca", "kwietnia", "maja", "czerwca", "lipca", "sierpnia", "września", "października", "listopada", "grudnia"] RO: type: string enum: ["ianuarie", "februarie", "martie", "aprilie", "mai", "iunie", "iulie", "august", "septembrie", "octombrie", "noiembrie", "decembrie"] RU: type: string enum: ["января", "февраля", "марта", "апреля", "мая", "июня", "июля", "августа", "сентября", "октября", "ноября", "декабря"] SK: type: string enum: ["januára", "februára", "marca", "apríla", "mája", "júna", "júla", "augusta", "septembra", "októbra", "novembra", "decembra"] SL: type: string enum: ["januar", "februar", "marec", "april", "maj", "junij", "julij", "avgust", "september", "oktober", "november", "december"] SV: type: string enum: ["januari", "februari", "mars", "april", "maj", "juni", "juli", "augusti", "september", "oktober", "november", "december"] TR: type: string enum: ["Ocak", "Şubat", "Mart", "Nisan", "Mayıs", "Haziran", "Temmuz", "Ağustos", "Eylül", "Ekim", "Kasım", "Aralık"] UK: type: string enum: ["січня", "лютого", "березня", "квітня", "травня", "червня", "липня", "серпня", "вересня", "жовтня", "листопада", "грудня"] tier: [VARCHAR, component] aliases: [month] samples: - "January" - "June" - "December" icon: calendar references: null notes: "v1 name: date.month. Enum shown is EN only — each locale has its own enum." # datetime.component.century: REMOVED in v0.5.1 # Roman numerals only. No format_string, no DuckDB transformation contract. # Vanishingly rare as a standalone column. Better classified as categorical. datetime.component.periodicity: title: Periodicity description: > Frequency or recurrence label. Used in scheduling, reporting cadence, and time-series metadata. designation: broad_words locales: [UNIVERSAL] broad_type: VARCHAR frictionless: type: string format_string: null transform: "{col}" transform_ext: null decompose: null validation: type: string enum: - "Once" - "Daily" - "Weekly" - "Biweekly" - "Monthly" - "Quarterly" - "Yearly" - "Never" tier: [VARCHAR, component] aliases: [] samples: - "Daily" - "Monthly" - "Quarterly" icon: calendar references: null notes: null # ═════════════════════════════════════════════════════════════════════ # NEW TIMESTAMP FORMATS (Format Coverage Expansion) # # 16 additional timestamp formats covering: # - Fractional-second precision variants (milliseconds, microseconds) # - Timezone offset combinations # - Log formats (Apache CLF, BSD syslog, ctime) # - Regional separator variants (dot-DMY, slash-YMD) # ═════════════════════════════════════════════════════════════════════ datetime.timestamp.sql_microseconds: title: SQL Timestamp with Microseconds description: > Space-separated date and time with 6-digit microsecond precision. Default output of Python str(datetime.now()) and PostgreSQL TIMESTAMP. Distinct from iso_8601_microseconds by space (not T) separator. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%d %H:%M:%S.%f" format_string: "%Y-%m-%d %H:%M:%S.%f" transform: "strptime({col}, '%Y-%m-%d %H:%M:%S.%f')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\.\\d{6}$" minLength: 26 maxLength: 26 tier: [TIMESTAMP, timestamp] aliases: [sql_micro] samples: - "2024-01-15 14:30:00.123456" - "2019-12-31 23:59:59.000000" - "2023-06-01 08:15:30.789012" icon: clock canonical: "https://www.iso.org/standard/76583.html" references: null notes: > New in format coverage expansion. High prevalence — Python str(datetime.now()) default. datetime.timestamp.sql_milliseconds: title: SQL Timestamp with Milliseconds description: > Space-separated date and time with 3-digit millisecond precision. MySQL DATETIME(3), Java Timestamp.toString() output. DuckDB uses %g specifier for 3-digit milliseconds. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "any" format_string: "%Y-%m-%d %H:%M:%S.%g" transform: "strptime({col}, '%Y-%m-%d %H:%M:%S.%g')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\.\\d{3}$" minLength: 23 maxLength: 23 tier: [TIMESTAMP, timestamp] aliases: [sql_millis] samples: - "2024-01-15 14:30:00.123" - "2019-12-31 23:59:59.000" - "2023-06-01 08:15:30.789" icon: clock canonical: "https://www.iso.org/standard/76583.html" references: null notes: > New in format coverage expansion. MySQL DATETIME(3), Java Timestamp.toString(). datetime.timestamp.iso_8601_milliseconds: title: ISO 8601 with Milliseconds description: > ISO 8601 datetime with 3-digit millisecond precision and UTC indicator. The exact output of JavaScript Date.toISOString() — arguably the most common timestamp format in JSON REST APIs worldwide. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "any" format_string: "%Y-%m-%dT%H:%M:%S.%gZ" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S.%gZ')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{3}Z$" minLength: 24 maxLength: 24 tier: [TIMESTAMP, timestamp] aliases: [js_iso, iso_millis] samples: - "2024-01-15T14:30:00.123Z" - "2019-12-31T23:59:59.999Z" - "2023-06-01T00:00:00.000Z" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: - title: "MDN Date.toISOString()" link: "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toISOString" notes: > New in format coverage expansion. Highest-impact single addition — covers the entire JavaScript ecosystem. Distinct from iso_8601 by mandatory 3-digit fractional seconds. datetime.timestamp.iso_8601_millis_offset: title: ISO 8601 with Milliseconds and Offset description: > ISO 8601 datetime with 3-digit milliseconds and numeric timezone offset. Java OffsetDateTime.toString() with ms precision. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "any" format_string: "%Y-%m-%dT%H:%M:%S.%g%z" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S.%g%z')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{3}[+-]\\d{2}:\\d{2}$" minLength: 29 maxLength: 29 tier: [TIMESTAMPTZ, timestamp] aliases: [] samples: - "2024-01-15T14:30:00.123+05:30" - "2023-12-31T23:59:59.999-05:00" - "2024-06-01T12:00:00.000+00:00" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: > New in format coverage expansion. Java OffsetDateTime with ms precision. datetime.timestamp.iso_8601_micros_offset: title: ISO 8601 with Microseconds and Offset description: > ISO 8601 datetime with 6-digit microsecond precision and numeric timezone offset. Python datetime.now(tz).isoformat() output, RFC 5424 syslog. Neither iso_8601_microseconds nor iso_8601_offset covers this combination. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%Y-%m-%dT%H:%M:%S.%f%z" format_string: "%Y-%m-%dT%H:%M:%S.%f%z" transform: "strptime({col}, '%Y-%m-%dT%H:%M:%S.%f%z')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{6}[+-]\\d{2}:\\d{2}$" minLength: 32 maxLength: 32 tier: [TIMESTAMPTZ, timestamp] aliases: [rfc_5424_micro] samples: - "2024-01-15T14:30:00.123456+00:00" - "2023-12-31T23:59:59.000000-05:00" - "2024-06-01T08:15:30.789012+05:30" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: - title: "RFC 5424 — The Syslog Protocol" link: "https://datatracker.ietf.org/doc/html/rfc5424" notes: > New in format coverage expansion. High prevalence — Python isoformat() with tz, RFC 5424 syslog. Fills the gap between iso_8601_microseconds and iso_8601_offset. datetime.timestamp.clf: title: Common Log Format (Apache/Nginx) description: > Apache and Nginx Common Log Format timestamp. Note the distinctive colon between date and time with no space, and abbreviated month. Billions of log lines generated daily in this format worldwide. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%d/%b/%Y:%H:%M:%S %z" format_string: "%d/%b/%Y:%H:%M:%S %z" format_string_alt: "[%d/%b/%Y:%H:%M:%S %z]" transform: "strptime({col}, '%d/%b/%Y:%H:%M:%S %z')" transform_ext: null decompose: null validation: type: string pattern: "^\\[?\\d{2}/[A-Z][a-z]{2}/\\d{4}:\\d{2}:\\d{2}:\\d{2} [+-]\\d{2}(?::?\\d{2})?\\]?$" minLength: 24 maxLength: 29 tier: [TIMESTAMPTZ, timestamp] aliases: [apache, nginx_log] samples: - "15/Jan/2024:14:30:00 +0000" - "31/Dec/2023:23:59:59 -0500" - "01/Jun/2024:08:15:30 +0530" icon: clock references: - title: "Apache Log Files" link: "https://httpd.apache.org/docs/2.4/logs.html" notes: > New in format coverage expansion. Critical for web log analysis. Highly distinctive format — colon between date:time is unique. The offset takes every form DuckDB's `%z` parses — `+00`, `+0000`, `+00:00` — because `+00` is what DuckDB's own `strftime` writes for this format string, and the transform reads all three. datetime.timestamp.syslog_bsd: title: BSD Syslog (RFC 3164) description: > Legacy syslog format from RFC 3164. No year component — year must be inferred from context. Day is space-padded. Extremely widespread in /var/log/syslog on Linux/BSD systems. designation: universal locales: [UNIVERSAL] broad_type: VARCHAR frictionless: type: datetime format: "any" format_string: "%b %-d %H:%M:%S" transform: "strptime(CAST(YEAR(CURRENT_DATE) AS VARCHAR) || ' ' || {col}, '%Y %b %-d %H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]{2} [\\s\\d]\\d \\d{2}:\\d{2}:\\d{2}$" minLength: 15 maxLength: 15 tier: [VARCHAR, timestamp] aliases: [rfc_3164] samples: - "Jan 15 14:30:00" - "Dec 31 23:59:59" - "Jun 1 08:15:30" icon: clock canonical: "https://datatracker.ietf.org/doc/html/rfc3164" references: - title: "RFC 3164 — The BSD syslog Protocol" link: "https://datatracker.ietf.org/doc/html/rfc3164" notes: > New in format coverage expansion. No year in format — transform prepends current year. Day may be space-padded (e.g., " 1" for first of month). broad_type is VARCHAR because year inference makes DATE unreliable. datetime.timestamp.sql_microseconds_offset: title: SQL Timestamp with Microseconds and Offset description: > PostgreSQL TIMESTAMPTZ output format. Space-separated date and time with 6-digit microseconds and full timezone offset. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%Y-%m-%d %H:%M:%S.%f%z" format_string: "%Y-%m-%d %H:%M:%S.%f%z" transform: "strptime({col}, '%Y-%m-%d %H:%M:%S.%f%z')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\.\\d{6}[+-]\\d{2}:\\d{2}$" minLength: 32 maxLength: 32 tier: [TIMESTAMPTZ, timestamp] aliases: [pg_timestamptz] samples: - "2024-01-15 14:30:00.123456+00:00" - "2023-12-31 23:59:59.000000-05:00" - "2024-06-01 08:15:30.789012+05:30" icon: clock canonical: "https://www.iso.org/standard/76583.html" references: null notes: > New in format coverage expansion. PostgreSQL TIMESTAMPTZ default output. Distinct from iso_8601_micros_offset by space (not T) separator. datetime.timestamp.pg_short_offset: title: PostgreSQL Short Offset description: > PostgreSQL-specific format with 2-digit timezone offset (e.g., -05 instead of -05:00). Microsecond precision with abbreviated offset. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMPTZ frictionless: type: datetime format: "%Y-%m-%d %H:%M:%S.%f%z" format_string: "%Y-%m-%d %H:%M:%S.%f%z" transform: "strptime({col} || ':00', '%Y-%m-%d %H:%M:%S.%f%z')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}\\.\\d{6}[+-]\\d{2}$" minLength: 28 maxLength: 29 tier: [TIMESTAMPTZ, timestamp] aliases: [] samples: - "2024-01-15 14:30:00.123456-05" - "2023-12-31 23:59:59.000000+00" - "2024-06-01 08:15:30.789012+10" icon: clock references: null notes: > New in format coverage expansion. PostgreSQL sometimes emits short offsets (-05 instead of -05:00). Transform appends :00 for strptime compatibility. datetime.timestamp.dot_dmy_24h: title: European Dot Timestamp (DMY 24h) description: > Dot-separated Day.Month.Year with space and 24-hour time. Standard in German, Central European, and Russian systems. SAP and German banking exports frequently use this format. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%d.%m.%Y %H:%M:%S" format_string: "%d.%m.%Y %H:%M:%S" transform: "strptime({col}, '%d.%m.%Y %H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}\\.\\d{2}\\.\\d{4} \\d{2}:\\d{2}:\\d{2}$" minLength: 19 maxLength: 19 tier: [TIMESTAMP, timestamp] aliases: [eu_dot_timestamp] samples: - "15.01.2024 14:30:00" - "31.12.2023 23:59:59" - "01.06.2024 08:15:30" icon: clock references: null notes: > New in format coverage expansion. Common in DACH region (Germany, Austria, Switzerland) and Eastern European ERP exports. datetime.timestamp.slash_ymd_24h: title: Slash YMD Timestamp (24h) description: > Slash-separated Year/Month/Day with space and 24-hour time. Standard in Japanese system logs and .NET ja-JP culture default. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y/%m/%d %H:%M:%S" format_string: "%Y/%m/%d %H:%M:%S" transform: "strptime({col}, '%Y/%m/%d %H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}/\\d{2}/\\d{2} \\d{2}:\\d{2}:\\d{2}$" minLength: 19 maxLength: 19 tier: [TIMESTAMP, timestamp] aliases: [jp_timestamp] samples: - "2024/01/15 14:30:00" - "2023/12/31 23:59:59" - "2024/06/01 08:15:30" icon: clock references: null notes: > New in format coverage expansion. Japanese system logs, .NET ja-JP culture. datetime.timestamp.ctime: title: C ctime / asctime Format description: > Output of C ctime() and asctime() functions, Python datetime.ctime(), Ruby Time#to_s. Day-of-week abbreviation, month abbreviation, space-padded day, 24-hour time, then 4-digit year. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "any" format_string: "%a %b %-d %H:%M:%S %Y" transform: "strptime({col}, '%a %b %-d %H:%M:%S %Y')" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]{2} [A-Z][a-z]{2} [\\s\\d]\\d \\d{2}:\\d{2}:\\d{2} \\d{4}$" minLength: 24 maxLength: 24 tier: [TIMESTAMP, timestamp] aliases: [asctime] samples: - "Mon Jan 15 14:30:00 2024" - "Tue Dec 31 23:59:59 2019" - "Sat Jun 1 08:15:30 2024" icon: clock references: null notes: > New in format coverage expansion. Unix tradition format. Day may be space-padded. Python datetime.ctime(), C ctime()/asctime(). datetime.timestamp.epoch_nanoseconds: title: Unix Epoch Nanoseconds description: > Nanoseconds since Unix epoch (1970-01-01T00:00:00Z). 19-digit integer. OpenTelemetry time_unix_nano, Go time.UnixNano(). Distinct from other epoch formats by digit count (19 vs 10/13/16). designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: integer format_string: null transform: "make_timestamp_ns({col}::BIGINT)" transform_ext: null decompose: null validation: type: string pattern: "^\\d{19}$" minLength: 19 maxLength: 19 tier: [BIGINT, epoch] aliases: [unix_nano] samples: - "1705325400000000000" - "1704067199000000000" - "1717228530000000000" icon: clock references: - title: "OpenTelemetry time_unix_nano" link: "https://opentelemetry.io/docs/specs/otel/metrics/data-model/" notes: > New in format coverage expansion. OpenTelemetry, Go time.UnixNano(). Transform casts the 19-digit text to BIGINT then converts via DuckDB make_timestamp_ns() — input is VARCHAR storage, so epoch_ns() (which expects a TIMESTAMP/TIME) has no binder overload and aborts validate. datetime.timestamp.iso_space_zulu: title: ISO with Space Separator and Z description: > RFC 3339 variant using space instead of T separator with UTC Z indicator. SQLite datetime('now') output and some cloud service log formats. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y-%m-%d %H:%M:%SZ" format_string: "%Y-%m-%d %H:%M:%SZ" transform: "strptime({col}, '%Y-%m-%d %H:%M:%SZ')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}Z$" minLength: 20 maxLength: 20 tier: [TIMESTAMP, timestamp] aliases: [sqlite_utc] samples: - "2024-01-15 14:30:00Z" - "2023-12-31 23:59:59Z" - "2024-06-01 00:00:00Z" icon: clock canonical: "https://www.iso.org/standard/70907.html" references: null notes: > New in format coverage expansion. RFC 3339 permits space instead of T. SQLite datetime('now'), AWS CloudWatch, GCP logs. datetime.timestamp.dot_ymd_24h: title: Dot YMD Timestamp (24h) description: > Dot-separated Year.Month.Day with space and 24-hour time. Niche format found in some East Asian and Baltic systems. designation: universal locales: [UNIVERSAL] broad_type: TIMESTAMP frictionless: type: datetime format: "%Y.%m.%d %H:%M:%S" format_string: "%Y.%m.%d %H:%M:%S" transform: "strptime({col}, '%Y.%m.%d %H:%M:%S')" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}\\.\\d{2}\\.\\d{2} \\d{2}:\\d{2}:\\d{2}$" minLength: 19 maxLength: 19 tier: [TIMESTAMP, timestamp] aliases: [] samples: - "2024.01.15 14:30:00" - "2023.12.31 23:59:59" - "2024.06.01 08:15:30" icon: clock references: null notes: > New in format coverage expansion. Low prevalence. East Asian/Baltic niche. # ═════════════════════════════════════════════════════════════════════ # NEW DATE FORMATS (Format Coverage Expansion) # # 23 additional date formats covering: # - Separator variants (slash, dash, dot, space) # - Field-order variants (DMY, YMD, MDY) # - Named-month formats (abbreviated, full, with/without comma) # - Partial dates (year-month, month-year) # - CJK formats (Chinese, Korean, Japanese era) # ═════════════════════════════════════════════════════════════════════ datetime.date.ymd_slash: title: YMD Date (slash-separated) description: > Year/Month/Day with slash separators. Unambiguous due to year-first ordering. CLDR ja-JP short format; standard in Japanese and Taiwanese CSV exports. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%Y/%m/%d" format_string: "%Y/%m/%d" transform: "strptime({col}, '%Y/%m/%d')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}/\\d{2}/\\d{2}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "2024/01/15" - "2023/12/31" - "2000/06/01" icon: calendar references: null notes: > New in format coverage expansion. High prevalence in East Asian data. datetime.date.ymd_dot: title: YMD Date (dot-separated) description: > Year.Month.Day with dot separators. Unambiguous due to year-first ordering. Hungarian, Norwegian, and some East Asian administrative data. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%Y.%m.%d" format_string: "%Y.%m.%d" transform: "strptime({col}, '%Y.%m.%d')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}\\.\\d{2}\\.\\d{2}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "2024.01.15" - "2023.12.31" - "2000.06.01" icon: calendar references: null notes: > New in format coverage expansion. Hungarian, Norwegian, East Asian systems. datetime.date.dmy_dash: title: DMY Date (dash-separated) description: > Day-Month-Year with dash separators and 4-digit year. Extremely common in EU informal data and Pandas examples. Ambiguous with mdy_dash when day <= 12. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d-%m-%Y" format_string: "%d-%m-%Y" transform: "strptime({col}, '%d-%m-%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}-\\d{2}-\\d{4}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "15-01-2024" - "31-12-2023" - "01-06-2000" icon: calendar references: null notes: > New in format coverage expansion. Ambiguous with mdy_dash when day <= 12. Column-mode inference resolves via value distribution (if any day > 12, classify DMY). datetime.date.mdy_dash: title: MDY Date (dash-separated) description: > Month-Day-Year with dash separators and 4-digit year. US database exports. Ambiguous with dmy_dash when day <= 12. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%m-%d-%Y" format_string: "%m-%d-%Y" transform: "strptime({col}, '%m-%d-%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}-\\d{2}-\\d{4}$" minLength: 10 maxLength: 10 tier: [DATE, date] aliases: [] samples: - "01-15-2024" - "12-31-2023" - "06-01-2000" icon: calendar references: null notes: > New in format coverage expansion. US database exports. Ambiguous with dmy_dash — resolved by column-mode inference checking if any value has first component > 12. datetime.date.dmy_space_abbrev: title: DMY Date with Abbreviated Month (space-separated) description: > Day Month-Abbr Year with space separators. Unambiguous due to named month. RFC 2822 date portion, military/NATO records, PostgreSQL to_char. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d %b %Y" format_string: "%d %b %Y" transform: "strptime({col}, '%d %b %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[0-3]?\\d [A-Z][a-z]{2} \\d{4}$" minLength: 10 maxLength: 11 tier: [DATE, date] aliases: [] samples: - "15 Jan 2024" - "31 Dec 2023" - "1 Jun 2000" icon: calendar references: null notes: > New in format coverage expansion. RFC 2822 date portion, military/NATO. datetime.date.dmy_space_full: title: DMY Date with Full Month (space-separated) description: > Day FullMonth Year with space separators. Unambiguous due to named month. CLDR en-GB long format, European formal documents. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d %B %Y" format_string: "%d %B %Y" transform: "strptime({col}, '%d %B %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[0-3]?\\d [A-Z][a-z]+ \\d{4}$" minLength: 12 maxLength: 20 tier: [DATE, date] aliases: [] samples: - "15 January 2024" - "31 December 2023" - "1 June 2000" icon: calendar references: null notes: > New in format coverage expansion. CLDR en-GB long format. datetime.date.abbrev_month_no_comma: title: Abbreviated Month Day Year (no comma) description: > Month-Abbr Day Year without comma separator. Unambiguous due to named month. SQL Server BCP output, syslog date headers. Distinct from abbreviated_month which requires a comma (Jan 15, 2024). designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%b %d %Y" format_string: "%b %d %Y" transform: "strptime({col}, '%b %d %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]{2} [0-3]?\\d \\d{4}$" minLength: 10 maxLength: 11 tier: [DATE, date] aliases: [] samples: - "Jan 15 2024" - "Dec 31 2023" - "Jun 1 2000" icon: calendar references: null notes: > New in format coverage expansion. SQL Server BCP output. Distinct from abbreviated_month (which has comma: "Jan 15, 2024"). datetime.date.full_month_no_comma: title: Full Month Day Year (no comma) description: > FullMonth Day Year without comma separator. Variant of long_full_month without the comma. Unambiguous due to named month. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%B %d %Y" format_string: "%B %d %Y" transform: "strptime({col}, '%B %d %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]+ [0-3]?\\d \\d{4}$" minLength: 12 maxLength: 20 tier: [DATE, date] aliases: [] samples: - "January 15 2024" - "December 31 2023" - "June 1 2000" icon: calendar references: null notes: > New in format coverage expansion. Variant of long_full_month without comma. datetime.date.dmy_dash_abbrev: title: DMY Date with Abbreviated Month (dash-separated) description: > Day-MonthAbbr-Year with dash separators. The Oracle NLS_DATE_FORMAT default. Extremely common in banking and financial data exports. Unambiguous due to named month. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d-%b-%Y" format_string: "%d-%b-%Y" transform: "strptime({col}, '%d-%b-%Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[0-3]?\\d-[A-Z][a-z]{2}-\\d{4}$" minLength: 10 maxLength: 11 tier: [DATE, date] aliases: [oracle_date] samples: - "15-Jan-2024" - "31-Dec-2023" - "01-Jun-2000" icon: calendar references: null notes: > New in format coverage expansion. Oracle NLS_DATE_FORMAT default (DD-MON-YYYY). High prevalence in enterprise database exports. datetime.date.dmy_dash_abbrev_short: title: DMY Date with Abbreviated Month (dash, 2-digit year) description: > Day-MonthAbbr-ShortYear with dash separators. Oracle classic DD-MON-RR format. Named month resolves field order; 2-digit year has century ambiguity. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d-%b-%y" format_string: "%d-%b-%y" transform: "strptime({col}, '%d-%b-%y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[0-3]?\\d-[A-Z][a-z]{2}-\\d{2}$" minLength: 8 maxLength: 9 tier: [DATE, date] aliases: [oracle_date_short] samples: - "15-Jan-24" - "31-Dec-99" - "01-Jun-00" icon: calendar references: null notes: > New in format coverage expansion. Oracle classic DD-MON-RR format. datetime.date.mdy_short_slash: title: MDY Short Date (slash, 2-digit year) description: > Month/Day/ShortYear with slash separators. Excel US short date format. Ambiguous with dmy_short_slash when day <= 12. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%m/%d/%y" format_string: "%m/%d/%y" transform: "strptime({col}, '%m/%d/%y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{2}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "01/15/24" - "12/31/23" - "06/01/00" icon: calendar references: null notes: > New in format coverage expansion. Excel US short date. Ambiguous with dmy_short_slash — resolved by column-mode inference. datetime.date.dmy_short_slash: title: DMY Short Date (slash, 2-digit year) description: > Day/Month/ShortYear with slash separators. Excel EU short date format. Ambiguous with mdy_short_slash when day <= 12. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d/%m/%y" format_string: "%d/%m/%y" transform: "strptime({col}, '%d/%m/%y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}/\\d{2}/\\d{2}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "15/01/24" - "31/12/23" - "01/06/00" icon: calendar references: null notes: > New in format coverage expansion. Excel EU short date. Ambiguous with mdy_short_slash — resolved by column-mode inference. datetime.date.dmy_short_dot: title: DMY Short Date (dot, 2-digit year) description: > Day.Month.ShortYear with dot separators and 2-digit year. CLDR de-DE short format (d.M.yy). Dot convention is nearly always DMY. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%d.%m.%y" format_string: "%d.%m.%y" transform: "strptime({col}, '%d.%m.%y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{2}\\.\\d{2}\\.\\d{2}$" minLength: 8 maxLength: 8 tier: [DATE, date] aliases: [] samples: - "15.01.24" - "31.12.23" - "01.06.00" icon: calendar references: null notes: > New in format coverage expansion. CLDR de-DE short format. datetime.date.year_month: title: Year-Month (ISO truncated) description: > ISO 8601 truncated form with no day component. Pervasive in monthly time-series data, financial reporting, and API responses. Transforms to first day of month. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: yearmonth format_string: "%Y-%m" transform: "strptime({col} || '-01', '%Y-%m-%d')::DATE" transform_ext: null decompose: year: "CAST(SUBSTRING({col}, 1, 4) AS INT)" month: "CAST(SUBSTRING({col}, 6, 2) AS INT)" validation: type: string pattern: "^\\d{4}-\\d{2}$" minLength: 7 maxLength: 7 tier: [DATE, date] aliases: [yearmonth] samples: - "2024-01" - "2023-12" - "2000-06" icon: calendar references: null notes: > New in format coverage expansion. ISO 8601 truncated date. Transform appends -01 to create full date for strptime. datetime.date.compact_ym: title: Compact Year-Month (YYYYMM) description: > 6-digit compact year-month with no separators. Data warehouse period keys, financial reporting codes. Could collide with 6-digit integers — requires header/context hints. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%Y%m" format_string: "%Y%m" transform: "strptime({col} || '01', '%Y%m%d')::DATE" transform_ext: null decompose: year: "CAST(SUBSTRING({col}, 1, 4) AS INT)" month: "CAST(SUBSTRING({col}, 5, 2) AS INT)" validation: type: string pattern: "^\\d{4}(0[1-9]|1[0-2])$" minLength: 6 maxLength: 6 tier: [DATE, date] aliases: [] samples: - "202401" - "202312" - "200006" icon: calendar references: null notes: > New in format coverage expansion. Data warehouse period keys. Collision risk with 6-digit integers mitigated by month validation (01-12). datetime.date.month_year_full: title: Full Month Year description: > Full month name followed by 4-digit year. No day component. Financial reports, government statistical publications. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%B %Y" format_string: "%B %Y" transform: "strptime('01 ' || {col}, '%d %B %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]+ \\d{4}$" minLength: 8 maxLength: 14 tier: [VARCHAR, date] aliases: [] samples: - "January 2024" - "December 2023" - "June 2000" icon: calendar references: null notes: > New in format coverage expansion. Financial reports, gov statistics. datetime.date.month_year_abbrev: title: Abbreviated Month Year description: > Abbreviated month name followed by 4-digit year. No day component. Financial dashboards, CLDR yMMM skeleton. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%b %Y" format_string: "%b %Y" transform: "strptime('01 ' || {col}, '%d %b %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]{2} \\d{4}$" minLength: 8 maxLength: 8 tier: [VARCHAR, date] aliases: [] samples: - "Jan 2024" - "Dec 2023" - "Jun 2000" icon: calendar references: null notes: > New in format coverage expansion. Financial dashboards, CLDR yMMM skeleton. datetime.date.month_year_slash: title: Month/Year (slash-separated) description: > Numeric Month/Year with slash separator. No day component. Accepts both MM/YYYY and MM/YY formats. Common in billing data, credit card expiration dates, and financial periodization. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%m/%Y" format_string: "%m/%Y" transform: "CASE WHEN LENGTH({col}) = 5 THEN strptime('01/' || SUBSTRING({col}, 1, 2) || '/20' || SUBSTRING({col}, 4, 2), '%d/%m/%Y')::DATE ELSE strptime('01/' || {col}, '%d/%m/%Y')::DATE END" transform_ext: null decompose: month: "CAST(SUBSTRING({col}, 1, 2) AS INT)" year: "CASE WHEN LENGTH({col}) = 5 THEN 2000 + CAST(SUBSTRING({col}, 4, 2) AS INT) ELSE CAST(SUBSTRING({col}, 4, 4) AS INT) END" validation: type: string pattern: "^(0[1-9]|1[0-2])/(\\d{2}|\\d{4})$" minLength: 5 maxLength: 7 tier: [VARCHAR, date] aliases: [card_expiry] samples: - "01/2024" - "12/2023" - "06/2000" - "12/25" - "03/26" icon: calendar references: null notes: > New in format coverage expansion. Billing data, financial periodization. Widened to accept MM/YY format (credit card expiration dates) after collapsing finance.payment.credit_card_expiration_date into this type. datetime.date.weekday_dmy_full: title: Weekday DMY Full (formal) description: > Full weekday name, Day FullMonth Year. Unambiguous due to named components. CLDR en-GB/fr-FR full format, formal correspondence. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: date format: "%A, %d %B %Y" format_string: "%A, %d %B %Y" transform: "strptime({col}, '%A, %d %B %Y')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^[A-Z][a-z]+, [0-3]?\\d [A-Z][a-z]+ \\d{4}$" minLength: 22 maxLength: 32 tier: [DATE, date] aliases: [] samples: - "Monday, 15 January 2024" - "Sunday, 31 December 2023" - "Saturday, 1 June 2024" icon: calendar references: null notes: > New in format coverage expansion. CLDR en-GB full format. datetime.date.chinese_ymd: title: Chinese Standard Date description: > Year-Month-Day with Chinese character delimiters. Standard in Chinese government, institutional data, and formal documents. Uses Unicode characters for year, month, day markers. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: string format_string: null transform: "strptime(regexp_replace(regexp_replace(regexp_replace({col}, '\u5E74', '-'), '\u6708', '-'), '\u65E5', ''), '%Y-%-m-%-d')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}\u5E74[1-9]\\d?\u6708[1-3]?\\d\u65E5$" tier: [VARCHAR, date] aliases: [cn_standard] samples: - "2024\u5E741\u670815\u65E5" - "2023\u5E7412\u670825\u65E5" - "2022\u5E746\u67081\u65E5" icon: calendar references: null notes: > New in format coverage expansion. Chinese standard date with Unicode delimiters. Transform strips Chinese characters and parses as ISO-like. datetime.date.korean_ymd: title: Korean Standard Date description: > Year-Month-Day with Korean character delimiters and spaces. Standard in Korean government and institutional data. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: string format_string: null transform: "strptime(regexp_replace(regexp_replace(regexp_replace({col}, '\uB144 ', '-'), '\uC6D4 ', '-'), '\uC77C', ''), '%Y-%-m-%-d')::DATE" transform_ext: null decompose: null validation: type: string pattern: "^\\d{4}\uB144 [1-9]\\d?\uC6D4 [1-3]?\\d\uC77C$" tier: [VARCHAR, date] aliases: [] samples: - "2024\uB144 1\uC6D4 15\uC77C" - "2023\uB144 12\uC6D4 25\uC77C" - "2022\uB144 6\uC6D4 1\uC77C" icon: calendar references: null notes: > New in format coverage expansion. Korean standard date with Unicode delimiters. Transform strips Korean characters and parses as ISO-like. datetime.date.jp_era_short: title: Japanese Imperial Year (Short) description: > Era abbreviation + year/month/day. Uses single-letter era abbreviation: R (Reiwa/令和, 2019+), H (Heisei/平成, 1989-2019), S (Showa/昭和, 1926-1989), T (Taisho/大正, 1912-1926), M (Meiji/明治, 1868-1912). Common in Japanese government documents and banking records. Requires era-to-year offset calculation. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: string format_string: null transform: > CASE WHEN {col}[1] = 'R' THEN make_date(2018 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'H' THEN make_date(1988 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'S' THEN make_date(1925 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'T' THEN make_date(1911 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) WHEN {col}[1] = 'M' THEN make_date(1867 + CAST(split_part(substring({col}, 2), '/', 1) AS INT), CAST(split_part(substring({col}, 2), '/', 2) AS INT), CAST(split_part(substring({col}, 2), '/', 3) AS INT)) END transform_ext: null decompose: era: "substring({col}, 1, 1)" era_year: "CAST(split_part(substring({col}, 2), '/', 1) AS INT)" validation: type: string pattern: "^[RHSTM]\\d{1,2}/\\d{1,2}/\\d{1,2}$" tier: [VARCHAR, date] aliases: [wareki_short] samples: - "R6/01/15" - "R1/05/01" - "H31/04/30" - "S64/01/07" icon: calendar references: - title: "Date and time notation in Japan" link: "https://en.wikipedia.org/wiki/Date_and_time_notation_in_Japan" notes: > New in format coverage expansion. Japanese imperial year (Wareki). Era offset table: R+2018, H+1988, S+1925, T+1911, M+1867. R1 = 2019 (Reiwa started May 1, 2019), H31 = 2019 (Heisei ended April 30). datetime.date.jp_era_long: title: Japanese Imperial Year (Long/Kanji) description: > Full Japanese era name in kanji followed by year, month, day with Chinese-style character delimiters. Used in formal Japanese government and legal documents. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: string format_string: null transform: > CASE WHEN {col} LIKE '令和%' THEN make_date(2018 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '平成%' THEN make_date(1988 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '昭和%' THEN make_date(1925 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '大正%' THEN make_date(1911 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) WHEN {col} LIKE '明治%' THEN make_date(1867 + CAST(regexp_extract({col}, '(\d+)年', 1) AS INT), CAST(regexp_extract({col}, '(\d+)月', 1) AS INT), CAST(regexp_extract({col}, '(\d+)日', 1) AS INT)) END transform_ext: null decompose: era_name: "regexp_extract({col}, '^([^\u0030-\u0039]+)', 1)" era_year: "CAST(regexp_extract({col}, '(\u005C\u0064+)\u5E74', 1) AS INT)" validation: type: string pattern: "^(\u4EE4\u548C|\u5E73\u6210|\u662D\u548C|\u5927\u6B63|\u660E\u6CBB)\\d{1,2}\u5E74\\d{1,2}\u6708\\d{1,2}\u65E5$" tier: [VARCHAR, date] aliases: [wareki_long] samples: - "\u4EE4\u548C6\u5E741\u670815\u65E5" - "\u5E73\u621031\u5E744\u670830\u65E5" - "\u662D\u548C64\u5E741\u67087\u65E5" icon: calendar references: - title: "Date and time notation in Japan" link: "https://en.wikipedia.org/wiki/Date_and_time_notation_in_Japan" notes: > New in format coverage expansion. Formal Japanese imperial year with kanji era names. Transform maps era names to gregorian year offsets. # ═════════════════════════════════════════════════════════════════════ # CATEGORY: period # # Date ranges and fiscal/calendar periods. These represent spans of # time rather than specific points. Transforms default to period start. # ═════════════════════════════════════════════════════════════════════ datetime.period.quarter: title: Calendar Quarter description: > Calendar quarter notation (Q1-Q4 with year). Ubiquitous in SEC filings, earnings data, and business intelligence dashboards. Represents a 3-month range. Transforms to first day of the quarter. designation: universal locales: [UNIVERSAL] broad_type: DATE frictionless: type: string format_string: null transform: "make_date(CAST(regexp_extract({col}, '(\\d{4})', 1) AS INT), (CAST(regexp_extract({col}, 'Q([1-4])', 1) AS INT) - 1) * 3 + 1, 1)" transform_ext: null decompose: year: "CAST(regexp_extract({col}, '(\\d{4})', 1) AS INT)" quarter: "CAST(regexp_extract({col}, 'Q([1-4])', 1) AS INT)" validation: type: string pattern: "^(Q[1-4]\\s?\\d{4}|\\d{4}[\\s-]?Q[1-4])$" tier: [VARCHAR, component] aliases: [quarter_iso] samples: - "Q1 2024" - "Q4 2023" - "2024-Q2" - "2023 Q3" icon: calendar-event references: null notes: > New in format coverage expansion. Accepts both "Q1 2024" and "2024-Q1" variants. Transform extracts year and quarter, constructs first day of quarter. datetime.period.fiscal_year: title: Fiscal Year description: > Fiscal year notation (FY prefix with 2 or 4-digit year). Common in corporate finance CSVs, government budget documents, and annual reports. The fiscal year ending period varies by organization — FY2024 may refer to a period ending in calendar 2024 or starting in 2024. designation: universal locales: [UNIVERSAL] broad_type: VARCHAR frictionless: type: string format_string: null transform: "CAST(regexp_extract({col}, 'FY(\\d{2,4})', 1) AS VARCHAR)" transform_ext: null decompose: year: "CAST(regexp_extract({col}, 'FY(\\d{2,4})', 1) AS INT)" validation: type: string pattern: "^FY\\d{2,4}$" minLength: 4 maxLength: 6 tier: [VARCHAR, component] aliases: [] samples: - "FY2024" - "FY24" - "FY2023" - "FY25" icon: calendar-event references: null notes: > New in format coverage expansion. Fiscal year offset context varies by organization. broad_type is VARCHAR because calendar date mapping requires organization-specific fiscal calendar knowledge.