Subject Re: Hourly rate (OT) Adam 2005-10-23T00:21:19Z
Hmmm,

I think it might be time to put an OT Label or change the subject or
something. The original question was not this, but this is pretty deep
stuff LOL.

Given that our application pretty much deals with times, here are some
points to ponder.

By definition, a valid time

00:00 <= valid time < 24:00
(note the strictly less than 24, 24:00 is not valid).
But time works like a modulos operation,

I can say that 18:00 + 7 hours = 01:00

But I can not ask for the interval between 18:00 and 01:00. It might
be 7 hours. It might be 17 hours, it might be 31 hours etc etc.

If you want to talk about intervals, you need to talk about Timestamps.

I can say that the interval between 2005-1-1 18:00 and 2005-1-2 01:00
is exactly 7 hours, and no other answer is correct.

Where some people get confused is that we usually choose to express
intervals in hh:mm format, so we expect to see the interval being
07:00. Note that this does not mean we are adding 7am to 2005-1-1 18:00.

Then comes the real fun part about times. The world is round, so only
part of the world is in sunlight at any instance time, hence the
concept of Timezones. So obviously my math above returns the correct
result only in a single timezone. To standardise this a bit, we have
GMT (also called UTC), and other timezones are measured as an "offset"
to GMT. (GB was obviously the superpower at the time.) But as long as
we have a standard like this, we can meaningfully discuss times with
people on the other side of the world without confusion. Secondly, the
world is not just spinning around, its axis is on a tilt to the sun,
meaning that we get more daylight in summer than winter. To take
advantage of this, many countries have the concept of daylight saving,
where an additional hour is added to the time called a "bias" during
the summer months. So again, the math will fail if the first time is
not DST but adding the interval to the first time places it into DST.

Of course, there is more than just the single calendar around. But
even in the calendar that says this is october 2005, there have been
various changes, some countries agreeing to add special leap days,
others refusing to and ending up being out by a month at some point in
time.

Firebird, like many other tools work with dates the same way as
This probably explains the old MySQL accepting 31 Feb as a valid date.
On a business logic level, it makes no sense, but on an implementation
level, it may well.

Where we could probably improve Firebird is by adding UTC support to
the variables 'today', 'now' etc.

Something like

select NOWUTC
from RDB\$DATABASE;

I live in a country that has three different timezones. Even major
states within that country have differing times because some do not
use daylight savings. So in my country, you can literally have people
thinking that 'now' is four different times, so we have to implement
UTC in a clunky UDF, but an internal function would definately be
preferable.