Solved

Adding time in hours and minutes in cold fusion

Posted on 2013-01-23
6
343 Views
Last Modified: 2013-02-04
I have a file that querys the database that pulls the starttime and endtime. I place the hours in a ValueList with a comma as the dilimmeter. I then convert the List to an array(ListToArray function) and then use the ArraySum function to add all the values in the array to get the sum then use timeformat to convert.  This works great as long as the value of the sum of the  hours does not go over 12. When the value goes over 12 then I get the incorrect value.

for example.  I have one day at 8:00 and then a second day at 3:00. total hours converts correctly to 11:00. Then when I add a third day of say 4:00  hours then I get the following total hours of 3:00. which I think gets there by adding 8+3+4 = 15 and then subtracts 12 to get to 3. I am using CF 10 and MySQL database.

<!--- set variables --->
<cfset sdate = #dateformat(FORM.startDate, "yyyy-mm-dd")#>
<cfset edate = #dateformat(FORM.endDate, "yyyy-mm-dd")#>

<cfquery name="getEmpHours" datasource="#application.settings.dsn#">
      select emp_name, emp_id, sh_id, sh_emp_id, sh_date, sh_end_date, sh_hours
      from employee, shift_hours
      where sh_emp_id = emp_id
      and emp_id = '#FORM.emp_id#'
      and sh_date BETWEEN '#sdate#' AND '#edate#'
</cfquery>

<cfif #getEmpHours.RecordCount# GT 0>
      <cfoutput query="getEmpHours">
            <table>
                  <tr>
                        <td width="150px">
                        EMPLOYEE
                        </td>
                        <td width="150px">
                        DATE
                        </td>
                        <td width="150px">
                        HOURS
                        </td>
                  </tr>      
                  <tr>
                        <td width="150px">
                        #emp_name#
                        </td>
                        <td width="150px">
                        #left(sh_date, 10)#
                        </td>
                        <td width="150px">
                        #timeformat(sh_hours, "hh:mm")#
                                                </td>
                  </tr>
            </table>
            <table>
                  <tr>
                        <td width="150px">
                              TOTAL HOURS
                        </td>
                  </tr>
                  <tr>
                        <td width="150px">
                              <cfset shHourValueList = #valueList(getEmpHours.sh_hours, ",")#>
                              
                              
                              #timeFormat(ArraySum(ListToArray(shHourValueList)), "hh:mm")#
                        </td>
                  </tr>
            </table>
            </cfoutput>
      <cfelse>
            "No records are found!"
</cfif>
0
Comment
Question by:dxladner
  • 4
  • 2
6 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38810365
I don't know if ArraySum will handle times the way you expect.  What is the data type of the starttime column? Output #shHourValueList# what is the value? I suspect its actually 0...

TimeFormat is designed to display time of day on a 12/24hr clock. Even if you're passing in the right value, it won't display values > 24 properly.  But if you convert the time to minutes, a simple formula will give you "x hours and y minutes":

       <cfset totalHours = int(totalTimeInMinutes / 60)>
       <cfset totalMinutes = totalTimeInMinutes mod 60>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38810399
Small note, you don't need dateFormat (it's meant for presentation anyway). Also, always use cfqueryparam to avoid sql injection attacks. They're everywhere these days ...


...
AND emp_id = <cfqueryparam value="#FORM.emp_id#" cfsqltype="cf_sql_varchar">
AND sh_date BETWEEN <cfqueryparam value="#FORM.startDate#" cfsqltype="cf_sql_date">
 AND <cfqueryparam value="#FORM.endDate#" cfsqltype="cf_sql_date">

Open in new window

0
 

Author Comment

by:dxladner
ID: 38811209
the mysql column type for the dates are:     datetime

also here is the cfdump for the shHourValueList:

03:00:00,08:00:00,04:00:00,

this is for a person that has times for 4 days, day 1 = 3:00, day 2 = 8:00 and day 3 = 4:00. The total hours that are calculated form these values is 3:00 which is incorrect.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38811405
You can't use arraySum on datetime values.  It's designed for numbers, not dates.  If you check the result it's something weird like 0.62.  

Also, you can't use TimeFormat either because it's designed for a 24hr clock - not a duration or time span.

Convert the values to minutes in your sql. Then calculate the number of hours and minutes like this:

SELECT   ( Hour(sh_hours) * 60 ) + Minute(sh_hours) AS TimeInMinutes
....

<cfset totalTime = arraySum(listToArray(valueList(yourQuery.TimeInMinutes)))>
<cfset totalHours = int(totalTime / 60)>
<cfset totalMinutes = totalTime mod 60>

<cfoutput>
TimeInMinutes =  #TimeInMinutes#<br>
Total #totalHours# hrs #totalMinutes# mins
</cfoutput>
0
 

Author Closing Comment

by:dxladner
ID: 38847993
_agx_,

thanks for the help. This works great!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38851467
Welcome :)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question