ISO/IEC TR 19075-2:2015

Information technology — Database languages — SQL Technical Reports — Part 2: SQL Support for Time-Related Information

This Technical Report describes the support in SQL for time-related information. This Technical Report discusses the following features of the SQL language: — Time-related datatypes — Operations on time-related data — Time-related Predicates — Application-time period tables — System-versioned tables — Bitemporal tables

Technologies de l'information — Langages de base de données — SQL rapports techniques — Partie 2: Soutien SQL d'information d'horodatage

General Information

• Status: Withdrawn
• Publication Date: 16-Jun-2015
• Withdrawal Date: 16-Jun-2015
• ICS: 35.060 - Languages used in information technology
• Technical Committee: ISO/IEC JTC 1/SC 32 - Data management and interchange
• Drafting Committee: ISO/IEC JTC 1/SC 32/WG 3 - Database language
• Current Stage: 9599 - Withdrawal of International Standard
• Completion Date: 01-Sep-2021

Relations

• Revised: ISO/IEC 19075-2:2021 - Information technology — Guidance for the use of database language SQL — Part 2: Time-related information
• Effective Date: 23-Apr-2020

Standards Content (Sample)

TECHNICAL ISO/IEC TR
REPORT 19075-2
Second edition
2015-07-01
Information technology — Database
languages — SQL Technical Reports —
Part 2:
SQL Support for Time-Related
Information
Technologies de l’information — Langages de base de données — SQL
rapports techniques —
Partie 2: Soutien SQL d’information d’horodatage
Reference number
ISO/IEC TR 19075-2:2015(E)
©
ISO/IEC 2015

---------------------- Page: 1 ----------------------
ISO/IEC TR 19075-2:2015(E)

COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2015, Published in Switzerland
All rights reserved. Unless otherwise specified, 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
Ch. de Blandonnet 8 • CP 401
CH-1214 Vernier, Geneva, Switzerland
Tel. [phone]
Fax [phone]
[e-mail]
www.iso.org
ii © ISO/IEC 2015 – All rights reserved

---------------------- Page: 2 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
Contents Page
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vi
1 Scope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Normative references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
2.1 ISO and IEC standards. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
3 Time-related datatypes, constructs, operators, and predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1 Datetime types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
3.2 DateTime literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3 Interval types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.4 Interval literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9
3.5 Periods. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
3.6 Operations involving datetimes and intervals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
3.7 Time-related predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
3.7.1 Overlaps Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
3.7.2 Period Predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
4 Time-related Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15
4.1 Application-time period tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
4.1.1 Extensions to primary key / unique constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
4.1.2 Extensions to referential constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
4.1.3 Inserting rows of tables containing an application-time period definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
4.1.4 Updating rows of tables containing an application-time period definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
4.1.5 Updating the table between specific points in time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
4.1.6 Deleting rows from tables containing an application-time period definition. . . . . . . . . . . . . . . . . . . . . . . . . . 23
4.1.7 Deleting rows between specific points in time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
4.1.8 Querying tables containing a period definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
4.1.9 Adding a period definition to a table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
4.2 System-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27
4.2.1 Primary key and referential constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
4.2.2 Updating system-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
4.2.3 Deleting system-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
4.2.4 Querying system-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
4.3 Bitemporal tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
©ISO/IEC 2015 – All rights reserved Contents iii

