ISO/IEC 19075-2:2021

Information technology - Guidance for the use of database language SQL - Part 2: Time-related information

This document describes the support in SQL for time-related information. This document discusses the following features of the SQL language: - Time-related data types - Operations on time-related data - Time-related Predicates - Application-time period tables - System-versioned tables - Bi-temporal tables

Technologies de l'information — Recommandations pour l'utilisation du langage de base de données SQL — Partie 2: Informations d'horodatage

General Information

• Status: Published
• Publication Date: 31-Aug-2021
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 6060 - International Standard published
• Start Date: 01-Sep-2021
• Due Date: 11-Feb-2024
• Completion Date: 31-Aug-2021

Relations

• Split Into: ISO/IEC 19075-10:2024 - Information technology - Guidance for the use of database language SQL - Part 10: SQL model (Guide/Model)
• Effective Date: 13-Aug-2022
• Revises: ISO/IEC TR 19075-2:2015 - Information technology - Database languages - SQL Technical Reports - Part 2: SQL Support for Time-Related Information
• Effective Date: 23-Apr-2020

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.

Standards Content (Sample)

INTERNATIONAL ISO/IEC
STANDARD 19075-2
First edition
2021-08
Information technology — Guidance
for the use of database language
SQL —
Part 2:
Time-related information
Technologies de l'information — Recommandations pour l'utilisation
du langage de base de données SQL —
Partie 2: Informations d'horodatage
Reference number
©
ISO/IEC 2021
© ISO/IEC 2021
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting
on the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address
below or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: [phone]
Email: [e-mail]
Website: www.iso.org
Published in Switzerland
ii © ISO/IEC 2021 – All rights reserved

ISO/IEC19075-2:2021(E)
Contents Page
Foreword.vi
Introduction.viii
1 Scope.1
2 Normativereferences.2
3 Termsanddefinitions.3
4 Time-relateddatatypes,constructs,operators,andpredicates.4
4.1 Contextoftime-relatedspecifications.4
4.2 Datetimetypes.4
4.3 DateTimeliterals.6
4.4 Intervaltypes.7
4.5 Interval literals.8
4.6 Periods.9
4.7 Operationsinvolvingdatetimesandintervals.10
4.8 Time-relatedpredicates.10
4.8.1 Overlaps predicate.10
4.8.2 Period predicates.11
5 Time-relatedtables.14
5.1 Introductiontotime-relatedtables.14
5.2 Application-timeperiodtables.14
5.2.1 Introductiontoapplication-timeperiodtables.14
5.2.2 Extensionstoprimarykey/uniqueconstraints.14
5.2.3 Extensionstoreferentialconstraints.15
5.2.4 Insertingrowsintotablescontaininganapplication-timeperioddefinition.18
5.2.5 Updatingrowsoftablescontaininganapplication-timeperioddefinition.18
5.2.6 Updatingthetablebetweenspecificpointsintime.19
5.2.7 Deletingrowsfromtablescontaininganapplication-timeperioddefinition.22
5.2.8 Deletingrowsbetweenspecificpointsintime.22
5.2.9 Queryingtablescontainingaperioddefinition.24
5.2.10 Addingaperioddefinitiontoatable.25
5.3 System-versionedtables.26
5.3.1 Introductiontosystem-versionedtables.26
5.3.2 Primarykeyandreferentialconstraints.27
5.3.3 Updatingrowsinsystem-versionedtables.28
5.3.4 Deletingrowsinsystem-versionedtables.29
5.3.5 Queryingsystem-versionedtables.29
5.4 Bi-temporal tables.30
Bibliography.32
©ISO/IEC2021–Allrightsreserved iii

ISO/IEC19075-2:2021(E)
Index.33
iv ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
Tables
Table Page
1 Fieldsindatetimevalues.4
2 Mappingofdatetimefieldstodatetimedatatypes.5
3 Examplesofthedatetimedatatypes.6
4 Examplesofdatetimeliterals.6
5 Fieldsinyear-monthINTERVALvalues.7
6 Fieldsinday-timeINTERVALvalues.7
7 Examplesofday-timeINTERVALvalues.8
8 Examplesofintervalliterals.8
9 Validoperatorsinvolvingdatetimesandintervals.10
10 Exampledatatableempforprimarykeywithapplication-timeperiod.15
11 Exampledatatabledeptforforeignkeywithapplication-timeperiod.16
12 Exampledatatableempforforeignkeywithapplication-timeperiod.16
13 Contentoftableempafterinsertwithapplication-timeperiod.18
14 Contentoftableempbeforeupdatingarow.18
15 Contentoftableempafterupdatingarow.19
16 Contentoftableempbeforeupdatingtheapplication-timeperiodofarow.19
17 Contentoftableempafterupdatingtheapplication-timeperiodofarow.19
18 Contentoftableempbeforeupdatingarowforaninnerpartofaperiod.20
19 Contentoftableempafterupdatingarowforaninnerpartofaperiod.20
20 Contentoftableempbeforeupdatingarowforallofaperiod.20
21 Contentoftableempafterupdatingarowforallofaperiod.21
22 Contentoftableempbeforeupdatingtheedgeofaperiodofarow.21
23 Contentoftableempafterupdatingtheedgeofaperiodofarow.21
24 Contentoftableempbeforedeletinganinnerpartofaperiod.22
25 Contentoftableempwithapplication-timeperiodafterdeletinganinnerpartofaperiod.23
26 Contentoftableempwithapplication-timeperiodbeforedeletingarow.23
27 Contentoftableempbeforedeletinganedgeofaperiod.23
28 Contentoftableempafterdeletinganedgeofaperiod.24
29 Contentofsystem-versionedtableempbeforeupdatingarow.28
30 Contentofsystem-versionedtableempafterupdatingarow.28
31 Contentofsystem-versionedtableempbeforedeletingarow.29
32 Contentofsystem-versionedtableempafterdeletingarow.29
©ISO/IEC2021–Allrightsreserved v

