Ramblings from the bit bucket

One of the new features included in release 2 of Ingres 2006 was the addition of ISO SQL date (also known as ANSI date), time and interval types to the Ingres date type.

The Ingres date is composed of both a date and a time component which differs from the discrete date and time of the ISO types. Both types are called by the same name DATE which can be the source of confusion.

For example, a table that includes an ISO SQL date column

create table t1 (c1 integer, c2 date);

The following statements fails as the Ingres constant ‘now’ is not valid for an ISO SQL date.
insert into t1 (c1, c2) values (1, ‘now’);
insert into t1 (c1, c2) values (2, ‘today’);

E_US10E5 ‘2007-07-14 00:00:00′ is an invalid format or value for ANSI
date. Please enter a valid date in yyyy-mm-dd format.
(Sun Jul 15 14:01:59 2007)

The use of Ingres constants can be acheived by implicit coercion, using one of the cast functions or using a literal
insert into t1 (c1, c2) values (1, date(’now’));
insert into t1 (c1, c2) values (2, date(’today’));
insert into t1 (c1, c2) values (3, cast(date(’now’) as ansidate));
insert into t1 (c1, c2) values (4, ‘2007-07-31′);

+-------------+-----------------+
|c1           |c2               |
+-------------+-----------------+
|            1|2007-07-31       |
|            2|2007-07-31       |
|            3|2007-07-31       |
|            4|2007-07-31       |
+-------------+-----------------+

For a table that includes an Ingres date column

create table t2 (c1 integer, c2 date);

insert into t2 (c1, c2) values (1, ‘now’);
insert into t2 (c1, c2) values (2, ‘today’);
insert into t2 (c1, c2) values (3, cast(ansidate(’2007-07-31′) as ingresdate));
insert into t2 (c1, c2) values (4, ansidate(’2007-07-31′));
insert into t2 (c1, c2) values (5, ‘31/07/07′);
insert into t2 (c1, c2) values (6, current_time);

select * from t2;

+-------------+-------------------------+
|c1           |c2                       |
+-------------+-------------------------+
|             1|31-jul-2007 03:49:41    |
|             2|31-jul-2007             |
|             3|31-jul-2007 00:00:00    |
|             4|31-jul-2007 00:00:00    |
|             5|31-jul-2007             |
|             6|31-jul-2007 04:14:40    |
+-------------+-------------------------+

Note that for values of the Ingres date type format of the output is dependent on the II_DATE_FORMAT setting and affects intepretation of date literals for Ingres date values.
You may have also noticed that the ‘create table’ statements looked the same. There is a server configuration parameter ‘date_type_alias’ which determines whether the DBMS server interprets the word ‘date’ as an Ingres date or as an ANSI date. To avoid confusion date types should be declared explicitly in column specifications.

Seasoned Ingres users are well aware of the issues of using the same data type for both date and time values but as is evident from the table t2, date values can exist with empty time components but time values cannot have empty date components making time comparisons more challenging.

Consider the table

create table t3 (c1 integer, c2 ingresdate, c3 ingresdate);

insert into t3 values (1, ‘31-Jul-2007 17:00:00′, ‘5 hrs’);
insert into t3 values (2, ‘31-Jul-2007 18:14:00′, ‘5 hrs 45 mins’);
insert into t3 values (3, ‘31-Jul-2007 18:24:00′, ‘4 hrs 21 mins’);
insert into t3 values (4, ‘31-Jul-2007 19:00:00′, ‘4 hrs 59 mins’);
insert into t3 values (5, ‘31-Jul-2007 12:21:00′, ‘6 hrs’);
insert into t3 values (6, ‘31-Jul-2007 21:00:00′, ‘3 hrs 1 min’);
insert into t3 values (7, ‘31-Jul-2007 21:00:00′, ‘48 hrs’);


A specific time can be specified using the functions hour(), minute() and second(). For example,

select c1, c2, c3 from t3 where hour(c2) = 18 and minute(c2) = 24

would return the row with the 18:24 time.

+-------------+---------------------+-----------------+
|c1           |c2                   |c3               |
+-------------+---------------------+-----------------+
|            3|31-jul-2007 18:24:00 |4 hrs 21 mins    |
+-------------+---------------------+-----------------+

Select the rows in the table where the sum of the date and the interval will rollover to the next day.
select c1, c2, c3 from t3 where date_trunc(’day’, c2 + c3) > date_trunc(’day’, c2);

+-------------+---------------------+-----------------+
|c1           |c2                   |c3               |
+-------------+---------------------+-----------------+
|            6|31-jul-2007 21:00:00 |3 hrs 1 mins     |
|            7|31-jul-2007 21:00:00 |2 days           |
+-------------+---------------------+-----------------+

The ANSI equivalent

create table t4 (c1 integer, c2 time, c3 interval day to second)
insert into t4 values (1, ‘17:00:00′, ‘00 05:00:00′);
insert into t4 values (2, ‘18:14:00′, ‘00 05:45:00′);
insert into t4 values (3, ‘18:24:00′, ‘00 04:21:00′);
insert into t4 values (4, ‘19:00:00′, ‘00 04:59:00′);
insert into t4 values (5, ‘12:21:00′, ‘00 06:00:00′);
insert into t4 values (6, ‘21:00:00′, ‘00 03:01:00′);
insert into t4 values (7, ‘21:00:00′, ‘00 48:00:00′);

A specific time can be specified directly. For example,

select c1, c2, c3 from t3 where c2 = ‘18:24:00′;

+-------------+---------------------+-----------------+
|c1           |c2                   |c3               |
+-------------+---------------------+-----------------+
|            3|18:24:00             |0 04:21:00       |
+-------------+---------------------+-----------------+

Select the rows in the table where an interval causes the time to rollover to the next day. Here we coerce values to Ingres dates and extend the time to include a date component.

select c1, c2, c3 from t4 where interval(’day’, (date(c2) + c3) - date(’today’)) >=1;

+-------------+---------------------+-----------------+
|c1           |c2                   |c3               |
+-------------+---------------------+-----------------+
|            6|21:00:00             |0 03:01:00       |
|            7|21:00:00             |2 00:00:00       |
+-------------+---------------------+-----------------+

I hope that this has offered a brief and superficial glimpse of the date and time types included in Ingres 2006 release 2, for more information see the documentation @ http://docs.ingres.com/sqlref/UnderstandingSQLDataTypes.