---------------------- Page: 3 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
Tables
Table Page
1 Fields in datetime values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2 Mapping of Datetime fields to Datetime Datatypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3 Examples of the datetime datatypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4 Examples of datetime literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
5 Fields in year-month INTERVAL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
6 Fields in day-time INTERVAL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
7 Fields in day-time INTERVAL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
8 Examples of the interval literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
9 Valid operators involving datetimes and intervals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
10 Example data table emp for primary key with application-time period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
11 Example data table dept for foreign key with application-time period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
12 Example data table emp for foreign key with application-time period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
13 Content of table emp after insert with application-time period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
14 Content of table emp before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
15 Content of table emp after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
16 Content of table emp with application-time period before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
17 Content of table emp with application-time period after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
18 Content of table emp with application-time period before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
19 Content of table emp with application-time period after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
20 Content of table emp with application-time period before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
21 Content of table emp with application-time period after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
22 Content of table emp with application-time period befor updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
23 Content of table emp with application-time period after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
24 Content of table emp with application-time period before deleteting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
25 Content of table emp with application-time period after deleteting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
26 Content of table emp with application-time period before deleting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
27 Content of table emp with application-time period before deleting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
28 Content of table emp with application-time period after deleting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
29 Content of system-versioned table emp before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
30 Content of system-versioned table emp after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
31 Content of system-versioned table emp before deleting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
32 Content of system-versioned table emp after deleting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
iv  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 4 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical Commission)
form the specialized system for worldwide standardization. National bodies that are members of ISO or IEC
participate in the development of International Standards through technical committees established by the
respective organization to deal with particular fields of technical activity. ISO and IEC technical committees
collaborate in fields of mutual interest. Other international organizations, governmental and non-governmental,
in liaison with ISO and IEC, also take part in the work. In the field of information technology, ISO and IEC
have established a joint technical committee, ISO/IEC JTC 1.
International Standards are drafted in accordance with the rules given in the ISO/IEC Directives, Part 2.
The main task of the joint technical committee is to prepare International Standards. Draft International Standards
adopted by the joint technical committee are circulated to national bodies for voting. Publication as an Interna-
tional Standard requires approval by at least 75 % of the national bodies casting a vote.
In exceptional circumstances, when the joint technical committee has collected data of a different kind from
that which is normally published as an International Standard (“state of the art”, for example), it may decide
to publish a Technical Report. A Technical Report is entirely informative in nature and shall be subject to
review every five years in the same manner as an International Standard.
Attention is drawn to the possibility that some of the elements of this document may be the subject of patent
rights. ISO and IEC shall not be held responsible for identifying any or all such patent rights.
ISO/IEC TR 19075-2 was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
ISO/IEC TR 19075 consists of the following parts, under the general title Information technology — Database
languages — SQL Technical Reports:
— Part 1: XQuery Regular Expression Support in SQL
— Part 2: SQL Support for Time-Related Information
— Part 3: SQL Embedded in Programs Using the Java™ Programming Language
— Part 4: SQL With Routines and Types Using the Java™ Programming Language
— Part 5: Row Pattern Recognition in SQL
NOTE 1 — The individual parts of multi-part technical report are not necessarily published together. New editions of one or more
parts may be published without publication of new editions of other parts.
©ISO/IEC 2015 – All rights reserved Foreword v

---------------------- Page: 5 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
Introduction
The organization of this part of ISO/IEC 19075 is as follows:
1) Clause 1, “Scope”, specifies the scope of this part of ISO/IEC 19075.
2) Clause 2, “Normative references”, identifies additional standards that, through reference in this part of
ISO/IEC 19075, constitute provisions of this part of ISO/IEC 19075.
3) Clause 3, “Time-related datatypes, constructs, operators, and predicates”, explains time-related datatypes,
operators, and predicates in SQL.
4) Clause 4, “Time-related Tables”, explains how time-related tables are used.
vi  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 6 ----------------------
ISO/IEC TR 19075-2:2015(E)
TECHNICAL REPORT ISO/IEC TR 19075-2:2015
Information technology — Database languages — SQL Technical Reports —
Part 2:
SQL Support for Time-Related Information
1 Scope
This Technical Report describes the support in SQL for time-related information.
This Technical Report discusses the following features of the SQL language:
— Time-related datatypes
— Operations on time-related data
— Time-related Predicates
— Application-time period tables
— System-versioned tables
— Bitemporal tables
©ISO/IEC 2015 – All rights reserved Scope 1

