SQL 92 Date Support in Gpre - Functional Specification

Description

This document describes the gpre support part for SQL Date & Time in gpre. It also explains the difference in behaviour for sql dialects 1, 2 and 3.

In order to completely support the new SQL date time feature, gpre must:

  • Be able to parse DATE, SQL DATE, TIME and TIMESTAMP as data types for field/domain definitions.
  • Also support the new SQL function EXTRACT() along with its extract parts.
  • Be able to generate the correct dyn for metadata changes.
  • correctly cast()/ evaluate the results of date/time calculations.
  • Support the new literal and constants. (The sql date functional specification has detailed information on littorals and constants)

User Interface/Usability

The following table shows how gpre would interpret the date, time , timestamp column types based on ods version, server version and sql dialect.

SQL Keyword

ODS Ver.

Compile time Server Ver.

Clients current Dialect

Resultant

Reason for Error

DATE

<10.0

<6.0

1

TIMESTAMP

2

Error

Ambiguous Keyword

3

Error

Compile time database does not support SQL DATE

10.0

6.0

1

TIMESTAMP

2

Error

Ambiguous Keyword

3

SQL DATE

TIME

<10.0

<6.0

1,2,3

Error

Compile time server does not support TIME

10.0

6.0

1,2,3

SQL TIME

TIMESTAMP

Any

Any

Any

TIMESTAMP

The following are the list of new keywords in InterBase V6.0 gpre.

  • New data types : TIME, TIMESTAMP, SQL DATE
  • New constants : CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
  • New Literals : DATE 'String' , TIME 'String' , TIMESTAMP 'String'
  • New Function : EXTRACT ( <extract_part> FROM <value>)
    where extract_part could be one of YEAR, MONTH, DAY, WEEKDAY, YEARDAY, HOUR, MINUTE, SECOND

Requirements and Constraints

General InterBase requirements

Migration Issues

InterBase V6.0 gpre allows the user to exploit the new data type i.e. date, time, timestamp present in the InterBase V6.0 server. This also means that in order to support backward compatibility gpre would have to emit proper definitions, blr and dyn depending upon the database ods, server version and sql dialect when pre-processing a ESQL program file.

Meta Data Statements:

While creating tables, domains, procedures etc. gpre generates dyn based on the following criteria

  • if a column type is TIMESTAMP then irrespective of the database ods, server version, sql dialect gpre will generate dyn that represents timestamp.
  • if a column type is TIME then gpre will punt with error for ods <10 and server version < 6.0 irrespective of the dialect. For all other cases dyn that represents sql time will be generated.
  • if a column type is DATE and the ods is < 10 and server version <=6.0 then gpre will generate
    dyn that represents timestamp if the sql dialect is 1
    Error if the sql dialect is 2,3
  • For all other cases i.e. ods is==10 and server==6.0 gpre will generate
    dyn that represents timestamp if the sql dialect is 1
    Error if the sql dialect is 2
    dyn that represents sql date if the sql dialect is 3

Data Retrieval statements:

While selecting, inserting etc. data, gpre will generate blr exactly as specified in the criteria above. blr_timestamp, blr_sql_date, blr_sql_time will be generated for column whose data types are TIMESTAMP(or DATE in dialect 1), DATE, TIME respectively.

For variables that are declared as based on a table column, following changes will be noticed.

  • A variable that is based on a timestamp column will be declared as ISC_TIMESTAMP variable.
  • A variable that is based on a time column will be declared as ISC_TIME variable.
  • A variable that is based on a date column will be declared as ISC_TIMESTAMP variable in dialect 1 or as ISC_DATE variable in dialect 3.

Note that earlier versions of gpre generated ISC_QUAD for variables based on date column type.