Subject | Re: [firebird-support] Re: Hourly rate |
---|---|

Author | Helen Borrie |

Post date | 2005-10-22T23:21:20Z |

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

clock convention observed by many mortals and computing devices on this

planet).

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).

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.

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).

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

> >A TIME value represents a static point in a day (according to the 24-hour

> > 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!

clock convention observed by many mortals and computing devices on this

planet).

>TIME is a numeric datatype stored in specific format to dislplayIn Firebird, TIME is *stored* as a 32-bit unsigned integer (not BigInt),

>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)

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 toIn some DBMS's, that's true, because they store date and time data as

>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.

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 moreAnd the Firebird engine does its TIME and DATE computations in C in order

>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)

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,To do what? In SQL you can "add" a DATE type to a TIME type to give a

>i will happy to learn it from you.

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