ISO/IEC19075-2:2021(E)
Foreword
ISO(theInternationalOrganizationforStandardization)andIEC(theInternationalElectrotechnical
Commission)formthespecializedsystemforworldwidestandardization.Nationalbodiesthatare
membersofISOorIECparticipateinthedevelopmentofInternationalStandardsthroughtechnical
committeesestablishedbytherespectiveorganizationtodealwithparticularfieldsoftechnicalactivity.
ISOandIECtechnicalcommitteescollaborateinfieldsofmutualinterest.Otherinternationalorganizations,
governmentalandnon-governmental,inliaisonwithISOandIEC,alsotakepartinthework.
Theproceduresusedtodevelopthisdocumentandthoseintendedforitsfurthermaintenanceare
describedintheISO/IECDirectives,Part1.Inparticular,thedifferentapprovalcriterianeededforthe
differenttypesofdocumentshouldbenoted.Thisdocumentwasdraftedinaccordancewiththeeditorial
rulesoftheISO/IECDirectives,Part2(seewww.iso.org/directivesorwww.iec.ch/mem-
bers_experts/refdocs).
Attentionisdrawntothepossibilitythatsomeoftheelementsofthisdocumentmaybethesubjectof
patentrights.ISOandIECshallnotbeheldresponsibleforidentifyinganyorallsuchpatentrights.Details
ofanypatentrightsidentifiedduringthedevelopmentofthedocumentwillbeintheIntroductionand/or
ontheISOlistofpatentdeclarationsreceived(seewww.iso.org/patents),ortheIEClistofpatent
declarationsreceived(seepatents.iec.ch).
Anytradenameusedinthisdocumentisinformationgivenfortheconvenienceofusersanddoesnot
constituteanendorsement.
Foranexplanationofthevoluntarynatureofstandards,themeaningofISOspecifictermsandexpressions
relatedtoconformityassessment,aswellasinformationaboutISO’sadherencetotheWorldTrade
Organization(WTO)principlesintheTechnicalBarrierstoTrade(TBT)seewww.iso.org/iso/fore-
word.html.IntheIEC,seewww.iec.ch/understanding-standards.
ThisdocumentwaspreparedbyTechnicalCommitteeISO/IECJTC1,Informationtechnology,Subcom-
mitteeSC32,Datamanagementandinterchange.
ThisfirsteditionofISO/IEC19075-2cancelsandreplacesISO/IECTR19075-2:2015.
ThisdocumentisintendedtobeusedinconjunctionwiththefollowingeditionsofthepartsoftheISO/IEC
9075series:
— ISO/IEC9075-1,sixtheditionorlater;
— ISO/IEC9075-2,sixtheditionorlater;
— ISO/IEC9075-3,sixtheditionorlater;
— ISO/IEC9075-4,seventheditionorlater;
— ISO/IEC9075-9,fiftheditionorlater;
— ISO/IEC9075-10,fiftheditionorlater;
— ISO/IEC9075-11,fiftheditionorlater;
— ISO/IEC9075-13,fiftheditionorlater;
— ISO/IEC9075-14,sixtheditionorlater;
— ISO/IEC9075-15,secondeditionorlater;
— ISO/IEC9075-16,firsteditionorlater.
vi ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.
Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A
completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
©ISO/IEC2021–Allrightsreserved vii

ISO/IEC19075-2:2021(E)
Introduction
Theorganizationofthisdocumentisasfollows:
1) Clause1,“Scope”,specifiesthescopeofthisdocument.
2) Clause2,“Normativereferences”,identifiesadditionalstandardsthat,throughreferenceinthis
document,constituteprovisionsofthisdocument.
3) Clause3,“Termsanddefinitions”,definesthetermsanddefinitionsusedinthisdocument.
4) Clause4,“Time-relateddatatypes,constructs,operators,andpredicates”,explainstime-relateddata
types,operators,andpredicatesinSQL.
5) Clause5,“Time-relatedtables”,explainshowtime-relatedtablesareused.
viii ©ISO/IEC2021–Allrightsreserved

INTERNATIONAL STANDARD ISO/IEC 19075-2:2021(E)
Informationtechnology—GuidancefortheuseofdatabaselanguageSQL—
Part2:
Time-relatedinformation
1 Scope
ThisdocumentdescribesthesupportinSQLfortime-relatedinformation.
ThisdocumentdiscussesthefollowingfeaturesoftheSQLlanguage:
— Time-relateddatatypes
— Operationsontime-relateddata
— Time-relatedPredicates
— Application-timeperiodtables
— System-versionedtables
— Bi-temporaltables
©ISO/IEC2021–Allrightsreserved 1

ISO/IEC19075-2:2021(E)
2 Normativereferences
Thefollowingdocumentsarereferredtointhetextinsuchawaythatsomeoralloftheircontentconsti-
tutesrequirementsofthisdocument.Fordatedreferences,onlytheeditioncitedapplies.Forundated
references,thelatesteditionofthereferenceddocument(includinganyamendments)applies.
ISO/IEC9075-1,Informationtechnology—Databaselanguages—SQL—Part1:Framework
(SQL/Framework)
ISO/IEC9075-2,Informationtechnology—Databaselanguages—SQL—Part2:Foundation
(SQL/Foundation)
2 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
3 Termsanddefinitions
Forthepurposesofthisdocument,thetermsanddefinitionsgiveninISO/IEC9075-1apply.
ISOandIECmaintainterminologicaldatabasesforuseinstandardizationatthefollowingaddresses:
— IECElectropedia:availableathttp://www.electropedia.org/
— ISOOnlinebrowsingplatform:availableathttp://www.iso.org/obp
©ISO/IEC2021–Allrightsreserved 3

