Hello Helen and others,

I am really sorry. Now I understood the matter. Initially I got confused with Time stamp data as I was not getting the required values when I take difference between two time stamp values. That is why I decided to use date and time separately.

Now I am going to use only time stamp.

By the way, the difference you get between two time stamp values is in days (helen, you had indicated that the difference should be divid by 24, but it should be multiplied by 24).

Also this is the raw data and we have to process this data with all possible errors and rules.

Thanks again to all.

Vishal

--- On Sat, 19/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: Saturday, 19 December, 2009, 10:34 AM







 









At 01:42 AM 19/12/2009, Vishal Tiwari wrote:



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



That doesn't make sense. If an employee has multiple periods of working throughout a day then you will need multiple "start-stop" records per employee, one for each period. These have to be timestamps, since a night-shift worker will certainly have periods that start on one *date* and finish on another *date*.



>because i don't know how many times employee might have gone out side and again came in for the same day.



If you cannot capture that information (time-clock, hours book, etc.) then where would that information come from?



Your "clock in and clock out" records *are* the data you will need to work with. Your system must protect itself from events such as

-- employees that clock in but do not clock out (or vice versa)

-- employees that come in through the window and bypass the clock-in

-- clocks/computers/ employees that break down



etc., etc.



So what I am telling you is that you have to DESIGN a time recording system that will work for people, hardware and software and is capable of highlighting any kinds of wrong behaviour.



>so, as per this i think one date and one time column is enough.



Enough for what?



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



My opinion is that you need to pay attention to what people are telling you and give a comprehensible description of your requirements. We have told you that TIME means "time of day" and cannot be used to record an interval of time in e.g. hours and minutes. IT DOES NOT AND CANNOT WORK THAT WAY. The Lords of the SQL Standard ordain it so.



If you believe that a record consisting of just a date and a number of hours worked is in some way useful for what you want to do, then make that third field a SMALLINT representing minutes worked (calculated by a guy standing by the door with a stopwatch and a shotgun) or maybe a DECIMAL(9,4) which gives you a way to record hours and part-hours.



If you genuinely want useful answers, ask technically sensible questions. Try reading this:



http://www.catb org/~esr/ faqs/smart- questions. html



./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]