---------------------- Page: 7 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
(Blank page)
2  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 8 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
2.1 ISO and IEC standards
2 Normative references
The following referenced documents are indispensable for the application of this document. For dated references,
only the edition cited applies. For undated references, the latest edition of the referenced document (including
any amendments) applies.
2.1 ISO and IEC standards
[ISO9075-2] ISO/IEC 9075-2:2011, Information technology — Database languages — SQL — Part 2:
Foundation (SQL/Foundation).
©ISO/IEC 2015 – All rights reserved Normative references 3

---------------------- Page: 9 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
(Blank page)
4  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 10 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
3.1 Datetime types
3 Time-related datatypes, constructs, operators, and predicates
3.1 Datetime types
There are three datetime types, each of which is made up of different datetime fields.
A value of data type TIMESTAMP is made up of the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE,
and SECOND. It is always a valid time at a valid Gregorian date.
A value of data type TIME comprises values of the datetime fields HOUR, MINUTE and SECOND. It is always
a valid time of day.
A value of data type DATE is made up of the datetime fields YEAR, MONTH, and DAY. It is always a valid
Gregorian date.
TIMESTAMP and TIME may be specified with a number of (decimal) digits of fractional seconds precision.
TIMESTAMP and TIME may also be specified as being WITH TIME ZONE, in which case every value has
associated with it a time zone displacement. In comparing values of a data type WITH TIME ZONE, the value
of the time zone displacement is disregarded.
Table 1, “Fields in datetime values”, specifies the fields that can make up a datetime value.
Table 1 — Fields in datetime values
Keyword Meaning
YEAR Year, between 0001 and 9999
MONTH Month within year, between 01 and 12
DAY Day within month, between 1 and 31, but further constrained by the value of
MONTH and YEAR fields, according to the rules for well-formed dates in the
Gregorian calendar.
HOUR Hour within day, between 00 and 23
MINUTE Minute within hour, between 00 and 59
SECOND Second and possibly fraction of a second within minute, between 00 and
61.999.
TIMEZONE_HOUR Hour value of time zone displacement, between –14 and 14. The range for time
zone intervals is larger than many readers might expect because it is governed
by political decisions in governmental bodies rather than by any natural law.
©ISO/IEC 2015 – All rights reserved Time-related datatypes, constructs, operators, and predicates 5

