Browse Prior Art Database

Processing Date-Time Portions of Relational Data Base Queries

IP.com Disclosure Number: IPCOM000039164D
Original Publication Date: 1987-Apr-01
Included in the Prior Art Database: 2005-Feb-01
Document File: 4 page(s) / 23K

Publishing Venue

IBM

Related People

Cheng, JM: AUTHOR [+5]

Abstract

This invention relates to a method for processing coded date-time string portions of a relational data base query. The method comprises the steps of (a) syntactically denoting the relative or absolute magnitude attributes of date-time strings, (b) ONTO mapping date-time strings into counterpart monotonically increasing and recursively decomposable finite number strings, (c) modifying said number strings by performing selected binary operations thereon, and (d) ONE-to-ONE mapping of said modified strings into date-time strings according to their syntactically denoted attributes.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 37% of the total text.

Page 1 of 4

Processing Date-Time Portions of Relational Data Base Queries

This invention relates to a method for processing coded date-time string portions of a relational data base query. The method comprises the steps of (a) syntactically denoting the relative or absolute magnitude attributes of date-time strings, (b) ONTO mapping date-time strings into counterpart monotonically increasing and recursively decomposable finite number strings, (c) modifying said number strings by performing selected binary operations thereon, and (d) ONE-to-ONE mapping of said modified strings into date-time strings according to their syntactically denoted attributes. The syntactical definition of data type for the CREATE, ALTER, and DECLARE TABLE statements in SQL for column definitions is extended to include the following options: DATE(USA) - defines a date column with the string format mm/dd/yyyy DATE(EUR) - defines a date column with the string format dd.mm.yyyy DATE(ISO) - defines a date column with the string format yyyy-mm-dd TIME(USA) - defines a time column with the string format hh:mm xM TIME(INT) - defines a time column with the string format hh.mm TIME(ISO) - defines a time column with the string format hh.mm.ss TIME(JIS) - defines a time column with the string format hh:mm TIMESTAMP - defines a timestamp column with the string format yyyy-mm-dd-hh.mm.ss.nnnnnn The syntactical definition of an expression in SQL is extended to include the keyword pairs CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, and CURRENT TIMEZONE.

Thus, any of these keyword pairs can be used whenever an expression can be used such as in a SELECT statement or an UPDATE statement.

The syntax of the VALUES clause of the INSERT statement is extended to include these keyword pairs also. The value of CURRENT DATE is the internal representation of a date, the value of CURRENT TIME is the internal representation of a time, and the value of CURRENT TIMESTAMP is the internal representation of a timestamp. Each value is based on a TOD clock reading performed during the execution of the statement containing the keyword pair. Each use of CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP in the same execution of the same statement gives a value based on the same TOD clock reading.

Otherwise, each use of these keyword pairs gives a value based on a different TOD clock reading. The value of CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP is the TOD clock reading plus the number of hours, minutes, and seconds specified by the installation via the PARMTZ parameters of MVS. Assuming that the TOD clock is set to GMT and PARMTZ gives the current difference from GMT for the timezone in which the installation is located, CURRENT DATE gives the current local date, CURRENT TIME gives the current local time, and CURRENT TIMESTAMP gives the current local timestamp. The value of CURRENT TIMEZONE is the current value of the PARMTZ parameter in the form of a time duration. The data type is DECIMAL(6,0). D...