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
• Start Date: 01-Sep-2021
• Completion Date: 30-Oct-2025

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 2015
© 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 intervals
Operand 1 Operator Operand 2 Result Type
Datetime – Datetime Interval
Datetime + or – Interval Datetime
10  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

DTR 19075-2:2014(E)
3.6 Operations involving datetimes and intervals
Operand 1 Operator Operand 2 Result Type
Interval + Datetime Datetime
Interval + or – Interval Interval
Interval * or / Numeric Interval
Numeric * Interval Interval
Arithmetic operations involving values of type datetime or interval obey the natural rules associated with dates
and times and yield valid datetime or interval results according to the Gregorian calendar.
Operations involving values of type datetime require that the datetime values be comparable. Operations
involving values of type interval require that the interval values be comparable.
Operations involving a datetime and an interval preserve the time zone of the datetime operand. If the datetime
operand does not include a time zone displacement, then the result has no time zone displacement.
An extract expression operates on a datetime or interval and returns an exact numeric value representing the
value of one component of the datetime or interval.
An interval absolute value function operates on an interval argument and returns its absolute value in the same
most specific type.
3.7 Time-related predicates
3.7.1 Overlaps Predicate
An overlaps predicate uses the operator OVERLAPS to determine whether or not two chronological periods
overlap in time. A chronological period is specified either as a pair of datetimes (starting and ending) or as a
starting datetime and an interval. If the length of the period is greater than 0 (zero), then the period consists of
all points of time greater than or equal to the lower endpoint, and less than the upper endpoint. If the length of
the period is equal to 0 (zero), then the period consists of a single point in time, the lower endpoint. Two
chronological periods overlap if they have at least one point in common.
3.7.2 Period Predicates
There are seven period predicates available, as shown below:
These predicates take two operands separated by keywords such as CONTAINS, OVERLAPS, etc. For predicates
other than the period contains predicate, each of the operands can be either a period name or the syntactic
construct of the form PERIOD (datetime value expression, datetime value expression), called a period constructor.
For the period contains predicate, the first operand can be either a period name or a period constructor while
the second operand can be either a period name, a period constructor, or a datetime value expression.
©ISO/IEC 2015 – All rights reserved Time-related datatypes, constructs, operators, and predicates 11

DTR 19075-2:2014(E)
3.7 Time-related predicates
Note that the predicates can be used wherever predicate syntax is allowed, e.g., in the WHERE clause, on the
ON clause of a joined table, etc.
A description of each of the predicates is provided below (assume x is the first operand and y is the second
operand; further assume that when x and y stand for periods, they are modelled as (closed, open) periods with
xs and xe as the start and end times of period x and ys and ye as the start and end times of period y):
For the examples 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)
);
1) The predicate “x OVERLAPS y” applies when both x and y are either period names or period constructors.
This predicate returns True if the two periods have at least one time point in common, i.e, if xs < ye and
xe > ys.
A query to retrieve all emp rows whose application-time periods overlap a given period, say a period with
the start date of 2001-01-01 and the end date of 2001-07-07 using existing SQL syntax looks as follows:
SELECT *
FROM emp e
WHERE e.bus_start < DATE '2001-07-27' AND e.bus_end > DATE '2001-01-01'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp e
WHERE e.business_time OVERLAPS PERIOD (DATE '2001-01-01', DATE '2001-07-27')
2) The predicate “x EQUALS y” applies when both x and y are either period names or period constructors.
This predicate returns True if the two periods have every time point in common, i.e., if xs = ys and xe =
ye
A query to retrieve all emp rows whose application-time periods is equal to a given period, say a period
with the start date of 2001-01-01 and the end date of 2001-07-07 using existing SQL syntax looks as follows:
SELECT *
FROM emp e
WHERE e.bus_start = DATE '2001-01-01' AND e.bus_end = DATE '2001-07-27'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp e
WHERE e.business_time EQUALS PERIOD (DATE '2001-01-01', DATE '2001-07-27')
12  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

DTR 19075-2:2014(E)
3.7 Time-related predicates
3) The predicate “x CONTAINS y” applies when
a) both x and y are either period names or period constructors. In this case, the predicate returns True if
x contains every time point in y, i.e., if xs ≤ ys and xe ≥ ye.
b) x is either a period name or a period constructor and y is a datetime value expression. In this case, the
predicate returns True if x contains y, i.e., if xs ≤ y and xe > y.
A query to retrieve all emp rows whose application-time periods contain the date 2001-01-01 using existing
SQL syntax looks as follows:
SELECT *
FROM emp
WHERE bus_start ≤ DATE '2001-01-01' AND bus_end > DATE '2001-01-01'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp
WHERE business_time CONTAINS DATE '2001-01-01'
4) The predicate “x PRECEDES y” applies when both x and y are either period names or period constructors.
In this case, the predicate returns True if the end value of x is less than or equal to the start value of y, i.e.,
if xe ≤ ys.
A query to retrieve all emp rows whose application-time periods precedes a given period, say a period
with the start date of 2001-01-01 and the end date of 2001-07-07 using existing SQL syntax looks as follows:
SELECT *
FROM emp e
WHERE e.bus_end ≤ DATE '2001-01-01'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp e
WHERE e.business_time PRECEDES PERIOD (DATE '2001-01-01', DATE '2001-07-27')
5) The predicate “x SUCCEEDS y” applies when both x and y are either period names or period constructors.
In this case, the predicate returns True if the start value of x is greater than or equal to the end value of y,
i.e., if xs ≥ ye.
A query to retrieve all emp rows whose application-time periods succeeds a given period, say a period
with the start date of 2001-01-01 and the end date of 2001-07-07 using existing SQL syntax looks as follows:
SELECT *
©ISO/IEC 2015 – All rights reserved Time-related datatypes, constructs, operators, and predicates 13

