• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Adding time in hours and minutes in cold fusion

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
dxladner
Asked:
dxladner
  • 4
  • 2
1 Solution
 
_agx_Commented:
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
 
_agx_Commented:
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
 
dxladnerAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
_agx_Commented:
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
 
dxladnerAuthor Commented:
_agx_,

thanks for the help. This works great!
0
 
_agx_Commented:
Welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now