ISO/IEC19075-2:2021(E)
4 Time-relateddatatypes,constructs,operators,andpredicates
4.1 Contextoftime-relatedspecifications
TherequirementsforthematerialdiscussedinthisdocumentshallbeasspecifiedinISO/IEC9075-1
andISO/IEC9075-2.
4.2 Datetimetypes
ISO/IEC9075-2definesthetime-relateddatatypes,constructs,operators,andpredicatesdescribedin
thisdocument.ISO/IEC9075-2specifiesrequirementsforthematerialdiscussedinthisdocument.
Therearethreedatetimetypes,eachofwhichismadeupofdifferentdatetimefields.
AvalueofdatatypeTIMESTAMPismadeupofthedatetimefieldsYEAR,MONTH,DAY,HOUR,MINUTE,
andSECOND.ItisalwaysavalidtimeatavalidGregoriandate.
AvalueofdatatypeTIMEcomprisesvaluesofthedatetimefieldsHOUR,MINUTEandSECOND.Itis
alwaysavalidtimeofday.
AvalueofdatatypeDATEismadeupofthedatetimefieldsYEAR,MONTH,andDAY.Itisalwaysavalid
Gregoriandate.
TIMESTAMPandTIMEmaybespecifiedwithanumberof(decimal)digitsoffractionalsecondsprecision.
TIMESTAMPandTIMEmayalsobespecifiedasbeingWITHTIMEZONE,inwhichcaseeveryvaluehas
associatedwithitatimezonedisplacement(theoffsetoftheTIMESTAMPorTIMEfromCoördinated
UniversalTime,UTC).IncomparingvaluesofadatatypeWITHTIMEZONE,thevalueofthetimezone
displacementisdisregarded.
Table1,“Fieldsindatetimevalues”,specifiesthefieldsthatcanmakeupadatetimevalue.
Table1—Fieldsindatetimevalues
Keyword Meaning
YEAR Year,between0001and9999
MONTH Monthwithinyear,between01and12
DAY Daywithinmonth,between1and31,butfurtherconstrainedbythevalue
ofMONTHandYEARfields,accordingtotherulesforwell-formeddates
intheGregoriancalendar.
HOUR Hourwithinday,between00and23
MINUTE Minutewithinhour,between00and59
4 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
4.2 Datetimetypes
Keyword Meaning
SECOND Secondandpossiblyfractionofasecondwithinminute,between00and
61.999.
TIMEZONE_HOUR Hourvalueoftimezonedisplacement,between–14and14.Therangefor
timezoneintervalsislargerthanmanyreadersmightexpectbecauseitis
governedbypoliticaldecisionsingovernmentalbodiesratherthanbyany
naturallaw.
TIMEZONE_MINUTE Minutevalueoftimezonedisplacement,between–59and59.Whenthe
valueofTIMEZONE_HOURiseither–12or14,thevalueofTIME-
ZONE_MINUTEisrestrictedtobe00(zeros).
Thereisanorderingofthesignificanceofthesefields.Thisis,frommostsignificanttoleastsignificant:
YEAR,MONTH,DAY,HOUR,MINUTE,andSECOND.
Table2,“Mappingofdatetimefieldstodatetimedatatypes”,clarifieswhichofthevariousdatetimefields
canappearineachofthedatetimedatatypes.
Table2—Mappingofdatetimefieldstodatetimedatatypes
Datatype YEAR MONTH DAY HOUR MINUTE SECOND TZ TZ
HOUR MINUTE
TIMESTAMP Y Y Y Y Y Y N N
TIMESTAMP Y Y Y Y Y Y Y Y
WITHTIME
ZONE
TIME N N N Y Y Y N N
TIMEWITH N N N Y Y Y Y Y
TIMEZONE
DATE Y Y Y N N N N N
Thesurfaceoftheearthisdividedintozones,calledtimezones,inwhicheverycorrectclocktellsthe
sametime,knownaslocaltime.LocaltimeisequaltoUTC(CoordinatedUniversalTime)plusthetime
zonedisplacement,whichisanintervalvaluethatrangesbetweenINTERVAL'–12:00'HOURTOMINUTE
andINTERVAL'+14:00'HOURTOMINUTE.Thetimezonedisplacementisconstantthroughoutatime
zone,changingatthebeginningandendofSummerTime,whereapplicable.
NOTE1—IANATimeZoneDatabaseisoneresourcefromwhichSQL-implementationscandeterminedetailsabouttime
zones.
Adatetimevalue,ofdatatypeTIMEWITHOUTTIMEZONEorTIMESTAMPWITHOUTTIMEZONE,may
representalocaltime,whereasadatetimevalueofdatatypeTIMEWITHTIMEZONEorTIMESTAMP
WITHTIMEZONErepresentsUTC.
Table3,“Examplesofthedatetimedatatypes”,providesafewexamplesofdatetimedatatypedeclarations.
©ISO/IEC2021–Allrightsreserved 5

