CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> ASP

 Adding Time Difference

Print topic Send  topic

Author Message
Dreamcatchers


Posts: 118
Posted: 03/05/2015, 3:34 PM

I am trying to accumulate the total time a user access a document. I store the current time in SQL Server as current_duration and trying to store the accumulated time as total_duration.
The total_duration never adds, it remains the same as the current_duration.

The SQL Server data type for both fields is Time(0).

Fields on the Form are set as Date with format as Short Time.
Record name Roster
StartTime = Now() from a session variable
Endtime = Now() when the user end up on final page
Duration = roster.duration.Value = roster.endtime.Value - roster.starttime.Value
Current_duration = roster.duration.value
total_duration = roster.total_duration.Value = roster.current_duration.Value + roster.duration.Value

I don't know what I'm missing. No errors, seems logical, result is always total_duration the same as current_duration.
_________________
Dream Catchers, Inc
ITIL Training and eLearning
www.itil.us.com
866-FOR-ITSM
View profile  Send private message
Dreamcatchers


Posts: 118
Posted: 03/05/2015, 3:49 PM

A simply analogy of what I am trying to accomplish is:

If Sally spent 1 hour 30 minutes on an activity yesterday and today she spent 15 minutes on the activity. I want to store in the database she has spent a total of 1 hour 45 minutes on the activity.
_________________
Dream Catchers, Inc
ITIL Training and eLearning
www.itil.us.com
866-FOR-ITSM
View profile  Send private message
eratech


Posts: 513
Posted: 03/05/2015, 4:15 PM

Hi

If I read the last 3 lines correctly, it appears you are setting the Duration to the End-Start time and then setting the Current_duration to that value, and so the Duration and Current Duration are the same, and the total_duration is then set to one of them.

Duration = roster.duration.Value = roster.endtime.Value - roster.starttime.Value  
Current_duration = roster.duration.value  
total_duration = roster.total_duration.Value = roster.current_duration.Value + roster.duration.Value 


I tend to use SQL queries to do that sort of summing and time calcs so that's an option if you aren't doing them on the screen.

Cheers

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
Dreamcatchers


Posts: 118
Posted: 03/05/2015, 5:37 PM

I check out some sql blogs and found recommendations to:
1. convert the time(0) value in sql to Datetime
2. do simple +/- operation on the values
3. Convert the result back to time(0) format
4. update the record

Doe this sound right to you?

I also found that by putting each of the values in a hidden field on my page and typing the fields to Date, then in custom code reference the fields and do the math, then do custom update to SQL it seems to work.

It all feels awkward and was hoping for something with a little more finesse.
_________________
Dream Catchers, Inc
ITIL Training and eLearning
www.itil.us.com
866-FOR-ITSM
View profile  Send private message
eratech


Posts: 513
Posted: 03/07/2015, 9:14 PM

Yes- the steps are correct, and I often put more into the SQL to do the conversions and calculations . MySQL and MSSQL have all the datetime functions so I tend to get them to do the hard work. You probably don't need to convert the time to datetime, just work in time with minutes.

For example, for a timesheet project, I did a view that took all the time in/out and lunch breaks, and calculated the number of minutes worked for the day (much easier to work in minutes than fractions of hours) and converted it out:

... isnull(convert(integer, (datediff(minute, entry_time, exit_time)-datediff(minute, lunchstart_time, lunchend_time)+adjust_minutes)),0) as worked_minutes...  
		

All the entry/ext/lunch values are stored in the table as TIME values, and there is an 'entry_date' DATE field for the day. The adjust_minutes was an extra adjustment whenever slight adjustments were needed.

So you could do a Custom SQL Update that updates the fields from the form (as usual), and does a calculation for the TOTAL time like above:

... isnull(convert(integer, datediff(minute, entry_time, exit_time)),0) ...  
		

The isnull is there because if the person hadn't checked out at end of day, it would propgate NULL through the calcs.

Then when you need to display the results in hours and minutes, something like below is what I've used (note the modulo division for the minutes)

 convert(varchar(4), (worked_minutes/60))+'hr ' + convert(varchar(4), (worked_minutes%60))+'min' AS total_hours

Hope that helps and doesn't confuse.

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
eratech


Posts: 513
Posted: 03/07/2015, 9:21 PM

Alternatively, you could leave the form to save the values to the table, and if you are keeping a record for each day, and an overall total, you could do an After Update to sum the times up to now and put that into the current record's TOTAL field.

It separates the time calc away from the record Update, and does it in 1 step via SQL.

Just a thought.

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
Dreamcatchers


Posts: 118
Posted: 03/09/2015, 3:52 PM

Good input, I appreciate that. I got my code to work only to discover my design was wrong. Using a Time() data type in SQL was nifty and saved some space. As I tested I discovered that once my accumulated time exceeded 24 hours the time value started over at zero. So I switch to regular DateTime2(0) format.

In an event Before update I ran the following:
  
dim Session_time, session_total, exit_count, newtime, addtime  
exit_count = roster.exit_count.Value  
session_time = cdate(roster.session_time.Value)  
session_total = cdate(roster.session_total.Value)  
addtime = cdate(roster.addtime.Value)  
  
'-- Current Accumulated time  
roster.session_time.Value = addtime  
roster.session_total.Value = session_total + addtime  
  
'-- Calculate Average time  
if exit_count = 0 then  
	exit_count = 1  
End if  
roster.session_average.Value = session_total / exit_count  

That does the calculation and posts everthing in the database just fine.
Since there is no date the beginning date of 12/30/1899 is entered in the database.

So when I want to display HH:MM where hours can be any number up to 1000, I put in a before show event for the total and average times the following (display field type is txt):

  
dim hours, minutes  
hours = datediff("h", "12/30/1899", roster.session_total.Value)  
minutes = datediff("n", "12/30/1899", roster.session_total.Value) - (hours*60)  
roster.datdeff2word.Value = hours & ":" & minutes  

This works just the way I wanted, if a person spent 36 hours and 15 minutes. There time is stored in the database as a date with only accumulated time and is presented on the screen as 36:15

My only fear is if the base date of the system might be different in some circumstances.
_________________
Dream Catchers, Inc
ITIL Training and eLearning
www.itil.us.com
866-FOR-ITSM
View profile  Send private message
Dreamcatchers


Posts: 118
Posted: 03/09/2015, 3:52 PM

This also allows me to run reports and total the times.
_________________
Dream Catchers, Inc
ITIL Training and eLearning
www.itil.us.com
866-FOR-ITSM
View profile  Send private message
eratech


Posts: 513
Posted: 03/09/2015, 5:08 PM

Excellent. Glad it worked out as you wanted.

I haven't run into the accumulated time exceeding 24 as I've only been dealing with worked hours in a Day so < 24 always.

Cheers

E


_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright 2003-2004 by UltraApps.com  and YesSoftware, Inc.