[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
ignoreEinstein 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*
thetime but it is sufficient unstable to be a problem for some applications.   There is not only the problem of Julian
vsGregorian vs some other calendar.   Consider the issue of changing the rules governing summer-time.  It is not long
agothat Britain changed the rule for ending summer time from being the fourth Sunday in October to being the last
Sundayin October.  Some countries seem to decide on a year to year basis if they want to have summer time.   Finally,
theinternational 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
differencebetween LDATE and UTIME: if we have a contract for me to start a machine on a specific LDATE and a leap
secondis 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
toolate.
 

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

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,
maybeseconds 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'mthick. :-)
 



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



Allan.