ISO/IEC19075-2:2021(E)
4.2 Datetimetypes
Table3—Examplesofthedatetimedatatypes
Datatype Explanation
TIMESTAMP(2) Thisatimestampwithafractionalprecisionof2forthesecondsfield
TIMESTAMP Thisisatimestampwithnofractionalprecisionforthesecondsfield
TIME(2) Thisisatimewithafractionalprecisionof2forthesecondsfield
TIME Thisisatimewithnofractionalprecisionforthesecondsfield
DATE Thisisadate
Onoccasion,UTCisadjustedbytheomissionofasecondortheinsertionofa“leapsecond”inorderto
maintainsynchronizationwithsiderealtime.Thisimpliesthatsometimes,butveryrarely,aparticular
minutewillcontainexactly59,61,or62seconds.Intervalarithmeticthatinvolvesleapsecondsordis-
continuitiesincalendarswillproduceimplementation-definedresults.
Fortheconvenienceofusers,wheneveradatetimevaluewithtimezoneistobeimplicitlyderivedfrom
onewithout(forexample,inasimpleassignmentoperation),SQLassumesthevaluewithouttimezone
tobelocal,subtractsthecurrentdefaulttimezonedisplacementoftheSQL-sessionfromittogiveUTC,
andassociatesthattimezonedisplacementwiththeresult.
Conversely,wheneveradatetimevaluewithouttimezoneistobeimplicitlyderivedfromonewith,SQL
assumesthevaluewithtimezonetobeUTC,addsthetimezonedisplacementtoittogivelocaltime,and
theresult,withoutanytimezonedisplacement,islocal.
DatetimedatatypeswillallowdatesintheGregorianformattobestoredinthedaterange0001–01–01
CEthrough9999–12–31CE.
NOTE2—Supportfordatespriorto0001–01–01CEisentirelyimplementation-defined.ISO/IEC9075-2neitherspecifies
syntaxorsemanticsforsuchdates,norprohibitsSQL-implementationsfromprovidingsupport.Additionallynosupport
isprovidedfordatesusingothercalendarsystems(forexample,Japaneseimperialcalendars)orepochaldatingsystems
(e.g.,,geologicaleras).
4.3 DateTimeliterals
Adatetimeliteralcanspecifydatetimevaluesoftherespectivedatetimedatatypes.Andatetimeliteral
consistsofthreeparts.Thekeywordforthedatatype,thevalueinafixedformatandthetimezonedis-
placement.Theformatforthedatetimeliteralisyyyy–mm–ddhh:mi:ss.ssss.Thedatatypeisautomatically
assignedtotheliteralsdependingontheircontentandthekeywordused.
Table4,“Examplesofdatetimeliterals”,givesexamplesofafewdatetimeliterals.
Table4—Examplesofdatetimeliterals
Literal Datatype Explanation
TIMESTAMP'2014–06–11 TIMESTAMP(2) Thisisatimestampforthe11thofJune2014at
09:15:22.03' 9hours,15minutesand22.03seconds
TIME'12:00:01+01:00' TIME(0)WITH Onesecondafternooninthetimezonewitha
TIMEZONE displacementof+1hour
6 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
4.3 DateTimeliterals
Literal Datatype Explanation
DATE'0001–01–01' DATE ThefirstofJanuaryinyear1.Thisisthefirst
possibledateinSQL
4.4 Intervaltypes
Avalueofanintervaltyperepresentsthedurationofaperiodoftime.Therearetwoclassesofintervals.
Oneclass,calledyear-monthintervals,hasanintervalprecisionthatincludesaYEARfieldoraMONTH
field,orboth.Theotherclass,calledday-timeintervals,hasanexpressorimpliedintervalprecisionthat
canincludeanysetofcontiguousfieldsotherthanYEARorMONTH.
Table5,“Fieldsinyear-monthINTERVALvalues”,specifiesthefieldsthatmakeupayear–monthinterval.
Table5—Fieldsinyear-monthINTERVALvalues
Keyword Meaning ValidvaluesofINTERVALfields
YEAR Years Unconstrainedexceptbytheleadingfield
precision
MONTH Months Months(withinyears)(0-11)
Table6,“Fieldsinday-timeINTERVALvalues”,specifiesthefieldsthatmakeupaday-timeinterval.A
day-timeintervalismadeupofacontiguoussubsetofthosefields.
Table6—Fieldsinday-timeINTERVALvalues
Keyword Meaning ValidvaluesofINTERVALfields
DAY Days Unconstrainedexceptbytheleadingfieldprecision
HOUR Hours Hours(withindays)(0-23)
MINUTE Minutes Minutes(withinhours)(0-59)
SECOND Secondsandpossibly Seconds(withinminutes)(0-59.999.)
fractionsofasecond
Theactualsubsetoffieldsthatcompriseavalueofeithertypeofintervaliscalledtheprecisionofthe
value.
Withinavalueoftypeinterval,thefirstfieldisconstrainedonlybytheprecisionoftheleadingfield.
ValuesinintervalfieldsotherthanSECONDareintegers.SECOND,canbedefinedtohaveaprecisionof
fractionalsecondsthatindicatesthenumberofdecimaldigitsmaintainedfollowingthedecimalpointin
thesecondsvalue.
Table7,“Examplesofday-timeINTERVALvalues”,givesexamplesofafewday-timeintervalliterals.
©ISO/IEC2021–Allrightsreserved 7

ISO/IEC19075-2:2021(E)
4.4 Intervaltypes
Table7—Examplesofday-timeINTERVALvalues
Datatype Explanation
INTERVALYEARTOMONTH Thisisayear-monthintervalwhichismadeupofthefields
yearandmonth
INTERVALHOURTOSECOND(2) Thisisaday-timeinterval,whichismadeupofthefields
hour,minuteandsecondwithafractionalprecisionof2.The
dayvalueisalways0(zero)
INTERVALDAY Thisisaday-timeinterval,whichismadeupofjustthefield
day.Allotherfieldsare0(zero)
INTERVALMONTH Thisisayear-monthintervalwhichismadeupofjustthe
fieldmonth.Theyearhasalwaysthevalue0(zero)
INTERVALSECOND(4) Thisisaday-timeinterval,whichismadeupofjustthefield
secondwithafractionalprecisionof4.Allotherfieldsare0
(zero)
Year-monthintervalsarecomparableonlywithotheryear-monthintervals.Iftwoyear-monthintervals
havedifferentintervalprecisions,theyare,forthepurposeofanyoperationsbetweenthem,converted
tothesameprecisionbyappendingnewdatetimefieldstoeitheroneoftheendsofoneinterval,orto
bothends.Newdatetimefieldsareassignedavalueof0(zero).
Day-timeintervalsarecomparableonlywithotherday-timeintervals.Iftwoday-timeintervalshave
differentintervalprecisions,theyare,forthepurposeofanyoperationsbetweenthem,convertedtothe
sameprecisionbyappendingnewdatetimefieldtoeitheroneoftheendsofoneinterval,ortobothends.
Newdatetimefieldsareassignedavalueof0(zero).
4.5 Intervalliterals
Anintervalliteralcanspecifyintervalvaluesoftherespectiveintervaldatatypes.Anintervalliteral
consistsofthreeparts.ThekeywordINTERVAL,thedatetimeintervalinafixedformatandtheinterval
qualifier.Theformatforthedatetimeliteralisyyyy-mm-ddhh:mi:ss.ssss.Anintervalliteralcanbepositive
ornegative.Thedatatypeisautomaticallyassignedtotheliteraldependingonthekeywordsusedfor
theintervalqualifier.
Table8,“Examplesofintervalliterals”,givesexamplesofafewdatetimeliterals.
Table8—Examplesofintervalliterals
Literal Datatype Explanation
INTERVAL'1'MONTH INTERVALMONTH Onemonth
INTERVAL'0110'DAYTO INTERVALDAYTOHOUR Onedayandtenhours
HOUR
INTERVAL'10:10:10.1'HOUR INTERVALHOURTOSECOND Tenhours,tenminutesand10.1sec-
TOSECOND(1) (1) onds
8 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
4.5 Intervalliterals
Literal Datatype Explanation
INTERVAL'–10'MINUTE INTERVALMINUTE Minus10minutes.
4.6 Periods
Aperiodisanobjectassociatedwithasinglebasetable.Aperioddefinitionforagiventableassociates
aperiodnamewithapairofcolumnnamesdefinedforthattable.Itisrequiredthatthecolumnsareboth
ofadatetimedatatypeandknownnotnullable.Furthermore,itisrequiredthatthedeclaredtypesof
bothcolumnsareidentical.
Similartocolumndefinitionsandconstraintdefinitions,aperioddefinitioncanonlybespecifiedaspart
ofatabledefinition.Foratablewithaperioddefinition,everyrowinthattableisconsideredtobe
associatedwithaperiodwhosenamecorrespondstotheperiodnamespecifiedintheperioddefinition
andwhosestartandendtimesareprovidedbythecolumnvaluesspecifiedintheperioddefinition.
Forexample,considerthetabledefinition:
CREATE TABLE emp
(emp_id INTEGER NOT NULL,
name VARCHAR(30),
salary DECIMAL(5,2),
dept_id INTEGER,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD FOR business_time (bus_start, bus_end)
);
Theperioddefinition“PERIODFORbusiness_time(bus_start,bus_end)”intheprecedingtabledefinition
definesaperiodnamedbusiness_timefortheemptablewiththevalueinthebus_startcolumnforagiven
rowactingasthestarttimeofthebusiness_timeperiodassociatedwiththerowandthevalueinthe
bus_endcolumnforagivenrowactingastheendtimeofthebusiness_timeperiodassociatedwiththe
row.
Ingeneral,foraperiodwithnameP,thefirstcolumnintheperioddefinitioniscalledthePperiodstart
column,andthesecondcolumniscalledthePperiodendcolumn.Thecolumnsparticipatinginaperiod
definitionshallsatisfytheconditions:
1) BothcolumnsshallbedeclaredasNOTNULL.
2) Thedatatypeofbothcolumnsshallbeadatetimedatatype.
3) Thedatatypesofbothcolumnsshallbeidentical.
Foranygivenperiod,theSQL-implementationensuresthevalueoftheperiodendcolumnisalways
greaterthanthevalueoftheperiodstartcolumn.Ingeneral,theperiodisasetofdatetimevaluescon-
sistingofeverydistinctvalueinthetimelinestartingfromtheperiodstartvalueuptobutnotincluding
theperiodendvalue.
Agiventablecanhaveatmosttwoperioddefinitions.Oneoftheperiodsisreservedforsupportingsystem-
timedimension,i.e.,,system-versionedtableswithapredefinedperiodnameof“SYSTEM_TIME”forsuch
aperiod.Thisleavesoneperiodforsupportingtheapplication-timedimensionwithauser-definedname
forsuchaperiod.
©ISO/IEC2021–Allrightsreserved 9

