Subject Re: [firebird-support] How to do Time calculation
Author Helen Borrie
At 10:43 PM 18/12/2009, you wrote:
>Hi All,
>I have one small question(doubt) that how to subtract time.
>Let me explain in detail:
>I have one table, say "Test_Data" and I have three fields as follows:
>1) EmpNo Varchar(15)
>2) dtDate Date
>3) tmTime Time
>This table will contain entry time as well as exit time for a perticual day for a perticular employee. Now here i want to calculate the total time employees worked for given two different dates or two same dates, how do i achieve this.

In addition to what others have said, be clear in your head that the TIME type is "time of day" (by the 24-hour clock), not an interval of time.

You cannot store intervals of time in a TIME type.

You *calculate* intervals of time by subtracting an earlier TIMESTAMP from a later one. Thus, for your simplified example

1) unique key, such as a generated BigInt, make it the primary key
2) EmpNo Varchar(15)
3) tsStartTime timestamp not null
4) tsEndTime timestamp not null
5) HoursWorked computed by (cast((tsEndTime - tsStartTime)/24) as decimal(9,4))