Subject Re: [firebird-support] How to do Time calculation
Author Vishal Tiwari
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).
This time period is required in the hours and minutes, for each employee 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
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

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


The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.

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