ISO/IEC19075-2:2021(E)
4.7 Operationsinvolvingdatetimesandintervals
4.7 Operationsinvolvingdatetimesandintervals
Table9,“Validoperatorsinvolvingdatetimesandintervals”,specifiesthedeclaredtypesofarithmetic
expressionsinvolvingdatetimeandintervaloperands.
Table9—Validoperatorsinvolvingdatetimesandintervals
Operand1 Operator Operand2 ResultType
Datetime – Datetime Interval
Datetime +or– Interval Datetime
Interval + Datetime Datetime
Interval +or– Interval Interval
Interval *or/ Numeric Interval
Numeric * Interval Interval
Arithmeticoperationsinvolvingvaluesoftypedatetimeorintervalobeythenaturalrulesassociated
withdatesandtimesandyieldvaliddatetimeorintervalresultsaccordingtotheGregoriancalendar.
Operationsinvolvingvaluesoftypedatetimerequirethatthedatetimevaluesbecomparable.Operations
involvingvaluesoftypeintervalrequirethattheintervalvaluesbecomparable.
Operationsinvolvingadatetimeandanintervalpreservethetimezoneofthedatetimeoperand.Ifthe
datetimeoperanddoesnotincludeatimezonedisplacement,thentheresulthasnotimezonedisplace-
ment.
Anextractexpressionoperatesonadatetimeorintervalandreturnsanexactnumericvaluerepresenting
thevalueofonecomponentofthedatetimeorinterval.
Anintervalabsolutevaluefunctionoperatesonanintervalargumentandreturnsitsabsolutevaluein
thesamemostspecifictype.
4.8 Time-relatedpredicates
4.8.1 Overlapspredicate
AnoverlapspredicateusestheoperatorOVERLAPStodeterminewhetherornottwochronological
periodsoverlapintime.Achronologicalperiodisspecifiedeitherasapairofdatetimes(startingand
ending)orasastartingdatetimeandaninterval.Ifthelengthoftheperiodisgreaterthan0(zero),then
theperiodconsistsofallpointsoftimegreaterthanorequaltothelowerendpoint,andlessthanthe
upperendpoint.Ifthelengthoftheperiodisequalto0(zero),thentheperiodconsistsofasinglepoint
intime,thelowerendpoint.Twochronologicalperiodsoverlapiftheyhaveatleastonepointincommon.
10 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
4.8 Time-relatedpredicates
4.8.2 Periodpredicates
Therearesevenperiodpredicatesavailable,whicharediscussedinthelistappearinglaterinthisSub-
clause.
ThesepredicatestaketwooperandsseparatedbykeywordssuchasCONTAINS,OVERLAPS,etc.For
predicatesotherthantheperiodcontainspredicate,eachoftheoperandscanbeeitheraperiodname
orthesyntacticconstructoftheformPERIOD(datetimevalueexpression,datetimevalueexpression),
calledaperiodconstructor.Fortheperiodcontainspredicate,thefirstoperandcanbeeitheraperiod
nameoraperiodconstructorwhilethesecondoperandcanbeeitheraperiodname,aperiodconstructor,
oradatetimevalueexpression.
Notethatthepredicatescanbeusedwhereverpredicatesyntaxisallowed,e.g.,,intheWHEREclause,
ontheONclauseofajoinedtable,etc.
Adescriptionofeachofthepredicatesisprovidedinthefollowinglist(assumexisthefirstoperandand
yisthesecondoperand;furtherassumethatwhenxandystandforperiods,theyaremodelledas(closed,
open)periodswithxsandxeasthestartandendtimesofperiodxandysandyeasthestartandend
timesofperiody):
Fortheexamplesconsiderthetabledefinition:
CREATE TABLE emp
(emp_id INTEGER NOT NULL,
name VARCHAR(30),
salary DECIMAL(5,2),
dept_id INTEGER,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD FOR business_time (bus_start, bus_end)
);
1) Thepredicate“xOVERLAPSy”applieswhenbothxandyareeitherperiodnamesorperiodconstruc-
tors.ThispredicatereturnsTrueifthetwoperiodshaveatleastonetimepointincommon,i.e.,,if
xsys.
Aquerytoretrieveallemprowswhoseapplication-timeperiodsoverlapagivenperiod,sayaperiod
withthestartdateof2001-01-01andtheenddateof2001-07-27usingexistingSQLsyntaxlooks
asfollows:
SELECT *
FROM emp e
WHERE e.bus_start < DATE '2001-07-27' AND e.bus_end > DATE '2001-01-01'
Theimmediatelyprecedingquerycanbewritteninamoresuccinctandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp e
WHERE e.business_time OVERLAPS PERIOD (DATE '2001-01-01', DATE '2001-07-27')
2) Thepredicate“xEQUALSy”applieswhenbothxandyareeitherperiodnamesorperiodconstructors.
ThispredicatereturnsTrueifthetwoperiodshaveeverytimepointincommon,i.e.,,ifxs=ysand
xe=ye
Aquerytoretrieveallemprowswhoseapplication-timeperiodsisequaltoagivenperiod,saya
periodwiththestartdateof2001-01-01andtheenddateof2001-07-27usingexistingSQLsyntax
looksasfollows:
©ISO/IEC2021–Allrightsreserved 11

