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
Current Stage
6060 - International Standard published
Start Date
31-Aug-2021
Due Date
11-Feb-2024
Completion Date
01-Sep-2021
Ref Project

Relations

Buy Standard

Standard
ISO/IEC 19075-2:2021 - Information technology -- Guidance for the use of database language SQL
English language
34 pages
sale 15% off
Preview
sale 15% off
Preview
Draft
ISO/IEC PRF 19075-2:Version 10-jul-2021 - Information technology -- Guidance for the use of database language SQL
English language
34 pages
sale 15% off
Preview
sale 15% off
Preview

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 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: +41 22 749 01 11
Email: copyright@iso.org
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-27usingexisting
...

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: +41 22 749 01 11
Email: copyright@iso.org
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
...

Questions, Comments and Discussion

Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.