---------------------- Page: 11 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
3.1 Datetime types
Keyword Meaning
TIMEZONE_MINUTE Minute value of time zone displacement, between –59 and 59. When the value
of TIMEZONE_HOUR is either –14 or 14, the value of TIMEZONE_MINUTE
is restricted to be 00 (zeros).
There is an ordering of the significance of these fields. This is, from most significant to least significant: YEAR,
MONTH, DAY, HOUR, MINUTE, and SECOND.
Table 2 — Mapping of Datetime fields to Datetime Datatypes
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
WITH TZ
TIME N N N Y Y Y N N
TIME WITH N N N Y Y Y Y Y
TZ
DATE Y Y Y N N N N N
The surface of the earth is divided into zones, called time zones, in which every correct clock tells the same
time, known as local time. Local time is equal to UTC (Coordinated Universal Time) plus the time zone dis-
placement, which is an interval value that ranges between INTERVAL '–14:00' HOUR TO MINUTE and
INTERVAL '+14:00' HOUR TO MINUTE. The time zone displacement is constant throughout a time zone,
changing at the beginning and end of Summer Time, where applicable.
A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE,
may represent a local time, whereas a datetime value of data type TIME WITH TIME ZONE or TIMESTAMP
WITH TIME ZONE represents UTC.
Table 3 — Examples of the datetime datatypes
Datatype Explanation
TIMESTAMP (2) This a timestamp with a fractional precision of 2 for the seconds field
TIMESTAMP This is a timestamp with no fractional precision for the seconds field
TIME (2) This is a time with a fractional precision of 2 for the seconds field
TIME This is a time with no fractional precision for the seconds field
DATE This is a date
6  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 12 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
3.1 Datetime types
On occasion, UTC is adjusted by the omission of a second or the insertion of a “leap second” in order to
maintain synchronization with sidereal time. This implies that sometimes, but very rarely, a particular minute
will contain exactly 59, 61, or 62 seconds. Interval arithmetic that involves leap seconds or discontinuities in
calendars will produce implementation-defined results.
For the convenience of users, whenever a datetime value with time zone is to be implicitly derived from one
without (for example, in a simple assignment operation), SQL assumes the value without time zone to be local,
subtracts the current default time zone displacement of the SQL-session from it to give UTC, and associates
that time zone displacement with the result.
Conversely, whenever a datetime value without time zone is to be implicitly derived from one with, SQL
assumes the value with time zone to be UTC, adds the time zone displacement to it to give local time, and the
result, without any time zone displacement, is local.
Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001–01–01 CE
through 9999–12–31 CE.
3.2 DateTime literals
A datetime literal can specify datetime values of the respective datetime datatypes. An datetime literal consists
of three parts. The keyword for the datatype, the value in a fixed format and the timezone displacement. The
format for the datetime literal is yyyy–mm–dd hh24:mi:ss.ssss. The datatype is automatically assignd to the
literals depending on their content and the keyword used.
Table 4 — Examples of datetime literals
Literal Datatype Explanation
TIMESTAMP '2014–06–11 TIMESTAMP (2) This is a timestamp for the 11th of June 2014 at 9
09:15:22.03' hours, 15 minutes and 22.03 seconds
TIME'12:00:01+01:00' TIME(0) WITH One second after noon in the timezone with a dis-
TIMEZONE placement of + 1 hour
DATE '0001–01–01' DATE The first of January in year 1. This is the first pos-
sible date in SQL
3.3 Interval types
A value of an interval type represents the duration of a period of time. There are two classes of intervals. One
class, called year-month intervals, has an interval precision that includes a YEAR field or a MONTH field, or
both. The other class, called day-time intervals, has an express or implied interval precision that can include
any set of contiguous fields other than YEAR or MONTH.
Table 5, “Fields in year-month INTERVAL values”, specifies the fields that make up a year–month interval.
©ISO/IEC 2015 – All rights reserved Time-related datatypes, constructs, operators, and predicates 7

---------------------- Page: 13 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
3.3 Interval types
Table 5 — Fields in year-month INTERVAL values
Keyword Meaning Valid values of INTERVAL fields
YEAR Years Unconstrained except by the leading field preci-
sion
MONTH Months Months (within years) (0-11)
Table 6, “Fields in day-time INTERVAL values”, specifies the fields that make up a day-time interval. A day-
time interval is made up of a contiguous subset of those fields.
Table 6 — Fields in day-time INTERVAL values
Keyword Meaning Valid values of INTERVAL fields
DAY Days Unconstrained except by the leading field precision
HOUR Hours Hours (within days) (0-23)
MINUTE Minutes Minutes (within hours) (0-59)
SECOND Seconds and possibly Seconds (within minutes) (0-59.999.)
fractions of a second
The actual subset of fields that comprise a value of either type of interval is called the precision of the value.
Within a value of type interval, the first field is constrained only by the precision of the leading field.
Values in interval fields other than SECOND are integers. SECOND, can be defined to have a precision of
fractional seconds that indicates the number of decimal digits maintained following the decimal point in the
seconds value.
Table 7 — Fields in day-time INTERVAL values
Datatype Explanation
INTERVAL YEAR TO MONTH This is a year-month interval which is made up of the fields year
and month
INTERVAL HOUR TO SECOND (2) This is a day-time interval whis made up of the fields hour,
minute and second with a fractional precision of 2. The day value
is always 0 (zero)
INTERVAL DAY This is a day-time interval whis made up of just the field day.
All other fields are 0 (zero)
INTERVAL MONTH This is a year-month interval which is made up of just the field
month. The year has always the value 0 (zero)
8  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

