At 04:12 PM 22/10/2005 +0000, Ali G. wrote:

> >
> > I disagree. TIME is a specific domain.
>
>every datatypes are specific.
>
> >
> > There simply is NO such thing as 28:00 in a TIME type.
>
>Yes, becuse it is a must.
>
>TIME is not a cosmic static point!

A TIME value represents a static point in a day (according to the 24-hour
clock convention observed by many mortals and computing devices on this
planet).

>TIME is a numeric datatype stored in specific format to dislplay
>fastly.
>
>TIME is an integer (also may be a float if you want)
>most significant bits took hours(to be sortable), others took
>minutes,seconds,milliseconds or microseconds or nanoseconds or
>picoseconds...(needs BIGINT)

In Firebird, TIME is *stored* as a 32-bit unsigned integer (not BigInt),
representing the number of ten-thousandths of a second that have elapsed
since midnight. It will never be higher than 863,999,999 because it is
constrained to this limit. Internally, this number is handled as DECIMAL(9,4).

>You can display it without any calculation(other than binary to
>ASCII convertion, it is valid for all numeric datatypes)
>But if there is any calculation with it, you should to convert it
>to an monoblock integer number.

In some DBMS's, that's true, because they store date and time data as
numbers that can be computed by simple arithmetic, e.g. Paradox. By
fiddling around with a datapump, perhaps. But you can't use SQL to retrieve
the stored form of a TIME type, nor to store it as its raw type. And you
cannot "import" a raw date or time datum from another DBMS and expect it to
work with Firebird. Date/time storage is absolutely vendor-specific.

>there is another way to calculate TIME and DATE but much more
>expensive and difficult.
>I wrote alot of TIME,DATE calculation functions in C,RPG,etc, when i
>was young. there was no source or internet.(only ansiklopedies)

And the Firebird engine does its TIME and DATE computations in C in order
to store and retrieve date/time data. But data-users do their computations
in SQL. SQL uses symbols taken from English, but that does not mean that
SQL is semantically mapped to English. In English, if we want to know the
time-of-day, we ask "What is the TIME?" It is that gloss that SQL has
borrowed (and not "Can you estimate the TIME it will take?")

In Firebird, in SQL, you can add a numeric(9,4) number n (seconds) to a
TIME to get a result of TIME type that is that time-of-day + n seconds (or
an overflow), you can subtract n seconds to get a TIME type result (or an
underflow) and you can subtract one TIME type from another TIME type to get
a difference interval in seconds (or an underflow).

>if you know a quick way about TIME,DATE,TIMESTAMP calculation,
>i will happy to learn it from you.

To do what? In SQL you can "add" a DATE type to a TIME type to give a
TIMESTAMP result. Under the hood, this concatenates the DATE
representation (also a constrained 32-bit integer with a scale of 4) to the
TIME representation. However, since this is a concatenation operation
(appending time-of-day to a date-only value), you cannot "subtract" a TIME
from a TIMESTAMP to get a DATE type.

You can add a number n to or subtract a number n from a DATE or TIMESTAMP
to advance or reduce the value by n days. The stored value of a DATE or
TIMESTAMP can be negative: this will happen for anything earlier than
November 17, 1898. In resolving these numbers, the engine counts backwards
until it hits the lower limit of numeric(9,4), which is some point in time
around AD 10, at which point (if it hasn't finished) it will throw an
underflow exception. That means Firebird can't store dates earlier than
that limit. (Task for you, Ali: compute Firebird's lowest and highest dates!)

If you think Firebird's "date zero" (1898-11-17) is a funny date, you might
be interested in Jim Starkey's posting to Firebird-general a couple of days
ago. It has a mythology all its own. :-)

./heLen