ISO/IEC19075-2:2021(E)
4.8 Time-relatedpredicates
SELECT *
FROM emp e
WHERE e.bus_start = DATE '2001-01-01' AND e.bus_end = DATE '2001-07-27'
Theimmediatelyprecedingexamplecanbewritteninamoresuccinctandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp e
WHERE e.business_time EQUALS PERIOD (DATE '2001-01-01', DATE '2001-07-27')
3) Thepredicate“xCONTAINSy”applieswhen
a) Bothxandyareeitherperiodnamesorperiodconstructors.Inthiscase,thepredicatereturns
Trueifxcontainseverytimepointiny,i.e.,,ifxs≤ysandxe≥ye.
b) xiseitheraperiodnameoraperiodconstructorandyisadatetimevalueexpression.Inthis
case,thepredicatereturnsTrueifxcontainsy,i.e.,,ifxs≤yandxe>y.
Aquerytoretrieveallemprowswhoseapplication-timeperiodscontainthedate2001-01-01using
existingSQLsyntaxlooksasfollows:
SELECT *
FROM emp
WHERE bus_start ≤ DATE '2001-01-01' AND bus_end > DATE '2001-01-01'
Theimmediatelyprecedingexamplecanbewritteninamoresuccinctandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp
WHERE business_time CONTAINS DATE '2001-01-01'
4) Thepredicate“xPRECEDESy”applieswhenbothxandyareeitherperiodnamesorperiodconstruc-
tors.Inthiscase,thepredicatereturnsTrueiftheendvalueofxislessthanorequaltothestartvalue
ofy,i.e.,ifxe≤ys.
Aquerytoretrieveallemprowswhoseapplication-timeperiodsprecedesagivenperiod,saya
periodwiththestartdateof2001-01-01andtheenddateof2001-07-27usingexistingSQLsyntax
looksasfollows:
SELECT *
FROM emp e
WHERE e.bus_end ≤ DATE '2001-01-01'
Theimmediatelyprecedingexamplecanbewritteninamoreexplicitandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp e
WHERE e.business_time PRECEDES PERIOD (DATE '2001-01-01', DATE '2001-07-27')
5) Thepredicate“xSUCCEEDSy”applieswhenbothxandyareeitherperiodnamesorperiodconstruc-
tors.Inthiscase,thepredicatereturnsTrueifthestartvalueofxisgreaterthanorequaltotheend
valueofy,i.e.,,ifxs≥ye.
12 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
4.8 Time-relatedpredicates
Aquerytoretrieveallemprowswhoseapplication-timeperiodssucceedsagivenperiod,saya
periodwiththestartdateof2001-01-01andtheenddateof2001-07-27usingexistingSQLsyntax
looksasfollows:
SELECT *
FROM emp e
WHERE e.bus_start ≥ DATE '2001-07-07'
Theimmediatelyprecedingexamplecanbewritteninamoreexplicitandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp e
WHERE e.business_time SUCCEEDS PERIOD (DATE '2001-01-01', DATE '2001-07-27')
6) Thepredicate“xIMMEDIATELYPRECEDESy”applieswhenbothxandyareeitherperiodnamesor
periodconstructors.Inthiscase,thepredicatereturnsTrueiftheendvalueofxisequaltothestart
valueofy,i.e.,,ifxe=ys.
Aquerytoretrieveallemprowswhoseapplication-timeperiodsimmediatelyprecedesagiven
period,sayaperiodwiththestartdateof2001-01-01andtheenddateof2001-07-27usingexisting
SQLsyntaxlooksasfollows:
SELECT *
FROM emp e
WHERE e.bus_end = DATE '2001-01-01'
Theimmediatelyprecedingexamplecanbewritteninamoreexplicitandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp e
WHERE e.business_time IMMEDIATELY PRECEDES
PERIOD (DATE '2001-01-01', DATE '2001-07-27')
7) Thepredicate“xIMMEDIATELYSUCCEEDSy”applieswhenbothxandyareeitherperiodnamesor
periodconstructors.Inthiscase,thepredicatereturnsTrueifthestartvalueofxisequaltotheend
valueofy,i.e.,,ifxs=ye.
Aquerytoretrieveallemprowswhoseapplication-timeperiodsimmediatelysucceedsagiven
period,sayaperiodwiththestartdateof2001-01-01andtheenddateof2001-07-27usingexisting
SQLsyntaxlooksasfollows:
SELECT *
FROM emp e
WHERE e.bus_start = DATE '2001-07-07'
Theimmediatelyprecedingexamplecanbewritteninamoreexplicitandmoreintuitivewaywhen
thepredicatesintheWHEREclauseareexpressedusingthenameoftheperiodratherthanthestart
andendcolumnsoftheperiod,asshownhere:
SELECT *
FROM emp e
WHERE e.business_time IMMEDIATELY SUCCEEDS
PERIOD (DATE '2001-01-01', DATE '2001-07-27')
©ISO/IEC2021–Allrightsreserved 13

