Re: Temporal Units

On 4/28/07, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:
> I would like to store a temporal frequency as NUMERIC, without units, and
> have the application's front end (or middleware) transform the number to the
> appropriate interval name. I'm having difficulties figuring out how to do
> this.

This is a common enough problem. Three factors come to mind:

(1) Can all your intervals be expressed in absolute time units, such
as number of days? "Work shift" is a human concept whose length is
defined by context.

(2) When expressed as absolute time units, are all intervals valid for
your data, or do you only permit subsets of the total set of possible
intervals? In other words, if your user interface allows "1 week"
today, but you remove this option in the future, is old data referring
to this interval invalidated, or is that fine?

(3) Do you need to refer to specific months or years? The length of
these units vary according to month and leap year, and cannot be
reliably encoded as "n days". The exception is when working with
native PostgreSQL intervals; see below.

If possible, I recommend dealing with absolute units and avoiding #2
and #3 altogether.

PostgreSQL does have an interval data type that was designed for this
very problem:

create table intervals (name text, value interval);
insert into intervals ('day', '1 day'::interval), ('week', '1
week'::interval), ('month', '1 month'::interval);

Calculations on intervals are internally consistent with the Gregorian
calendar system:

# select current_date;
2007-04-29 00:00:00
# select current_date + '1 month'::interval;
2007-05-29 00:00:00
# select current_date + '1 month'::interval * 3;
2007-07-29 00:00:00

Based on this, you could create the table above as a lookup table for
symbolic constants.

Alexander.