DTR 19075-2:2014(E)
3.7 Time-related predicates
FROM emp e
WHERE e.bus_start ≥ DATE '2001-07-07'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp e
WHERE e.business_time SUCCEEDS PERIOD (DATE '2001-01-01', DATE '2001-07-27')
6) The predicate “x IMMEDIATELY PRECEDES y” applies when both x and y are either period names or
period constructors. In this case, the predicate returns True if the end value of x is equal to the start value
of y, i.e., if xe = ys.
A query to retrieve all emp rows whose application-time periods immediately precedes a given period, say
a period with the start date of 2001-01-01 and the end date of 2001-07-07 using existing SQL syntax looks
as follows:
SELECT *
FROM emp e
WHERE e.bus_end = DATE '2001-01-01'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp e
WHERE e.business_time IMMEDIATELY PRECEDES
PERIOD (DATE '2001-01-01', DATE '2001-07-27')
7) The predicate “x IMMEDIATELY SUCCEEDS y” applies when both x and y are either period names or
period constructors. In this case, the predicate returns True if the start value of x is equal to the end value
of y, i.e., if xs = ye.
A query to retrieve all emp rows whose application-time periods immediately succeedes a given period,
say a period with the start date of 2001-01-01 and the end date of 2001-07-07 using existing SQL syntax
looks as follows:
SELECT *
FROM emp e
WHERE e.bus_start = DATE '2001-07-07'
The above query can be written in a more succinct and more intuitive way when the predicates in the
WHERE clause are expressed using the name of the period rather than the start and end columns of the
period, as shown below:
SELECT *
FROM emp e
WHERE e.business_time IMMEDIATELY SUCCEEDS
PERIOD (DATE '2001-01-01', DATE '2001-07-27')
14  SQL Support for Time-Related Information ©ISO/IEC 2015 – All rights reserved

DTR 19075-2:2014(E)
4.1 Application-time period tables
4 Time-related Tables
There are three different flavors of time-related tables. The first type has just an application-time period, the
second has just the system versioning period, whereas the third type, called a bitemporal table, has both types
of periods.
4.1 Application-time period tables
Application-time period tables are intended for meeting the requirements of applications that are interested in
capturing time periods during which the data is believed to be valid in the real world. A typical example of
such applications is an insurance application, where it is necessary to keep track of the specific policy details
of a given customer that are in effect at any given point in time.
A primary requirement of such applications is that the user be put in charge of setting the start and end times
of the validity period of rows, and the user be free to assign any time values, either in the past, current or in the
future, for the start and end times. Another requirement of such applications is that the user be permitted to
update the validity periods of the rows as errors are discovered or new information is made available.
Any table that contains a period definition with a user-defined name is an application-time period table.
Users can pick any name they want for the name of the period as well as for the names of columns that act as
the start and end columns of the period. The data types of the period start and end columns must be either DATE
or a timestamp type, and data types of both columns must be the same.
4.1.1 Extensions to primary key / unique constraints
Users can define primary key/unique constraints on tables containing an application-time period using the
current syntax with exactly the same behavior. However, the presence of an application-time period provides
an opportunity to enhance the notion of primary key/unique constraints on that table. For example, assume that
we are interested in creating a primary key for the emp table that corresponds to the combination of the emp_id,
bus_start, and bus_end columns, such that for a given emp_id value and a given point in time T, there is exactly
one row whose application-time period (i.e., set of values from bus_start value through to but not including
bus_end value) contains T. This means that for any selection based on a specified emp_id value and a specified
date, one and only one row is retrieved. Assume the emp table contains the following rows:
Table 10 — Example data table emp for primary key with application-time period
emp_id name salary dept_id bus_start bus_end
100 Tom 3000 1 2001-07-27 2002-01-01
100 Tom 3500 10 2002-01-01 2003-01-01
©ISO/IEC 2015 – All rights reserved Time-related Tables 15

DTR 19075-2:2014(E)
4.1 Application-time period tables
emp_id name salary dept_id bus_start bus_end
100 Tom 4000 20 2003-01-01 2004-01-01
In this example there are three rows. Note first, the application-time periods across all three rows are not
overlapping. Note second, the end of the time period from row 1 is the start time from row 2, and the endtime
for row 2 is the start time of row 3. Note third that during each of these time periods, the employee is assigned
to a different department and had a different salary. Note fourth that even though there are multiple rows for
the same employee spanning a period from 2001-07-27 through 2004-01-01, there is only one emp row whose
application-time period contains any specified date in the period from 2001- 07-27 through 2004-01-01. For
example, a specified date of 2002-12-01 would result in the selection of row 2. Suppose however, that row 2
is deleted. This would cause a gap in the period from 2001-07-27 through 2004-01-01. Thus, if there was a
query to produce the dept_id and salary for the employee on the date, 2002-12-01, the query would fail because
there is no row whose application-time period contains the date 2002-12-01. However, if there was a query to
produce the dept_id and salary for the employee on the date, 2001-12-01 or on the date, 2003-12-01, it would
still return only one employee row.
From the above example, it is clear that we need a capability to specify the emp table contains no two rows
with the same emp_id value and overlapping application-time periods. There is additional syntax for primary
key/unique constraint declarations to provide such a capability. The following example illustrates this new
syntax:
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
...