hi,
let say, each record have two fields of Time type. One for start time and one for end
time. I would like to calculate hours and minutes inbetween.
How do I form a sql statement?
XP, D6, dbisam 4.12

andrej


In DBISAM you can simply subtract the two time fields to give milliseconds. After that it becomes a bit more tricky.

((Time2-Time1) / 60000) gives minutes

((Time2-Time1) / 60000) / 60 gives hours

So

SELECT
TRUNC(((Time2-Time1) / 60000) / 60) AS HOURS ,
((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60) AS MINUTES
FROM ....

Assuming you only ever enter hours and minutes otherwise you'll have to handle the seconds and milliseconds yourself

Roy Lambert [Team Elevate]

tnx for your reply and it is, of course, working. BUT. What if Time1 is, let  say, 23:30,
and Time2 is, let say 00:35?
In other words, if both Time1 and Time2 are not from the same day?

andrej





Roy Lambert <roy.lambert@skynet.co.uk> wrote:

andrej


In DBISAM you can simply subtract the two time fields to give milliseconds. After that it
becomes a bit more tricky.

((Time2-Time1) / 60000) gives minutes

((Time2-Time1) / 60000) / 60 gives hours

So

SELECT
TRUNC(((Time2-Time1) / 60000) / 60) AS HOURS ,
((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60) AS MINUTES
FROM ....

Assuming you only ever enter hours and minutes otherwise you'll have to handle the seconds
and milliseconds yourself

Roy Lambert [Team Elevate]

and another thing. I would like to SUM this hours and minutes according to a certain key.
Let say date. I would like to sum hours and minutes inside each day?
please, advise
andrej


Roy Lambert <roy.lambert@skynet.co.uk> wrote:

andrej


In DBISAM you can simply subtract the two time fields to give milliseconds. After that it
becomes a bit more tricky.

((Time2-Time1) / 60000) gives minutes

((Time2-Time1) / 60000) / 60 gives hours

So

SELECT
TRUNC(((Time2-Time1) / 60000) / 60) AS HOURS ,
((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60) AS MINUTES
FROM ....

Assuming you only ever enter hours and minutes otherwise you'll have to handle the seconds
and milliseconds yourself

Roy Lambert [Team Elevate]

"andrej" <andrej.bivic@abitrade.si> wrote in message
news:BAF719E4-0D0F-4319-8B81-F05B7241B1D5@news.elevatesoft.com...
> tnx for your reply and it is, of course, working. BUT. What if Time1 is,
> let  say, 23:30,
> and Time2 is, let say 00:35?
> In other words, if both Time1 and Time2 are not from the same day?
>

If you know that if time2 is less than time1 it is the next day (IOW, that
there can not be more than 24 hrs between the two times) just add 86400000
to time2.

SELECT
TRUNC(((if (Time2 < Time1 then (Time2 + 86400000 - Time1) else
(Time2-Time1))) / 60000) / 60) AS HOURS ,

etc.

Robert

Yes, yes! This is realy working!!
Can you, please, write down also for minutes? Please.

andrej


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"andrej" <andrej.bivic@abitrade.si> wrote in message
news:BAF719E4-0D0F-4319-8B81-F05B7241B1D5@news.elevatesoft.com...
> tnx for your reply and it is, of course, working. BUT. What if Time1 is,
> let  say, 23:30,
> and Time2 is, let say 00:35?
> In other words, if both Time1 and Time2 are not from the same day?
>

If you know that if time2 is less than time1 it is the next day (IOW, that
there can not be more than 24 hrs between the two times) just add 86400000
to time2.

SELECT
TRUNC(((if (Time2 < Time1 then (Time2 + 86400000 - Time1) else
(Time2-Time1))) / 60000) / 60) AS HOURS ,

etc.

Robert

andrej

>and another thing. I would like to SUM this hours and minutes according to a certain key.
>Let say date. I would like to sum hours and minutes inside each day?
>please, advise

You'll have to use a script and select into a memory table to do this. Simply SUMing the HOURS and MINUTES will be ok for the hours but you'll probably end up with a minutes value in excess of 60 and you can't carry out calculations on the SUM.

So something like

SELECT
DATEFIELD,
SUM(TRUNC(((Time2-Time1) / 60000) / 60)) AS HOURS ,
SUM(((Time2-Time1) / 60000) - 60 * TRUNC(((Time2-Time1) / 60000) / 60)) AS MINUTES
INTO "Memory\handm"
FROM table
GROUP BY DATEFIELD;

SELECT
DATEFIELD,
HOURS + TRUNC(MINUTES / 60) AS HOURS,
MINUTES - 60*TRUNC(MINUTES / 60) AS MINUTES
FROM "Memory\Temp";

Totally untested.

Also remember to drop the memory table when you've finished with it.

Roy Lambert [Team Elevate]

Robert, can you please write me a query for MINUTES like this one below?

If you know that if time2 is less than time1 it is the next day (IOW, that
there can not be more than 24 hrs between the two times) just add 86400000
to time2.

SELECT
TRUNC(((if (Time2 < Time1 then (Time2 + 86400000 - Time1) else
(Time2-Time1))) / 60000) / 60) AS HOURS ,

etc.

Robert

"andrej" <andrej.bivic@abitrade.si> wrote in message
news:99014C92-E443-4DC6-BA19-F05126E15499@news.elevatesoft.com...
> Yes, yes! This is realy working!!

Calm down

> Can you, please, write down also for minutes? Please.
>

Every place where you have "(Time2 - Time1)", replace with "if (Time2 <
Time1 then (Time2 + 86400000 - Time1) else
(Time2-Time1))"

Robert

Tnx

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"andrej" <andrej.bivic@abitrade.si> wrote in message
news:99014C92-E443-4DC6-BA19-F05126E15499@news.elevatesoft.com...
> Yes, yes! This is realy working!!

Calm down

> Can you, please, write down also for minutes? Please.
>

Every place where you have "(Time2 - Time1)", replace with "if (Time2 <
Time1 then (Time2 + 86400000 - Time1) else
(Time2-Time1))"

Robert