---------------------- Page: 14 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
3.3 Interval types
Datatype Explanation
INTERVAL SECOND (4) This is a day-time interval whis made up of just the field second
with a fractional precision of 4. All other fields are 0 (zero)
Year-month intervals are comparable only with other year-month intervals. If two year-month intervals have
different interval precisions, they are, for the purpose of any operations between them, converted to the same
precision by appending new datetime fields to either one of the ends of one interval, or to both ends. New
datetime fields are assigned a value of 0 (zero).
Day-time intervals are comparable only with other day-time intervals. If two day-time intervals have different
interval precisions, they are, for the purpose of any operations between them, converted to the same precision
by appending new datetime field to either one of the ends of one interval, or to both ends. New datetime fields
are assigned a value of 0 (zero).
3.4 Interval literals
An interval literal can specify interval values of the respective interval datatypes.An interval literal consists of
three parts. The keyword INTERVAL, the datetime interval in a fixed format and the interval qualifier. The
format for the datetime literal is yyyy-mm-dd hh24:mi:ss.ssss. An interval literal can be positive or negative.
The datatype is automatically assignd to the literal depending on the keywords used for the interval qualifier.
Table 8 — Examples of the interval literals
Literal Datatype Explanation
INTERVAL '1' MONTH INTERVAL MONTH One month
INTERVAL '01 10' DAY TO INTERVAL DAY TO HOUR One day and one hour
HOUR
INTERVAL '10:10:10.1' INTERVAL HOUR TO SEC- Ten hours, ten minutes and 10.1 sec-
HOUR TO SECOND(1) OND (1) onds
INTERVAL '-10' MINUTE INTERVAL MINUTE Minus 10 minutes.
3.5 Periods
A period is an object associated with a single base table. A period definition for a given table associates a period
name with a pair of column names defined for that table. The columns must be both of a datetime data type
and known not nullable. Further, the declared types of both columns need to be identical.
Similar to column definitions and constraint definitions, a period definition can only be specified as part of a
table definition. For a table with a period definition, every row in that table is considered to be associated with
©ISO/IEC 2015 – All rights reserved Time-related datatypes, constructs, operators, and predicates 9

---------------------- Page: 15 ----------------------
ISO/IEC TR 19075-2:2015(E)
DTR 19075-2:2014(E)
3.5 Periods
a period whose name corresponds to the period name specified in the period definition and whose start and end
times are provided by the column values specified in the period definition.
For example, consider the following table definition:
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)
  );
The period definition "PERIOD FOR business_time (bus_start, bus_end)" in the above table definition defines
a period named business_time for the emp table with the value in the bus_start column for a given row acting
as the start time of the business_time period associated with the row and the value in the bus_end column for
a given row acting as the end time of the business_time period associated with the row.
In general, for a period with name P, the first column in the period definition is called the P period start column,
and the second column is called the P period end column. The columns participating in a period definition must
satisfy the following conditions:
1) Both columns must be declared as NOT NULL.
2) The data type of both columns must be a datetime data type.
3) The data types of both columns must be identical.
For any given period, the SQL-implementation ensures the value of the period end column is always greater
than the value of the period start column. In general, the period is a set of datetime values consisting of every
distinct value in the timeline starting from the period start value up to but not including the period end value.
A given table can have at most two period definitions. One of the periods is reserved for supporting system-
time dimension, i.e., system-versioned tables with a predefined period name of "SYSTEM_TIME" for such a
period. This leaves one period for supporting the application-time dimension with a user-defined name for such
a period.
3.6 Operations involving datetimes and intervals
Table 9, “Valid operators involving datetimes and intervals”, specifies the declared types of arithmetic
expressions involving datetime and interval operands.
Table 9 — Valid operators involving datetimes and int
...