Datediff for Time Clock

Hi dogooder64012

I'm new to adobe and i'm trying to create a time stamping login logout form for employees so i can track their time.

The old fashioned clunky time clock that punches the card is dying.

Would it be possibe to email or show me a sample of how you got your's to work?

It would help out a lot.

if anyone else has a sample or point me in the right direction that would be awesome also.

Thank you!

soynv@yahoo.com

Hello.

Thanks for your responses. I made the database modifications so that the clock in entries are seperate from the clock out entries as see below:

However, I am still not able to get the data from the database to display in columns nor can I get the times to calculate in the number of hours/minutes there are between clocking in and out.

I appreciate any help you can provide.  Thanks.

I agree with the comment that the database-design could be considerably improved.

Each time the user punches-in, he creates a new punch-record, of a particular type (e.g. "sick," "holiday," "regular") with a specified starting-time and a NULL ending-time.  When he clocks out, we expect to find that the most-recent record has a NULL ending-time and so we update it.  (But the possibility of a "missing punch" must also be considered!)

The resulting single record describes one contiguous unit-of-time.  The table is indexed by {employee_id, start_timestamp} so that we can instantly locate the clock-in record to update when we clock-out.

I do offer the slightly-dissenting opinion that the "date" and "time" columns should be one... a "datetime" data-type that incorporates both the date and the time.  After all, people do clock-out "after midnight."  (And I'm entirely sure that Ian would agree with that.)

Also, of course, you'll use <cftransaction> tags to make the operations fully atomic.

What my problem is is that I am not able to do a datediff to figure out how many hours/mins that employee has worked between the clock in and out.

<!--- start and end times --->
<cfset start_time = timeformat("09:34:56", "hh:mm:sstt")>
<cfset end_time = timeformat("12:01:33", "hh:mm:sstt")>

<!--- to form datetime values, add today's date to start and end times --->
<cfset start_datetime = parsedatetime(dateformat(now(), "yyyy-mm-dd") & " " & start_time)>
<cfset end_datetime = parsedatetime(dateformat(now(), "yyyy-mm-dd") & " " & end_time)>

<!--- use datediff() to calculate difference between datetime values --->
<cfset time_diff_hr = datediff("h",start_datetime,end_datetime)>
<cfset time_diff_min = datediff("n",start_datetime,end_datetime)>
<cfset time_diff_sec = datediff("s",start_datetime,end_datetime)>

time difference in hours: <cfoutput>#time_diff_hr#</cfoutput><br>
time difference in minutes: <cfoutput>#time_diff_min#</cfoutput><br>
time difference in seconds: <cfoutput>#time_diff_sec#</cfoutput><br>

<!--- time difference in hours, minutes, seconds --->
<cfset hours = int(time_diff_sec/3600)>
<cfset minutes = int(time_diff_sec/60)-hours*60>
<cfset seconds = time_diff_sec - hours*3600 - minutes*60>

<br><br>
Time difference:<br>
hours: <cfoutput>#hours#</cfoutput><br>
minutes: <cfoutput>#minutes#</cfoutput><br>
seconds: <cfoutput>#seconds#</cfoutput>

Are we allowed to suggest a database design change to make this work much easier?

If you had a more normalized database with the start time and endtime for each work period as a seperate record rather then repeating fields like you currenlty have them.  Repeating fields are a sure sign of data that could be noralized.

Thus if you had a workPeriod table that looked something like this

Then it would be a fairly basic task to query this timeworked table join it to the employee and other relevant tables and find out how long an employee worked.