SQL 92 Date and Time - Design Specification
This specification is incomplete. The following information will be added at a later date:
- Modules modified for exact numeric date arithmetic
- Design of GPRE modifications and ESQL interface.
- Investigate constructs such as DEFAULT CURRENT_DATE
Refer also to the SQL Time & Date functional specification
Also refer to specifications on Exact Numeric support & SQL Dialect support.
High-Level Design
The InterBase V6 implementation of SQL TIME/DATE/TIMESTAMP is a specialization of the existing InterBase V5 implementation of GDML-Date. The existing GDML-Date support is renamed to become TIMESTAMP. New data-types are introduced for SQL TIME and SQL DATE.
This feature crosses component boundaries, affecting JRD, DSQL, ISQL, GPRE, GBAK, and Remote. It also introduces new values for metadata.
There are several parts to this project:
- New base data-types blr_sql_time & blr_sql_date
- Rename of existing blr_date Data-types to blr_timestamp
- Implementation of new SQL expressions CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP
- Usage of SQL <date value function> in DEFAULT clause.
- Implementation of SQL function EXTRACT()
- Modification of <date type> to <string> and <string> to <date type> functions to conform with SQL specifications (while preserving Y2K compliance)
- Use of Exact numeric for <date type> arithmetic, instead of v5's floating point arithmetic.
- Migration issues from InterBase V5 to InterBase V6 - InterBase V6 SQL has semantic differences from InterBase V5 in several constructs.
Glossary
| DATE blr_sql_date |
A date value, consisting of year, month, day. |
| TIME blr_sql_time |
A time value, consisting of hours, minutes, seconds and fraction of seconds. |
| TIMESTAMP blr_timestamp |
A combination of a DATE value and a TIME value. |
| GDML DATE blr_date |
In V5, the DATE keyword meant a TIMESTAMP value. |
| <date type> |
Any DATE, TIME, or TIMESTAMP |
High-Level Algorithm
New InterBase data-types are introduced for the representation of date and time information.
InterBase data-types are defined publicly by the BLR values that describe them (in ibase.h), and internally by a dsc_dtype defined in jrd/dsc.h.
| Public Type |
Internal Type |
3GL Data-types |
Description |
| blr_timestamp (also known as blr_date prior to v6) |
dtype_timestamp (also known as dtype_date prior to v6) |
SQL: TIMESTAMP DATE (dialect 1 only) GDML: DATE |
8-byte structure. 1st 4-bytes are a SLONG representing the date portion of the timestamp as an offset from 17 Nov 1858. 2nd 4-bytes are a ULONG representing the time portion of the timestamp as a count of 1/10000 seconds since midnight. This structure is known in some circles as a "Modified Julian Date" or MJD. |
| blr_sql_time |
Dtype_sql_time |
SQL: TIME |
4 byte Unsigned Long Represents TIME as a count of 1/10000 seconds since midnight. (e.g.: as seconds since midnight in a DECIMAL (9,4) format) |
| blr_sql_date |
Dtype_sql_date |
SQL: DATE SQL: SQL DATE |
4 byte Signed Long Represents Date as an offset from 17 Nov 1858. |
blr_timestamp is represented by a data structure consisting of a blr_sql_date value followed by a blr_sql_time value.
Note that the blr_timestamp Data-types is unmodified from InterBase V5 to InterBase V6. It is identical to the InterBase V5 blr_date Data-types, but has been renamed. The renaming assists in easily identifying all points in the code that have been examined, and to prevent future programmer-confusion with the blr_sql_date Data-types.
Trivia Information: The base date for InterBase date calculation is 17 November 1858. The REASON for this is that is the base date used by Digital for all of their date calculations. The REASON for Digital's use of that date was originally thought to have something to do with the date the Smithsonian Institute first used photography to look at the stars. I recently discovered that 17 November 1858 is the base date for a calendar system called "Modified Julian Date" (MJD for short).
References for MJD and Julian Date are as follows:
http://www.astro.virginia.edu/~eww6n/astro/ModifiedJulianDate.html
In brief, Julian Dates sequentially number all days starting from 12 noon, 1st. January 4713 BC. MJD subtracts 2400000.5 from Julian Dates so that a MJD date number corresponds to a calendar date (which starts at midnight, not noon) and numbers are in a more sensible range for usage in recent history. The start date for the Julian Date system was determined by looking at "the 28-year solar cycle (the time taken before the days of the week next align with the Julian year), the 19-year lunar cycle (when the phase of the moon aligns with the days), and the 15-year Roman indiction cycles (used for taxation, census and other legal purposes which continued to be used in to the Middle ages). Assuming all dates are reckoned in the Julian calendar, for which it was named, the Julian period began at 12 noon, 1st. January 4713 BC"
So the date 17 November 1858 has no significance whatsoever other than being 2400000.5 days since the origin of the Julian Date 0.
Just as a total aside, there is a very interesting discussion of Star Trek's StarDates at:
http://www.cs.umanitoba.ca/~djc/startrek/stardates/
Data Structures/Class Hierarchy
New Public Data-types are defined as follows:
typedef INT32 ISC_DATE;
typedef UINT32 ISC_TIME;
typedef struct {ISC_DATE date_part; ISC_TIME time_part;} ISC_TIMESTAMP;
In InterBase V5, timestamp values were stored in the generic public ISC_QUAD datatype.
Interfaces
API
New Utility functions are provided in InterBase V6, for use by client-programs. These API's are utility only, they do not make a server call.
void isc_decode_sql_date (ISC_DATE *, struct tm *);
void isc_decode_sql_time (ISC_TIME *, struct tm *);
void isc_decode_timestamp (ISC_TIMESTAMP *, struct tm *);
void isc_encode_sql_date (struct tm *, ISC_DATE *);
void isc_encode_sql_time (struct tm *, ISC_TIME *);
void isc_encode_timestamp (struct tm *,ISC_TIMESTAMP *)
The existing V5 API calls isc_encode_date() and isc_decode_date() are preserved, and function identically to isc_encode_timestamp() & isc_decode_timestamp().
NOTE: None of the time / timestamp functions handle fractions of a second as the OS tm structure has no field for fractional seconds. A client can decode the fractional part by doing
- seconds_fraction=iscTime % 10000;
- seconds_fraction=iscTimestamp.time_part % 10000;
Encoding is done as:
- iscTime +=(seconds_fraction % 10000);
- iscTimestamp.time_part +=(seconds_fraction % 10000);
BLR
New BLR for data-types:
blr_sql_time
blr_sql_date
blr_timestamp (renamed from blr_date)
New BLR expressions:
blr_extract <extract_part> <blr expression>
blr_current_time
blr_current_date
blr_current_timestamp
Literal Expressions in BLR:
blr_literal allows a blr_data_type> definition that can be any of the <date type>. The literal value is encoded as:
| blr_literal |
blr_timestamp |
<date:4-byte blr integer> <time:4-byte blr integer> |
| blr_literal |
blr_sql_time |
<time:4-byte blr integer> |
| blr_literal |
blr_sql_date |
<date:4-byte blr integer> |
Dynamic SQL
The new data-types are described in a DSQL XSQLDA as follows:
| blr_timestamp |
SQL_TIMESTAMP Named SQL_DATE prior to V6. The SQL_DATE definition is retained for V5 application programs. |
| blr_sql_time |
SQL_TIME |
| blr_sql_date |
SQL_ANSI_DATE Note: The SQL CLI module specifies this should be named SQL_DATE, however InterBase used that symbol prior to V6 to indicate a blr_timestamp value. |
Remote Protocol
InterBase V5's highest remote protocol was Protocol 9.
In InterBase V6, Remote Protocol 10 is required for representation of date & time values across a network. Should a client using an earlier protocol access data defined as blr_sql_time or blr_sql_date the V10 protocol will convert the value into a V9 blr_timestamp value - with the irrelevant portion of the timestamp set to 0.
Should the client input data values into the irrelevant portion of a timestamp being used to transport a blr_sql_time or blr_sql_date value, then the remote layer will raise a data truncation exception to the client.
Detailed Design
Internal Data Structures
To struct vlu, vlux, and vluk in jrd/exe.h are added the following new fields to the union part of the structures:
- ISC_TIMESTAMP vlu_timestamp;
- ISC_DATE vlu_sql_date;
- ISC_TIME vlu_sql_time;
Detailed Algorithm
Evaluation of CURRENT_TIME
Usage of the CURRENT_TIME / _DATE / _TIMESTAMP functions are parsed into BLR and execution nod's as follows:
| SQL |
BLR Generated |
Internal Node |
Result Type |
| CURRENT_TIME |
blr_current_time |
nod_current_time |
blr_sql_time |
| CURRENT_DATE |
blr_current_date |
nod_current_date |
blr_sql_date |
| CURRENT_TIMESTAMP |
blr_current_timestamp |
nod_current_timestamp |
blr_timestamp |
The SQL standard requires that "if a single SQL statement results in multiple evaluations of CURRENT_TIME / CURRENT_TIMESTAMP / CURRENT_DATE, then all such evaluations return the same result. The time of evaluation is implementation dependent." (SQL Standard foundation (May 1996), section 6.16, general rule 3, page 205)
For InterBase, when the statement:
INSERT INTO aTable (aTime) VALUES (CURRENT_TIME)
is executed. Then inside all Triggers, Stored Procedures, etc. that are executed as a result of this statement, an evaluation of CURRENT_TIME will return the same result as what is inserted.
To implement this, a new field is added to request structure (req in jrd/req.h)
ULONG req_start_time; /* as returned by OS time() function */
This is initialized with the time at the start of the request (isc_start_request()). All evaluations of CURRENT_TIME (etc.) will use the stored timestamp value to determine the proper time.
In order to save the overhead involved in calling the OS clock() routine, the req_start_time will only be stored if the request, or any of it's children requests, could possibily use one of the CURRENT_* functions. This will be tested for while the request is being compiled, and a bit set in the req_flags to indicate the usage of time.
#define REQ_USES_CURRENT_TIME <tbd>
Requests that are chained from a statement (triggers & stored procedure executions) will copy this value from the parent request area into the child request area to ensure the same value is used for all evaluations until the end of the statement.
Note: RECURSIVE requests will receive timestamps based on the original start of the request.
GDML nested requests (request levels) will obtain a new timestamp for each level of the request.
Note: CURRENT_TIMESTAMP is, in a sense, an "invariant" sub-expression for a request. However, it is invariant not only for the request, but also for any sub-requests of the request - and cannot be implemented using the existing invariant expression mechanism.
DEFAULT CLAUSES
A Column or Domain definition may specify a DEFAULT clause. The default expression can be one of the SQL <datetime value functions>, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP.
The evaluation of the <datetime value function> occurs at the time the value is called for, and follows the SQL rules for all evaluations of such functions in a single statement to return the same result.
To implement this ….
- Look at the places where blr_user can be part of a DEFAULT clause.
- Make sure blr_current_etc can be allowed there as well.
- Do we need a subrequest to put the timestamp into?
EXTRACT Function
EXTRACT generates the following BLR
blr_extract
<byte: blr_extract_part>
<blr_expression: blr_extract_target>
<blr_extract_target> is any blr expression that results in a blr_sql_time, blr_sql_date, or blr_timestamp Data-types.
<blr_extract_part> indicates the type of information to extract from the blr_extract_target. Symbolic names for blr_extract_part are: blr_extract_year, blr_extract_month, blr_extract_day, blr_extract_hour, blr_extract_minute, blr_extract_seconds, blr_extract_yeardate, and blr_extract_weekday.
The engine parses this BLR into a new node type, nod_extract, which is processed per the normal node processing code.
The result of a blr_extract expression is <blr_short, 0> (a.k.a DECIMAL(4,0)), except when blr_extract_part is blr_extract_seconds. The result then is <blr_integer, -4> (a.k.a DECIMAL(6,4))
Index Types
Index segment types are tagged by InterBase to indicate the type of key that must be created for each segment. This is so we know the base data type of the index, and what conversions must be applied to a target-value to make a key to search the index with.
As InterBase supports on-the-fly changes to column data types, we cannot use the column datatype to determine the index type. When the index is created, the current column definition is bound to the index definition.
In InterBase V5, there was only the blr_timestamp data-type,
| Version, ODS |
Datatype |
Index Type |
Sort Key type |
Sort Key representation. |
| V5, ODS 8.x, ODS 9.x |
blr_date |
idx_date |
SKD_date |
8 byte double, representing day.fraction_of_day. E.g., 0.5 is Noon on 17 Nov 1858 |
| V6, ODS 8.x, ODS 9.x |
blr_timestamp (same as blr_date) |
idx_timestamp1 (same as idx_date) |
SKD_timestamp1 (renamed from SKD_date) |
8 byte float, representing the MJD of the 8-byte ISC_TIMESTAMP value. |
| V6, ODS 10.0 |
blr_timestamp |
idx_timestamp2 |
SKD_timestamp2 |
8 byte exact numeric, representing the number of seconds offset since the MJD base date as a DECIMAL(18,4). |
| V6, ODS 10.0 |
blr_sql_time |
idx_sql_time |
SKD_sql_time |
4 byte unsigned integer (direct copy of value in blr_sql_time) |
| V6, ODS 10.0 |
blr_sql_date |
idx_sql_date |
SKD_sql_date |
4 byte signed integer (direct copy of value in blr_sql_date) |
Note that in InterBase V6, with ODS 10.0 only, a new type of key is created for indices having a segment of type blr_timestamp. In ODS 10 blr_timestamp is indexed with an exact numeric. In previous ODS blr_timestamp was indexed with a double precision floating point. A new idx_ type and SKD_ type is defined for each <date type>.
SKD_timestamp1 indicates the ODS 9 double precision index format.
SKD_timestamp2 indicates ODS 10 exact numeric format.
Floating point vs. Exact Numeric
In V5, InterBase used double precision floating point arithmetic for date arithmetic and index keys. Double precision floating point has insufficient precision for exact blr_timestamp operations. Our blr_timestamp data-type is exact to 1/10000 of a second - a range of values from 0 to 864,000,000 fractional seconds for each day. Days range from -680,000 (approx.) to 42,046,540. The range of values to enumerate seconds exactly is represented by DECIMAL (17,4). Double Precision is exact only to approximately DECIMAL (15,*).
For InterBase V6, internal calculations use conversion to exact numeric.
For develoeprs, there are unknown behavioral differences to be documented. In InterBase V5, developer code did the following:
<aTimestamp>=<bTimeStamp> + <floating-point number>
Where the floating-point number represents a fraction of days.
Internally, in InterBase V6, we convert that user expression into
<aTimeStamp>=<bTimeStamp> + CAST( SECONDS_PER_DAY *
<floating-point number> AS DECIMAL (17,4))
The rounding introduced by this conversion will be different than in InterBase V5, but is closer to what the user intended.
Similarly, the result of Date Subtraction is now an exact numeric instead of a Double.
<aTimeStamp> - <bTimeStamp> è Decimal(17,4) Seconds
This has a different meaning however, it is the number of SECONDS between two timestamps, rather than the number of Days.
To keep consistency with existing user code, and expectations, a count of days between two timestamps is returned as the result of the expression:
CAST ((<difference as Decimal (17,4)> / SECONDS_PER_DAY)AS
DECIMAL(18,10))
The SQL Leveling Dialect method is used to flag code which with Date Expressions that used to return <floating point double> and now return <exact numeric>.
Time Arithmetic
Time Arithmetic uses number of seconds, instead of number of days. The result of
<aTime> - <bTime> returns DECIMAL(9,4) representing number of seconds.
For Time Addition,
<aTime> + <numeric value>
The numeric value is interpreted as representing a number of seconds (and fractional seconds). All Time addition is done modulo 24 hours.
Conversion from Date to String
In InterBase V5, InterBase when converted a blr_timestamp value into a string, it was formatted as
DD-Mon-YYYY hh:mm:ss.thou
e.g.:
3-Mar-1958 4:24:00.0030
In InterBase V5, if the buffer provided for the resulting string was too small the extra text was silently truncated. InterBase V5 developers made use of this in order to remove time information from columns that needed only date information, e.g.:
- UPDATE aTable SET aDATE=CAST(
CAST (aDATE as CHARACTER(11))
CAST (aDATE as CHARACTER(11)) as DATE);
In InterBase V6, the silent truncation of characters on conversion is no longer supported. (SQL hates any form of data loss).
SQL requires that the output of CAST (<date type> AS <string>) be the shortest string which is acceptable as input as a string literal that results in the same value (sqlfound.txt, section 6.22, general rule 8.iii.e). In InterBase V6 the output format for date-to-string conversion performed by the engine is:
| blr_timestamp |
YYYY-MM-DD hh:mm:ss.thou |
24 characters |
| blr_sql_time |
hh:mm:ss.thou |
13 characters |
| blr_sql_date |
YYYY-MM-DD |
10 characters |
Conversion from String to Date
The conversion from a string to a date presents several interesting problems.
- InterBase V6 must support the SQL required format of YYYY-MM-DD, which was not supported in V5.
- It is desirable for InterBase V6 to continue support for all previously correct input string formats (for compatibility with existing code). And the InterBase V5 supported input string formats are very flexible.
- InterBase V6 must not break Y2K compliance
- InterBase V6 must prevent any ambiguity in converting strings to date.
Note: Conversion of String to Time is well understood and not subject to the same issues as String to Date.
Tokens
| Token |
Description |
Examples |
| <4 digit numeric> |
A string of digits of at least 3 digits in length, and at most 4 digits. Leading 0 are significant. |
1998, 0048, 123, 0000 |
| <2 digit numeric> |
A string of digits of at least 1 digit in length, and at most 2 digits. Leading 0 are significant. |
12, 31, 04, 3, 0. |
| <English Month> |
JAN, FEB, … DEC. Case insignificant. |
JAN, Jan, jAn, |
Separators
In InterBase V5 the separator could be Period, Comma, slash, hyphen, space, Tab, or colon. Arbitrary counts of whitespace (Space or Tab) could be between input parts.
In InterBase V5, the type of separator is significant. A <Period>, if used as separator, indicates DD.MM.YYYY format. Any other separator indicates MM-DD-YYYY format.
The following formats are parsed by the InterBase V6 string-to-date conversion. Any character may be used as separator.
| year<4 digit numeric> |
month<English Month> |
day<2digit numeric> |
|
| year<4 digit numeric> |
month<2 digit numeric> |
day<2digit numeric> |
SQL standard required format. |
| month<English Month> |
day<2digit numeric> |
year<4 digit numeric> |
|
| month<English Month> |
day<2digit numeric> |
year<2 digit numeric> |
|
| month<2 digit numeric> |
day<2digit numeric> |
year<4 digit numeric> |
Note: possible confusion with D2-M2-Y4 If Separator is <period>, then D2.M2.Y4 is assumed. |
| month<2 digit numeric> |
day<2digit numeric> |
year<2 digit numeric> |
Note: possible confusion with D2-M2-Y2 If Separator is <period>, then D2.M2.Y2 is assumed. |
| month<English Month> |
day<2 digit numeric> |
|
Year is assumed to be current year. |
| month<2 digit numeric> |
day<2digit numeric> |
|
Year is assumed to be current year |
| day <2 digit numeric> |
month <English month> |
year <4 digit numeric> |
|
| day <2 digit numeric> |
month <English month> |
year <2 digit numeric> |
|
| TODAY |
Today's Date - at time of interpretation. |
||
| NOW |
Today's Date - at time of interpretation. |
||
| TOMORROW |
Tomorrow's Date - at time of interpretation. |
||
| YESTERDAY |
Yesterday's Date - at time of interpretation. |
||
InterBase V5 allowed Day & Month values to be longer than 2 digits (e.g.: 0012) -- this is now flagged as an error in InterBase V6.
One or Two-digit years are subject to ambiguity, with the year "01" representing possibly 1901, 2001, or 0001.
InterBase will not allow a Two-digit year to represent years in the range 0000 - 0099 (inclusive).
The interpretation of 2-digit years (when converted from string to <date type> format) is dependent on the current year at the time the evaluation is performed. InterBase will interpret a 2-digit year to mean the closest possible matching year in the 100 year interval centered in the current year.
For instance:
If the current year is 1998
| 2 digit input |
Resulting Year |
| 98 |
1998 |
| 99 |
1999 |
| 00 |
2000 |
| 01 |
2001 |
| … |
|
| 48 |
2048 |
| 49 |
1949 |
| 50 |
1950 |
| … |
|
| 97 |
1997 |
Note the dependency on the current year - developers should be discouraged from using 2-digit years in textual format.
Notice the changing interpretation of 48 as the current year changes
| Current Year |
Input |
Resulting Year |
| 1997 |
48 |
1948 |
| 1998 |
48 |
1948 |
| 1999 |
48 |
2048 |
| 2000 |
48 |
2048 |
The code for 2-digit year interpretation is pre-existing code (even from old v3 days!). However, with the heightened sensitivity to Year-2000 issues care must be taken do document our behavior and not break it.
Note on special Tokens:
The special tokens TODAY, NOW, YESTERDAY, TOMORROW are interpreted when the string is parsed into a date value. There are some distinctions on how the evaluation is performed.
| CAST ('TODAY' AS DATE) |
Conversion occurs at run-time. May result in a different result for each row. |
| DATE 'TODAY' |
Conversion occurs at Compile-time. (Either GPRE or DSQL). Result then is dependant on when the statement was compiled from SQL to BLR. |
| CURRENT_DATE |
Conversion occurs at run-time. All statements using CURRENT_DATE receive the same value for the time. |
These special tokens are retained in InterBase V6 for compatibility with InterBase V5 applications. However, their use in literal <date type> is discouraged!
Migration from V5 to V6
Feature Availability Matrix
| blr_timestamp |
V5, V6 |
ODS 8.x - 10.x |
N/A |
|
| blr_sql_time |
V6 |
ODS 10.x |
N/A |
|
| blr_sql_date |
V6 |
ODS 10.x |
N/A |
|
| EXTRACT() |
V6 |
any |
any |
|
| CURRENT_TIME CURRENT_TIMESTAMP CURRENT_DATE |
V6 |
any |
any |
|
| TIME keyword |
V6 |
ODS 10.x |
1 and above |
|
| TIMESTAMP keyword |
V6 |
any |
1 and above |
|
| DATE keyword |
V5 |
any |
1 |
|
| DATE keyword |
V6 |
any |
1 |
result is blr_timestamp |
| DATE keyword |
V6 |
any |
2 |
ERROR issued |
| DATE keyword |
V6 |
ODS 10.x |
3 |
result is blr_sql_date |
| String to Date |
V6 |
any |
any |
All V5 formats supported. YYYY-MM-DD also supported |
| Date to String |
V6 |
any |
any |
YYYY-MM-DD is only output format. |
The key migration issue for Time/Date support is the meaning of the DATE keyword. In InterBase V5 it generated a blr_timestamp data-type. In InterBase V6 (and the SQL standard) it means a blr_sql_date Data-types.
To enable developers to keep existing applications running, and migrate to the new meaning of the keyword, the following leveling rules are applied.
| SQL Dialect |
Leveling rules |
| 1 |
DATE means blr_timestamp data-type |
| 2 |
DATE keyword is not allowed |
| 3 |
DATE keyword means blr_sql_date data-type. |
| any |
TIME, TIMESTAMP, SQL DATE, EXTRACT(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, Literal DATE / TIME / TIMESTAMP strings. |
blr_sql_time and blr_sql_date data-types are only available in ODS 10 databases.
To implement these restrictions:
DSQL - During parse of a statement, when the keyword DATE is recognized, we check the passed in SQL Dialect rules to determine what action to take on the DATE keyword (define a blr_timestamp, report error, or define a blr_sql_date).
Whenever a blr_sql_time or blr_sql_date column is defined, we check the ODS to ensure it is 10.0 or above. An error is reported if the ODS does not support these data types. (This occurs during dsql/pass1.c).
GPRE - Similar to DSQL implementation. Note that the ODS restriction can only be checked against the compile time database. If the runtime database is pre 10.x a run-time error is issued when the engine tries to execute the DYN to define the new column types.
If an InterBase V6 compiled program is run against an InterBase V5 server, the InterBase V5 Server will reject the column definition as improperly formatted Dyn. If the program is run against an InterBase V6 server, the InterBase V6 server will check that the ODS of the target database is 10 or greater -- if not the exception "ods doesn't support date" is thrown.
GBAK
GBAK treats the new data-types blr_sql_time & blr_sql_date as other data-types. The format ID of the backup is incremented to prevent a prior version of GBAK from attempted to restore from a file that may contain blr_sql_time or blr_sql_date data.
New/Affected modules
align.h
Added entries for new blr_dtype's for time & date.
blp.h
New blr for extract, current_date, current_time, current_timestamp
btr.h
New index types for time only & date only data-types
dsc.h
New defines for dtype_sql_date dtype_sql_time
Renamed dtype_date as dtype_timestamp
Introduced MACRO's to cover property testing on different types.
exe.h
New nodes for nod_extract, nod_current_date, nod_current_time, nod_current_timestamp
fields.h
Fixed fld_time to be a TIMESTAMP field.
blr.h
New blr for time / date
Renamed blr_date to blr_timestamp
Added blr for current_date/time/timestamp
Added blr for extract
nod.h
added nodes for extract, current_date, current_time, current_timestamp
sort.h
Renamed SKD_date to SKD_timestamp
Added SKD_sql_time & SKD_sql_date for sort key values on Time / Date
types.h
Replaced hard-coded constants with symbolic names
Added new data-types DATE & TIME.
Renamed existing DATE type as TIMESTAMP.
dsql/keywords.h
Added new kewords
EXTRACT, TIME, TIMESTAMP, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, SQL, YEARDAY, WEEKDAY,
dsql/node.h
Added new nodes for current_date/time/timestamp
Defined constants to replace hardcoded values for MAKE_string()
blrtable.c
New nodes for extract & current_*
Removed warnings
btr.c
Handle index lengths & key creation for TIME & DATE.
cmp.c
Implement nod_extract
Implement nod_current_date/time/timestamp
Implement arithmetic on time / date values
cvt.c
New constants for precision of TIME values
Conversion of time / date / timestamp to/from text
cvt2.c
Handle TIME & DATE conversion
dsql/ddl.c
Generate DYN for TIME / DATE defined fields.
- dialect leveling code
dfw.e
Index types for TIME / DATE fields
dsc.c
Length definitions in conversion of new TIME/DATE to string
dsql/dsql.c
Conversion of InterBase date/time types to SQL SQLDA types
Handle new blr
evl.c
Evaluate nodes for current_date (etc)
Implement nod_extract
extvms.c
Rename dtype_time to dtype_timestamp
fun.e
Handle TIME / DATE values as parameters / returns from UDF
gds.c
Pretty print of blr_extract, blr_current*
Assertion checks on isc_extract_date (really isc_extract_timestamp)
Comments on Julian date conversion
dsql/gen.c
Generate blr for EXTRACT, CURRENT_TIME*, new time/date values
ini.e
Handle system fields that are time / date / timestamp
isql/isql.e
Input /Output of DATE & TIME values
Allow SET TERMINATOR
Allow SET TRANSACTION
make.c
TIME / DATE SQL literals
Aritmetic on time / date values
metd.e
Use table constants instead of literal constants
mov.c
Conversion of double to date formats (date/time/timestamp)
This is a temporary routine that should be eliminated when we use exact numeric for time & date calculation.
opt.c
Handle time/date extract and current_*
par.c
Handle blr_extract, blr_current*, blr_sql_date, blr_sql_time
dsql/pass1.c
Handle extract
sdl.c
Handle arrays of TIME / DATE
isql/show.e
Rename DATE -> TIMESTAMP for extract.
Handle extract of TIME & DATE
sort.c
Handle sort keys for TIME / DATE
dsql/utld.c
Conversion between blr_sql_date (etc) to/from SQL_DATE (etc)
parse.y
SQL parsing of EXTRACT, CURRENT_*, TIME / DATE, Literals
GPRE Modules to be determined
Modules for Exact Numeric in Date Arithmetic to be determined.
Testing Considerations
Test should make special consideration of conversion from strings containing 2-digit years to InterBase TIMESTAMP & DATE types.
All supported input string formats for String to Date should be tested.
Special consideration of the addition/subtraction of scalar values from TIMESTAMP. In particular, it should be possible to add 1 second to a TIMESTAMP by doing
<timestamp column> + CAST(1.0/(24*60*60) AS DECIMAL(<something>))
Validate that multiple instances of CURRENT_TIME (etc) within a single SQL statement are evaluated simultaneously.
Validate usage of blr_sql_time & blr_sql_date data-types within arrays, stored procedures, domains, columns, calculated fields, CAST(), indices, and UDF's.
Validate that UNIQUE indices on TIME & TIMESTAMP are truly unique to 0.0001 second differentials.