ISO/IEC19075-2:2021(E)
5 Time-relatedtables
5.1 Introductiontotime-relatedtables
Therearethreedifferentflavorsoftime-relatedtables.Thefirsttypehasjustanapplication-timeperiod,
thesecondhasjustthesystemversioningperiod,whereasthethirdtype,calledabi-temporaltable,has
bothtypesofperiods.
5.2 Application-timeperiodtables
5.2.1 Introductiontoapplication-timeperiodtables
Application-timeperiodtablesareintendedformeetingtherequirementsofapplicationsthatareinter-
estedincapturingtimeperiodsduringwhichthedataisbelievedtobevalidintherealworld.Atypical
exampleofsuchapplicationsisaninsuranceapplication,whereitisnecessarytokeeptrackofthespecific
policydetailsofagivencustomerthatareineffectatanygivenpointintime.
Aprimaryrequirementofsuchapplicationsisthattheuserbeputinchargeofsettingthestartandend
timesofthevalidityperiodofrows,andtheuserbefreetoassignanytimevalues,eitherinthepast,
currentorinthefuture,forthestartandendtimes.Anotherrequirementofsuchapplicationsisthatthe
userbepermittedtoupdatethevalidityperiodsoftherowsaserrorsarediscoveredornewinformation
ismadeavailable.
Anytablethatcontainsaperioddefinitionwithauser-definednameisanapplication-timeperiodtable.
Userscanpickanynametheywantforthenameoftheperiodaswellasforthenamesofcolumnsthat
actasthestartandendcolumnsoftheperiod.Thedatatypesoftheperiodstartandendcolumnsshall
beeitherDATEoratimestamptype,andthedatatypesofbothcolumnsshallbethesame.
5.2.2 Extensionstoprimarykey/uniqueconstraints
Userscandefineprimarykey/uniqueconstraintsontablescontaininganapplication-timeperiodusing
thecurrentsyntaxwithexactlythesamebehavior.However,thepresenceofanapplication-timeperiod
providesanopportunitytoenhancethenotionofprimarykey/uniqueconstraintsonthattable.For
example,assumethatanapplicationrequiresthecreationaprimarykeyfortheemptablethatcorresponds
tothecombinationoftheemp_id,bus_start,andbus_endcolumns,suchthatforagivenemp_idvalueand
agivenpointintimeT,thereisexactlyonerowwhoseapplication-timeperiod(i.e.,,setofvaluesfrom
bus_startvaluethroughtobutnotincludingbus_endvalue)containsT.Thismeansthatforanyselection
basedonaspecifiedemp_idvalueandaspecifieddate,oneandonlyonerowisretrieved.Assumethe
emptablecontainstherowsshowninTable10,“Exampledatatableempforprimarykeywithapplication-
timeperiod”:
14 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
5.2 Application-timeperiodtables
Table10—Exampledatatableempforprimarykeywithapplication-timeperiod
emp_id name salary dept_id bus_start bus_end
100 Tom 3000 1 2001-07-27 2002-01-01
100 Tom 3500 10 2002-01-01 2003-01-01
100 Tom 4000 20 2003-01-01 2004-01-01
Inthisexampletherearethreerows.Notefirst,theapplication-timeperiodsacrossallthreerowsare
notoverlapping.Notesecond,theendofthetimeperiodfromrow1isthestarttimefromrow2,andthe
endtimeforrow2isthestarttimeofrow3.Notethirdthatduringeachofthesetimeperiods,the
employeeisassignedtoadifferentdepartmentandhadadifferentsalary.Notefourththateventhough
therearemultiplerowsforthesameemployeespanningaperiodfrom2001-07-27through2004-01-
01,thereisonlyoneemprowwhoseapplication-timeperiodcontainsanyspecifieddateintheperiod
from2001-07-27through2004-01-01.Forexample,aspecifieddateof2002-12-01wouldresultinthe
selectionofrow2.Supposehowever,thatrow2isdeleted.Thiswouldcauseagapintheperiodfrom
2001-07-27through2004-01-01.Thus,iftherewasaquerytoproducethedept_idandsalaryforthe
employeeonthedate,2002-12-01,thequerywouldfailbecausethereisnorowwhoseapplication-time
periodcontainsthedate2002-12-01.However,iftherewasaquerytoproducethedept_idandsalary
fortheemployeeonthedate,2001-12-01oronthedate,2003-12-01,itwouldstillreturnonlyone
employeerow.
Fromtheimmediatelyprecedingexample,itisclearthatacapabilityisneededtospecifytheemptable
containsnotworowswiththesameemp_idvalueandoverlappingapplication-timeperiods.Thereis
additionalsyntaxforprimarykey/uniqueconstraintdeclarationstoprovidesuchacapability.This
exampleillustratesthisnewsyntax:
CREATE TABLE emp
(emp_id INTEGER NOT NULL,
name VARCHAR(30),
salary DECIMAL(5,2),
dept_id INTEGER,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD FOR business_time (bus_start, bus_end),
PRIMARY KEY (emp_id, business_time WITHOUT OVERLAPS)
);
ThePRIMARYKEYconstraintintheimmediatelyprecedingdefinitionensuresthetablehasnotworows
withthesameemp_idvaluewithoverlappingapplication-timeperiods.Thereferencetothebusiness_time
inthePRIMARYKEYdeclarationeffectivelymakesthebus_startandbus_endcolumnsaspartofthepri-
marykeywithcheckforoverlappingperiodsratherthanforequalityofperiods.Forthisexample,an
attempttoinsertanewrowwithanemp_idvalueof100andapplication-timeperiodfrom2004-01-01
to2005-01-01willsucceedwhileanattempttoinsertanewrowwithanemp_idvalueof100and
application-timeperiodfrom2003-06-01to2004-06-01willfail.
5.2.3 Extensionstoreferentialconstraints
Currently,areferentialconstraintbetweenareferencingtableandareferencedtableensures,forevery
rowRinthereferencingtable,valuesintheforeignkeycolumnsofRmatchwiththevaluesofthepri-
mary/uniquekeycolumnsofexactlyonerowinthereferencedtable.However,thepresenceofan
©ISO/IEC2021–Allrightsreserved 15

