Subject Re: [firebird-support] How to do Time calculation Vishal Tiwari 2009-12-18T14:25:30Z
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]