Coercion Between Date/Time Data Types
The rules governing coercion between the various date/time data types are as follows:
1. Ansidate cannot be converted to any of the time types, nor can the time types be converted to ansidate. Doing so results in error E_AD5066_DATE_COERCION.
2. When converting from a data type that does not have time zone information to a data type with time zone value (for example, ansidate to a timestamp with time zone, time without time zone to a time with time zone), the time zone is set to the current session time zone.
Example: In Eastern Standard Time (EST) time zone (that is, -05:00), the following statements insert a value of 2007-02-08 16:41:00-05:00 in the database.
CREATE TABLE tab (col1 TIMESTAMP WITH TIME ZONE);
INSERT INTO tab VALUES (TIMESTAMP ‘2007-02-08 16:41:00’);
3. Ingresdate, time with local time zone, and timestamp with local time zone, store date/time values in UTC. When converting from other data types like ansidate, time with/without time zone, timestamp with/without time zone to these data types, the session time zone displacement is subtracted from the date/time value. On the reverse operation, when converting from ansidate, time with/without time zone, timestamp with/without time zone to ingresdate, time with local time zone and timestamp with local time zone, the session time zone displacement is added to the date/time value in the database to make it in local time zone.
Example: In EST time zone (with time zone displacement of -05 :00), the following query stores a value of 2007-02-18 15:04:12 in the database:
CREATE TABLE tab (col1 TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO tab VALUES (TIMESTAMP ‘2007-02-18 10:04:12’);
If this value was selected in PST time zone (with time zone displacement of -08:00), the session time zone value is added to the value stored in database and the value in local time zone is displayed, that is:
2007-02-18 07:04:12
4. When a time value is converted to a timestamp, date, or time/timestamp with local time zone types, the year, month, and day fields are filled with the current year, month, and day value.
Example: If current date is 08 Feb 2007 then the following statements insert a value of 2007-02-08 17:01:00 in the database:
CREATE TABLE tab (col1 TIMESTAMP);
INSERT INTO tab VALUES (TIME ’17:01:00’);
5. When converting from time without time zone to time with local time zone, the following procedure is used:
a. Current date is added to the time value to make a timestamp.
b. Time zone displacement is then applied to the time value.
c. The date part is removed from the result.
Example: If current date is 08 Feb 2007 and the session time zone is -05 :00 (EST), the following query stores a value of 22:01:00 in the database:
CREATE TABLE tab (col1 TIME WITH LOCAL TIME ZONE);
INSERT INTO tab VALUES (TIME ’17:01:00’);
6. INTERVAL types cannot be converted to any other types except themselves and ingresdates.
7. When a time/timestamp with time zone is converted to ingresdate, time with local time zone, or timestamp with local time zone, the time value is converted to UTC by applying the time zone information in the value.
Example: In any time zone, the following query will insert a value of 2007-02-18 03:04:12 in the database:
CREATE TABLE tab (col1 TIMESTAMP WITH LOCAL TIME ZONE);
INSERT INTO tab VALUES (TIMESTAMP ‘2007-02-18 10:04:12-07:00’);