Subject Re: Adding time to date
Author Svein Erling Tysvær
--- In, Raigo <raigo77@s...> wrote:
> I am glad to read, that you (Primoz) have similar application.
> I just wondered if you have to separate those hours somehow by time?
> For example I have to show how many of those hours was between 6-18
> (daily fee), 18-22 (evening fee), 22-6 (nightly fee). Result would
> be something like this
> 16.09.2004 16:00-24:00 (total 8 hours; daily 2 hours(16-18);
> evening 4 hours(18-22), nightly 2 hours(22-24)).
> How to best calculate this in SQL?

Is this possible to do through SQL? Probably, though I don't know.

Is this sensible to do through SQL? Only if trying to convince
somebody that you really master SQL.

Anyway, I think the result would be a query with several subselects
that is difficult to read and maybe even error prone (you have to take
into account those that start within the fee period, those that stops
within the fee period, those that both starts and stops within the fee
period, those that span the entire fee period without starting or
stopping as well as those that encapsulates both the end and beginning
of a period (consider those that start at 5am and finishes 23pm)).

For those of us that are a bit more lazy, writing a stored procedure
would be the easy way to do this (or even a trigger).