Solved

Adding time in hours and minutes in cold fusion

Posted on 2013-01-23
6
355 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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