[warning: largely off-topic] Re: Data type confusion

Josh,

Thanks for your explanation. I'd like to get hold of a copy of SQL99/PKG001 to see what they have actually defined.

I think the INTERVAL type sux :-) Long rant follows - consider hitting the delete button now.

In this area, there are a number of different concepts that it would make sense to separate.

Let's call one UTIME. It is time defined as (the time-coordinate of) an event in the history of the Universe. [We'll ignore Einstein for the following.] It has physical meaning.

Let call another LDATE. It is what you and I normally call a date. Specifically, it is a legal (or social or religious) representation of a UTIME. You can make a contract (legal with man, or religiously with God) using LDATEs.

Let's invent a CALENDAR. It translates between UTIME and LDATE. It changes all the time ;-( Well, maybe not *all* the time but it is sufficient unstable to be a problem for some applications.
There is not only the problem of Julian vs Gregorian vs some other calendar.
Consider the issue of changing the rules governing summer-time. It is not long ago that Britain changed the rule for ending summer time from being the fourth Sunday in October to being the last Sunday in October. Some countries seem to decide on a year to year basis if they want to have summer time.
Finally, the international committee that governs the CALENDAR only have to give eight weeks notice when it introduces a leap-second. (Leap seconds are the reason that struct tm.tm_sec has a range of 0,...,61.)

This means that the CALENDAR is, strictly speaking, only known eight weeks in advance. It also highlights the difference between LDATE and UTIME: if we have a contract for me to start a machine on a specific LDATE and a leap second is introduced between now and then, then I'd better change that sleep(n) statement in my control program to sleep(n+1) or the factory will start too early.

I once spent an unhappy week debugging a problem related to this :-( It really would have started the factory one hour too late.

Now of course you can define deltas. Unix systems kind of keep UTIME using a delta: a variable of type time_t holds the number of seconds since a specific event (defined as a given LDATE for a fixed CALENDAR). Let's call them DUTIME and DLDATE.

They are conceptually different: One day of DLDATE may be 23, 24, or 25 hours of DUTIME, depending on summer time rules. It may be 86,400 or 86,401 (rarely: 86,402) seconds depending on leap seconds.

Important: There is no meaningful translation between DUTIME and DLDATE except for a fixed (start- or end-) UTIME (or DLDATE).

(No, really!!)

Let's introduce a final concept: a unit of UTIME. Maybe we call it TIMEU. You need to measure UTIME is something, maybe seconds is the fundamental unit (as in SI) and you have other, derived units.

We don't need a similar concept for LDATE - it is effectively provided by the CALENDAR. It is it that which defines years, months, or whatever the Incas used to use...

Now BACK ON-TOPIC:

What's an INTERVAL supposed to be? DUTIME, DLDATE, TIMEU or something that really belongs to the CALENDAR?

All of the above?

Methinks SQL99 (or PostgreSQL's implementation) is going for the latter option. Methinks it is confusing. But then, I'm thick. :-)

Apologies for the interruption -- We now continue the regular scheduled program....

Allan.