ISO/IEC 19075-2:2021
(Main)Information technology — Guidance for the use of database language SQL — Part 2: Time-related information
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
Relations
Buy Standard
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: +41 22 749 01 11
Email: copyright@iso.org
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)
...
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: +41 22 749 01 11
Email: copyright@iso.org
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)
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.