I was a bit suprised to find out that Oracle 10 supports INTERVAL type and temporal operator OVERLAPS.
Intervals are quite simple. There is INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH support.
Example:
INTERVAL '5' DAY is 5 days.
INTERVAL '5 01:00:00' is 5 days and 1 hour.
INTERVAL '1' YEAR is one year.
INTERVAL '1-2' YEAR TO MONTH is one year and 2 months.
INTERVAL '12' MONTH is 12 months.
I don't know what is INTERVAL YEAR TO MONTH for. In what case when operating on ISO dates one year would be something different from 12 months. The most interesting, in my opinion, would be INTERVAL MONTH TO DAY and INTERVAL MONTH TO WEEK, because operating on days/weeks and months simultanously is the most problemating.
OVERLAPS operator is less interesting. It can be used only in WHERE clause and returns true when 2 periods, defined as (date-from, date-to), overlaps.
When overlapping is considered, periods are defined as exclusive sets
so (date '2009-01-01', date '2009-01-10') OVERLAPS (date '2009-01-10', date '2009-01-20') is NEGATIVE. Inclusive treating of periods by OVERLAPS operator would be very problematic to handle, considering we operate on timestamps. However, when we treat DATE field as date (not date with time) and take care that time part is '0', OVERLAPS is not very handy. From my operations, it does not make temporal queries more effective.
No comments:
Post a Comment