Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Adding time in hours and minutes in cold fusion

Posted on 2013-01-23
6
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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