ISO/IEC PRF 19075-2

Information technology -- Guidance for the use of database language SQL

Titre manque

General Information

• Status: Published
• 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

RELATIONS

• Revised: ISO/IEC TR 19075-2:2015 - Information technology -- Database languages -- SQL Technical Reports
• Effective Date: 23-Apr-2020

Standards Content (sample)

INTERNATIONAL ISO/IEC
STANDARD 19075-2
First edition
Information technology — Guidance
for the use of database language
SQL —
Part 2:
Time-related information
PROOF/ÉPREUVE
Reference number
ISO/IEC 19075-2:2021(E)
ISO/IEC 2021
---------------------- Page: 1 ----------------------
ISO/IEC 19075-2:2021(E)
COPYRIGHT PROTECTED DOCUMENT
© 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
---------------------- Page: 2 ----------------------
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
---------------------- Page: 3 ----------------------
ISO/IEC19075-2:2021(E)

Index........................................................................................33

iv ©ISO/IEC2021–Allrightsreserved
---------------------- Page: 4 ----------------------
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
---------------------- Page: 5 ----------------------
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
---------------------- Page: 6 ----------------------
ISO/IEC19075-2:2021(E)
AlistofallpartsintheISO/IEC19075seriescanbefoundontheISOandIECwebsites.

Anyfeedbackorquestionsonthisdocumentshouldbedirectedtotheuser’snationalstandardsbody.A

completelistingofthesebodiescanbefoundatwww.iso.org/members.htmlandwww.iec.ch/-
national-committees.
©ISO/IEC2021–Allrightsreserved vii
---------------------- Page: 7 ----------------------
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
---------------------- Page: 8 ----------------------
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
---------------------- Page: 9 ----------------------
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
---------------------- Page: 10 ----------------------
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
---------------------- Page: 11 ----------------------
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
---------------------- Page: 12 ----------------------
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
---------------------- Page: 13 ----------------------
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
---------------------- Page: 14 ----------------------
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
---------------------- Page: 15 ----------------------
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
---------------------- Page: 16 ----------------------
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
---------------------- Page: 17 ----------------------
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
---------------------- Page: 18 ----------------------
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
---------------------- Page: 19 ----------------------
ISO/IEC19075-2:2
...