BS PD ISO IEC TR 19075-2-2015 Information technology Database languages SQL Technical Reports SQL Support for Time-Related Information《信息技术 数据库语言 SQL技术报告 时间相关信息的SQL支持》.pdf

1、BSI Standards Publication PD ISO/IEC TR 19075-2:2015 Information technology Database languages SQL Technical Reports Part 2: SQL Support for Time-Related InformationPD ISO/IEC TR 19075-2:2015 PUBLISHED DOCUMENT National foreword This Published Document is the UK implementation of ISO/IEC TR 19075-2:

2、2015. The UK participation in its preparation was entrusted to Technical Committee IST/40, Data management and interchange. A list of organizations represented on this committee can be obtained on request to its secretary. This publication does not purport to include all the necessary provisions of

3、a contract. Users are responsible for its correct application. The British Standards Institution 2015. Published by BSI Standards Limited 2015 ISBN 978 0 580 85086 8 ICS 35.060 Compliance with a British Standard cannot confer immunity from legal obligations. This Published Document was published und

4、er the authority of the Standards Policy and Strategy Committee on 31 July 2015. Amendments/corrigenda issued since publication Date T e x t a f f e c t e dInformation technology Database languages SQL Technical Reports Part 2: SQL Support for Time-Related Information Technologies de linformation La

5、ngages de base de donnes SQL rapports techniques Partie 2: Soutien SQL dinformation dhorodatage TECHNICAL REPORT ISO/IEC TR 19075-2 Second edition 2015-07-01 Reference number ISO/IEC TR 19075-2:2015(E) ISO/IEC 2015 PD ISO/IEC TR 19075-2:2015ii ISO/IEC 2015 All rights reserved COPYRIGHT PROTECTED DOC

6、UMENT 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 wr

7、itten permission. Permission can be requested from either ISO at the address below or ISOs member body in the country of the requester. ISO copyright office Ch. de Blandonnet 8 CP 401 CH-1214 Vernier, Geneva, Switzerland Tel. +41 22 749 01 11 Fax +41 22 749 09 47 copyrightiso.org www.iso.org ISO/IEC

8、 TR 19075-2:2015(E)PD ISO/IEC TR 19075-2:2015 Contents Page Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v Introduction. . . . . . . . . . . . . . . . . . . . . . .

9、. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vi 1 Scope. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

10、2 Normative references. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 2.1 ISO and IEC standards. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

11、. . . . . . . . . . . . 3 3 Time-related datatypes, constructs, operators, and predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 3.1 Datetime types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

12、 . . . . . . . . . . .5 3.2 DateTime literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.3 Interval types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

13、 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 3.4 Interval literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9 3.5 Periods. . . . . . . . . . . . . . . . . . . . . . . . . . .

14、. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 3.6 Operations involving datetimes and intervals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 3.7 Time-related predicates. . . . . . .

15、. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 3.7.1 Overlaps Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 3.7.2 Perio

16、d Predicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 4 Time-related Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

17、. . . . . . . . . .15 4.1 Application-time period tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 4.1.1 Extensions to primary key / unique constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

18、 . . . . . . . . . . . . . . 15 4.1.2 Extensions to referential constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 4.1.3 Inserting rows of tables containing an application-time period definition. . . . . . . . . . . . . . .

19、 . . . . . . . . . . . . . 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、 . . . . . . 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

21、23 4.1.8 Querying tables containing a period definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 4.1.9 Adding a period definition to a table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

22、 . . . . . 26 4.2 System-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .27 4.2.1 Primary key and referential constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

23、. . . . . . . . . . . . . . 29 4.2.2 Updating system-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 4.2.3 Deleting system-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

24、. . . . . . . . . . . . . . . . . . . . . . 30 4.2.4 Querying system-versioned tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 4.3 Bitemporal tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

25、 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 ISO/IEC 2014 All rights reserved Contents iii D

26、TR 19075-2:2014(E) PD ISO/IEC TR 19075-2:2015 ISO/IEC TR 19075-2:2015(E)PD ISO/IEC TR 19075-2:2015 Tables Table Page 1 Fields in datetime values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2 Mapping of D

27、atetime fields to Datetime Datatypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 3 Examples of the datetime datatypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

28、4 Examples of datetime literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 5 Fields in year-month INTERVAL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

29、 . . . . . . . . . . 8 6 Fields in day-time INTERVAL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 7 Fields in day-time INTERVAL values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

30、 . . . . . . . . . . . . . . . . . . 8 8 Examples of the interval literals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 9 Valid operators involving datetimes and intervals. . . . . . . . . . . . . . . . . . . . . .

31、 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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. . . . . . . . . . . . . .

32、. . . . . . . . . . . . . . . . . . . . 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. . . . . . . . . . . . . . . . . . . . . . . . . .

33、 . . . . . . . . . . . . . . 19 14 Content of table emp before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 15 Content of table emp after updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

34、. . . . . . . . . . . . . . . . . . . . . . 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. . . . . . . . . . . . . . . . . . . . .

35、 . . . . . . . . . . . 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. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

36、. 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 ta

37、ble 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 applica

38、tion-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 befo

39、re 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. . .

40、. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 29 Content of system-versioned table emp before updating a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 30 Content of system-versioned table emp after updating a row. . . . . . . . . . . . . . . .

41、 . . . . . . . . . . . . . . . . . . . . . . . . . 30 31 Content of system-versioned table emp before deleting a row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 32 Content of system-versioned table emp after deleting a row. . . . . . . . . . . . . . . . . . . .

42、. . . . . . . . . . . . . . . . . . . . . . 30 iv SQL Support for Time-Related Information ISO/IEC 2014 All rights reserved DTR 19075-2:2014(E) PD ISO/IEC TR 19075-2:2015 ISO/IEC TR 19075-2:2015(E)PD ISO/IEC TR 19075-2:2015 Foreword ISO (the International Organization for Standardization) and IEC (t

43、he 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 parti

44、cular 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 estab

45、lished 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 techni

46、cal 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

47、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

48、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

49、 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 techni