Hi Helen,
 
Thanks for replay.
 
But let me explain once again :
 
Say for example one employee's entry and exit time may be multiple in a day that's
why I need only one date and time column based on this i will do rest calculation.
 
because i don't know how many times employee might have gone out side and again came in for the same day. so, as per this i think one date and one time column is enough.
 
 
Now as per following columns how i will calculate the total time employee had worked for a day (i.e. for each day)
 
1) EmpNo Varchar(15)
2) dtDate Date
3) tmTime Time

 
Plz. let me know your opinion.
 
Thanks for replay.
 
Vishal Tiwari... 

--- On Fri, 18/12/09, Helen Borrie <helebor@...> wrote:


From: Helen Borrie <helebor@...>
Subject: Re: [firebird-support] How to do Time calculation
To: firebird-support@yahoogroups.com
Date: Friday, 18 December, 2009, 7:18 PM


 



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

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

./heLen









The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/

[Non-text portions of this message have been removed]