Overview
ISO/IEC 19075-2:2021 - "Information technology - Guidance for the use of database language SQL - Part 2: Time-related information" specifies how SQL supports time-related data and temporal features. The standard documents the SQL datatypes, literals, operators, predicates, and table constructs used to represent and manage dates, times, intervals and temporal history (application-time, system-versioned and bi-temporal tables). It is intended to be used alongside the ISO/IEC 9075 SQL family.
Key topics and technical requirements
- Time-related datatypes: Defines DATE, TIME and TIMESTAMP (with optional fractional seconds precision and optional WITH TIME ZONE). Explains field components (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND) and valid ranges.
- Datetime literals: Describes the fixed literal format (yyyy‑mm‑dd hh:mi:ss.sss) and automatic datatype assignment based on literal content.
- Interval types: Distinguishes year–month and day–time INTERVAL classes and their allowed fields and precisions.
- Operations and operators: Lists valid arithmetic and relational operations between datetimes and intervals (addition, subtraction, comparisons). Notes implementation-defined behavior for leap seconds and calendar discontinuities.
- Time-related predicates: Covers predicates for temporal relationships, including OVERLAPS and other period predicates to test period relations.
- Time-related tables: Extensive guidance on:
- Application-time period tables (business-validity periods),
- System-versioned tables (system-maintained history for row versions),
- Bi-temporal tables (combining application and system time).
Clause-level guidance includes primary/foreign key and constraint extensions, insertion, update and delete semantics, and querying patterns for temporal tables.
- Time zone handling: Describes time zone displacement semantics and rules for implicit conversions between timezone-aware and timezone-naïve datetimes.
Applications and users
ISO/IEC 19075-2:2021 is essential for:
- DBMS implementers and SQL engine vendors implementing temporal features and time-related datatypes.
- Database architects and data modelers designing temporal schemas, application-time period tables, system-versioned and bi-temporal systems.
- Application developers building auditing, historical reporting, financial, healthcare or legal systems that require precise temporal semantics.
- Data governance and compliance teams that rely on standardized temporal behavior for retention, provenance and audit trails.
Practical benefits include consistent handling of timezones, intervals, temporal queries, and standardized behavior for temporal constraints and history management.
Related standards
This part is intended to be used in conjunction with the ISO/IEC 9075 series (SQL), including parts 1 and 2 (Framework and Foundation) and other relevant ISO/IEC 9075 parts cited within the standard. Keywords: ISO/IEC 19075-2:2021, SQL time-related, temporal SQL, datetime types, system-versioned tables, bi-temporal tables, application-time period, timezones, SQL intervals.