Ingresdate Interval Arithmetic
Ingresdate intervals can be added, subtracted, and divided, and can be multiplied or divided with a numeric expression. The following lists the results of date arithmetic:
When adding intervals, each of the units is added.
For example:
date('6 days') + date('5 hours')
yields, 6 days 5 hours, while:
date('4 years 20 minutes') + date('6 months 80 minutes')
yields, 4 years 6 months 1 hour 40 minutes.
In the above example, 20 minutes and 80 minutes are added and the result is 1 hour 40 minutes. 20 minutes plus 80 minutes equals 100 minutes, but this result overflows the minute time unit because there are 60 minutes in an hour. Overflows are propagated upward except when intervals are added. In the above example, the result is 1 hour 40 minutes. However, days are not propagated to months. For example, if 25 days is added to 23 days, the result is 48 days.
When intervals or absolute dates are subtracted, the result is returned in appropriate time units. For example, if the following subtraction is performed:
date('2 days') - date('4 hours')
the result is 1 day 20 hours.
Date constants can be converted into numbers of days relative to an absolute date. For example, to convert today’s date to the number of days since January 1, 1900:
num_days = int4(interval('days', 'today' - date('1/1/00')))
To convert the interval back to a date:
(date('1/1/00') + concat(char(num_days), ' days'))
where num_days is the number of days added to the date constant.
Adding a month to a date always yields the same date in the next month. For example:
date('1-feb-98') + '1 month'
yields March 1.
If the result month has fewer days, the resulting date is the last day of the next month. For instance, adding a month to May 31 yields June 30, instead of June 31, which does not exist. Similar rules hold for subtracting a month and for adding and subtracting years.
Dates that are stored without time values can acquire time values as a result of date arithmetic. For example, the following SQL statements create a table with one date column and store today’s date (with no time) in the column:
create table dtest (dcolumn date);
insert into dtest (dcolumn) values (date('today'));
If the contents of the date column is selected using the following query:
select dcolumn from dtest;
a date with no time is returned. For example:
09-aug-2001
If date arithmetic is used to adjust the value of the date column, the values in the column acquire a time. For example:
update dtest set dcolumn=dcolumn-date('1 hour');
select dcolumn from dtest;
returns the value:
08-aug-1998 23:00:00