Solved

Adding time in hours and minutes in cold fusion

Posted on 2013-01-23
6
349 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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