Datediff for Time Clock

[dogooder64012]

Hello.  I am having a problem putting my mind around how this works.  I am working on creating a time clock. When a person clocks in each day an entry is inserted into the database.  As they continue to clock in or out that day entry in the database is updated to log the time/date stamp up to 6 entries.

EmployeeID

TimeClockID

TimeEntryReg1

TimeEntryVac

TimeEntrySick

TimeEntryHol

TimeEntryOther

TimeEntryOtherReason

SPROverride

SPROverrideDate

TimeEntryReg2

TimeEntryReg3

TimeEntryReg4

TimeEntryReg5

TimeEntryReg6

10

1

11/12/2009 5:51:31 PM

NULL

NULL

NULL

NULL

NULL

NULL

NULL

NULL

When I call the query I can get the cfoutput to loop through each record and display each of the log in/out's.  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. I get an error every time that says my date is invalid. I am guessing that it is because the variable is unable to determine which record is which since there are multiple records.  Could someone PLEASE help me get my head around how to display the time worked correctly?  Thanks.

<table width="800">
<cfoutput query="selectPerEmployeeTimeClockEntry">
  <tr>
   <td width="50" nowrap>
    #DateFormat(TimeEntryReg1, "dddd mm/dd")#
   </td>
   <!--- <td width="50" nowrap>
    #TimeFormat(TimeEntryReg1, "hh:mm:sstt")#
   </td>
   <td width="50" nowrap>
    #TimeFormat(TimeEntryReg2, "hh:mm:ss tt")#
   </td>
   <td width="50" nowrap>
    #TimeFormat(TimeEntryReg3, "hh:mm:ss tt")#
   </td>
   <td width="50" nowrap>
    #TimeFormat(TimeEntryReg4, "hh:mm:ss tt")#
   </td>
   <td width="50" nowrap>
    #TimeFormat(TimeEntryReg5, "hh:mm:ss tt")#
   </td>
   <td width="50" nowrap>
    #TimeFormat(TimeEntryReg6, "hh:mm:ss tt")#
   </td>

[ilssac]

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

EmployeeID	Date	Starttime	Endtime
123	1/1/2001	10:30	12:00
123	1/1/2001	12:30	14:00
123	1/1/2001	14:15	16:00
123	1/2/2001	8:00	10:30

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.

[BKBK]

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>

[TLC-IT]

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.

[ilssac]

TLC-IT wrote:

After all, people do clock-out "after midnight."  (And I'm entirely sure that Ian would agree with that.)

You JUST HAD to make me remember the year that I worked two graveyard shifts from 8pm to 4am. While at the same time working two day shifts (8am to 4pm) and one swing shift (12:00 noon to 8pm) every WEEK.  Goodness that was a hell of a year.

[BKBK]

ilssac wrote:

TLC-IT wrote:

After all, people do clock-out "after midnight."  (And I'm entirely sure that Ian would agree with that.)

You JUST HAD to make me remember the year that I worked two graveyard shifts from 8pm to 4am. While at the same time working two day shifts (8am to 4pm) and one swing shift (12:00 noon to 8pm) every WEEK.  Goodness that was a hell of a year.

You're responding to post that is 18 months old! Even then you're not providing an answer or something of the sort. What the heck is going on here, man?

[ilssac]

BKBK wrote:

What the heck is going on here, man?

The Jive confusion factor.  I got an email, directing to this post.  It struck a note and I provided an answer.  Then I noticed that the thread is a year and a half old and way down at the bottom there is a new post.

[dogooder64012]

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:

	Employee ID	TimeClockID	TimeEntryReg1IN	TimeEntryReg2
	10	1	11/12/2009 17:51	11/12/2009 19:51
	10	2	11/12/2009 20:17	11/12/2009 22:17

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.

[Dan_Bracuk]

One problem at a time.  We'll do the interval between clocking in and out.

Is TimeEntryReg1IN to time in and TimeEntryReg2 the time out?  What data type are they in your db?  What db software are you using?

Different db's have different ways of calculating these intervals.  You can look your's up by googling "your db date functions".

[dogooder64012]

Yes, the column "TimeEntryReg1IN" is to time in and "TimeEntryReg2" is to time out. They both have the data type of "datetime".  And, I am using "myLittleAdmin for MS-SQL Server".

I am using just a basic query to get started which is:

SELECT *
FROM TimeClock tc, Employee e
WHERE tc.EmployeeID = form.EmployeeID AND
      tc.EmployeeID = e.EmployeeID

Thanks for your help!

[Dan_Bracuk]

Here is the reference for ms sql date functions.  http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx

The one you want is datediff.

[dogooder64012]

I saw that documentation, but I figured that I would do the DateDiff outside of the SQL and in th CF code since I assume that the employee(s) would have multiple days worth of punches. My query would request all of the employees punches for a period of time and then I would display it to the auditor.

My roadblock(s) seems to be:

a) how do you get the a single days worth of entries to display in columns rather than in separate rows,

(i.e.)

     current display:

          Friday 11/13
          06:18:34 PM
          06:58:34 PM

          Friday 11/13
          07:19:00 PM
          10:19:00 PM

          Sunday 11/15
          11:31:03 AM
          01:45:03 PM

    and I would like to see:

     date                       Time In                Time Out            Hours                 Time In                  Time Out              Hours              Total

     Friday 11/13            06:18:34 PM         06:58:34 PM        0 hrs 40 mins       07:19:00 PM           10:19:00 PM          3 hrs 0 min       3 hrs 40 mins

     Sunday 11/15          11:31:03 AM         01:45:03 PM        2 hrs 14 mins                                                                                        2 hrs 14 mins

b) how to show the number of hours/mins/secs between a time in and a time out. [as seen above]

