Databases Reference
In-Depth Information
will store Asserted Versioning temporal parameters, such as begin
and end dates, in UTC. For example, with Policy_AV being an
asserted version table of insurance policies, we would insert a
policy like this:
INSERT INTO Policy_AV (oid, asr_beg_dt .....)
VALUES (55, CURRENT TIMESTAMP - CURRENT TIMEZONE .....)
For queries, they will perform better if we do the time conver-
sion before using the value as a selection predicate in the SQL
itself. This is because most optimizers treat functions that
appear in predicates as non-indexable. For example, in DB2,
we should write:
SET :my-cut ¼ TIMESTAMP(:my-local-time-value) - CURRENT
TIMEZONE
SELECT .....FROM .....
WHERE oid ¼ 55
AND asr_beg_dt <¼ :my-cut
AND asr_end_dt > :my-cut
rather than
SELECT .....FROM .....
WHERE oid
55
AND asr_beg_dt
¼
<¼
TIMESTAMP(:my-local-time-value) - CURRENT TIMEZONE
AND.....
However, if these functions are used for display purposes, then
there is no reason to exclude them from the queries. For example:
SELECT asr_beg_dt þ CURRENT TIMEZONE AS my_local_asr_beg_dt . .
...FROM.....
It would also be useful to add alternate columns for the tem-
poral dates in our views that have the translation to local time
performed already.
The Very Concept of Bi-Temporality
Business IT professionals were using tables with both an
effective date and a physical row create date by the early 90s.
9
But
they were doing so with apparently no knowledge of
9
Or timestamps, or other datatypes. We remind the reader that, throughout this topic,
we use the date datatype for all temporal columns, and a first of the month value for
all our dates. This simplifies the presentation without affecting any of the semantics.
In real business applications, of course, these columns would often be timestamps.