ISO/IEC19075-2:2021(E)
5.2 Application-timeperiodtables
application-timeperiodinbothreferencingandreferencedtablesprovidesanopportunitytoenhance
thenotionofreferentialconstraintsbetweenthosetables.Forexample,creationofareferentialconstraint
betweentheemptableasdefinedinSubclause5.2.2,“Extensionstoprimarykey/uniqueconstraints”,
andthedepttableshownhereisrequired:
CREATE TABLE dept
(dept_id INTEGER NOT NULL,
name VARCHAR(30),
budget DECIMAL(5,2),
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD FOR business_time (bus_start, bus_end),
PRIMARY KEY (dept_id, business_time WITHOUT OVERLAPS)
);
Assumethatthedepttablehasaprimarykeythatcorrespondstothecombinationofthedept_idcolumn
andthebusiness_timeperiodasshownintheimmediatelyprecedingtabledefinition.Assumefurther
thatcreationofareferentialconstraintisrequiredbetweentheemptableandthedepttablewiththe
emptable’sforeignkeythatcorrespondstothecombinationofthedept_idcolumnandthebusiness_time
period,suchthatforagivenrowRinemptable,foreverypointintimeTinR’sapplication-timeperiod
(i.e.,,setofvaluesfrombus_startvaluethroughtobutnotincludingbus_endvalue),thereisexactlyone
matchingrowinthedepttablewhoseapplication-timeperiodcontainsT.
AssumethecontentsofdeptandemptablesareasshowninTable11,“Exampledatatabledeptforforeign
keywithapplication-timeperiod”,andTable12,“Exampledatatableempforforeignkeywithapplication-
timeperiod”,respectively:
Table11—Exampledatatabledeptforforeignkeywithapplication-timeperiod
dept_id name budget bus_start bus_end
1 Server 30000 2000-03-01 2002-01-01
1 Server 35000 2002-01-01 2003-01-01
2 Tools 40000 2003-01-01 2004-01-01
Table12—Exampledatatableempforforeignkeywithapplication-timeperiod
emp_id name salary dept_id bus_start bus_end
100 Tom 3000 1 2001-07-27 2002-07-27
100 Tom 3500 1 2002-07-27 2003-01-01
100 Tom 4000 2 2003-01-01 2003-06-01
Examiningthecontentofeachtable,itisclearthatthedept_idvalueofbothrow1androw2ofemptable
(i.e.,,1)matcheswiththedept_idvalueofbothrow1androw2ofdepttableandthedept_idvalueof
row3ofemptable(i.e.,,2)matcheswiththedept_idvalueofexactlyonerow,row3,ofdepttable.Itis
alsoclearthattheapplication-timeperiodofrow1ofemptableisnotcompletelycontainedinthe
application-timeperiodofeitherrow1orrow2ofdepttablesinceeventhoughthebus_startvalueof
row1ofemptableoccursafterthebus_startvalueofrow1ofdepttable,thebus_endvalueofrow1of
emptableoccursafterthebus_endvalueofrow1ofdepttableandeventhoughthebus_endvalueof
row1ofemptableoccursbeforethebus_endvalueofrow2ofdepttable,thebus_startvalueofrow1
16 ©ISO/IEC2021–Allrightsreserved

ISO/IEC19075-2:2021(E)
5.2 Application-timeperiodtables
ofemptableoccursbeforethebus_startvalueofrow2ofdepttable.Forrow2ofemptable,itisevident
thatitsapplication-timeperiodofiscompletelycontainedintheapplication-timeperiodofrow2ofdept
table.Similarly,itisapparentthattheapplication-timeperiodofrow3ofemptableiscompletelycontained
intheapplication-timeperiodofrow3ofdepttable.
Thefactthatthedept_idvalueofagivenrow(sayrow1ofemptable)matcheswiththedept_idvalueof
morethanonerowofdepttablemayseemlikeapotentialviolationofreferentialintegritybetweenemp
tableandthedepttable.However,thatisreallynotthecase.Thisisbecauseforeverydatevalueinthe
application-timeperiodofrow1ofemptable,i.e.,,foreverydatevaluestartingfrom2001-07-27through
butnotincluding2002-07-27,thedept_idvalueofrow1ofemptablematcheswiththedept_idvalueof
exactlyonerow(eitherrow1orrow2)ofdepttable.Forinstance,foraspecificdatesuchas2001-08-
30,thedept_idvalueofrow1ofemptablematcheswiththedept_idvalueofrow1ofdepttable.For
anotherdatesuchas2002-03-01,thedept_idvalueofrow1ofemptablematcheswiththedept_idvalue
ofrow2ofdepttable.Thisistrueforeverydatevaluestartingfrom2001-07-27through2002-07-26.
Itisthesamecaseforeverydatevalueintheapplication-timeperiodofrow2ofemptableandforevery
datevalueintheapplication-timeperiodofrow3ofemptable.
Fromtheimmediatelyprecedingexample,itisclearthatcapabilityisneededtospecifyforeverypoint
intimeTintheapplication-timeperiodofagivenrowRinemptable,thereisexactlyonematchingrow
inthedepttablewhoseapplication-timeperiodcontainsT.Thereisadditionalsyntaxforreferential
constraintdeclarationstoprovidesuchacapability.Thisexampleillustratesthisnewsyntax:
CREATE TABLE emp
(emp_id INTEGER NOT NULL,
name VARCHAR(30),
salary DECIMAL(5,2),
dept_id INTEGER,
bus_start DATE NOT NULL,
bus_end DATE NOT NULL,
PERIOD FOR business_time (bus_start, bus_end),
PRIMARY KEY (emp_id, business_time WITHOUT OVERLAPS),
FOREIGN KEY (dept_id, PERIOD business_time) REFERENCES
dept (dept_id, PERIOD business_time)
);
Intheimmediatelyprecedingexample,thereferencetotheperiodnameintheFOREIGNKEYclauseas
wellasintheREFERENCESclauseisprefixedwiththekeywordPERIODtoindicatethespecialchecking
thatneedstobedonetoenforcereferentialintegrity.Forthisexample,anattempttoinsertanewrow
withanemp_idvalueof100,dept_idvalueof1,andapplication-timeperiodfrom2000-06-01to2001-
01-01intoemptablewillsucceedwhileanattempttoinsertanewrowwithanemp_idvalueof100,
dept_idvalueof1,andapplication-timeperiodfrom2000-01-01to2001-01-01willfail.
Notethatintheimmediatelyprecedingexample,boththereferencingandreferencedtablescontainan
application-timeperiod
...