Thanks a bunch.

[ilssac]

dogooder64012 wrote:

I saw that documentation, but I figured that I would do the DateDiff outside of the SQL and in th CF code since I assume that the employee(s) would have multiple days worth of punches. My query would request all of the employees punches for a period of time and then I would display it to the auditor.

That has little to no implication on whether you do the calculation in the SQL our in the CFML.

To display the time data horizontally should not be hard.

If your recordset is constructed correctly, it would be a matter of nested <cfoutput...> tags utilizing proper "group" parameters.

What this will actually look like depends greatly on how your organize your recordset.

<cfoutput query="theTimeQry" group="employeeID">
  Output Employee data
  <cfoutput group="dateField">
    Output day data
    <cfoutput>
      Output time stamps.
    </cfoutput>
  </cfoutput>
</cfoutput>

Mix in the some HTML to create the desired layout, probably using tables, and that would give you a horzintal display.

[BKBK]

b) how to show the number of hours/mins/secs between a time in and a time out. [as seen above]

This question comes 4 days after I had answered it.

[ilssac]

SO would you like to do this in the SQL or CFML?

How accurate do you want the difference to be? To hour, the minute, the seccond, the millisecond?

SQL

SELECT DATEDIFF('ss',tc.TimeEntryReg1IN,tc.TimeEntryReg2) AS TimeDiffInSeconds,* -- you really should name columns here
FROM TimeClock tc, Employee e
WHERE tc.EmployeeID = form.EmployeeID AND
      tc.EmployeeID = e.EmployeeID

CFML

<cfoutput query="myTimeQry">

#datediff("s",myTimeQry.TimeEntryReg1IN,myTimeQry.TimeEntryReg2)# is time difference in seconds.

</cfoutput>

Now to format that into hours, minutes seconds you would take the time in seconds and divide by 3600 for hours and the remainder of that by 60 for minutes and the remainder of that is seconds.

Message was edited by: ianskinner    Wow, my offical 1000'th post!  What no streamers or balloons, oh well.

[Guest]

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

[BKBK]

soynv wrote:

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.

I would advise you to start your own thread. I am sure you will then have a better chance to get assistance.

[Adam Cameron.]

Yes: start a new thread.

However first you need to design your domain model: which entities you need to represent (eg: an employee, a time card, etc).  This could just be a class diagram or something like that.

Then you need to design what interactions you need between the model and the user: use case diagrams.

From that you'll get a fair idea of what the UI will need to do, as well as how the API should work.

Then go through some Model Glue (or similar framework) tutorials to get up to speed to how to build a website in a manageable and scalable fashion.

When you have problems with elements of the above, post here if appropriate, or a more specialised forum if there is one (eg: a Model Glue forum, a SQL Server forum, etc).

And don't expect people to give away their work, instead of doing it for yourself.  I presume you're being paid to do this?

--

Adam

[Guest]

Thanks for the direction and help. Sorry to bring up a completed thread.