Time Manipulation

[izachar]

I have a field in the db that has a date/time stamp. I managed to pull the lowest and highest number which implies entry point and exit pointper user. I now want to find the time they spent in the site. I know I need to break the hh:mm:ss to be all seconds and then subtract and convert back to hh:mm:ss but I am haveing problems whith it. Here is my code:

DateVar strip_d:= DateTimeToTime ({InternetLog.LogTime});

DateVar start_time:= Minimum(strip_d,{InternetLog.ClientHost});

DateVar end_time:= Maximum(strip_d,{InternetLog.ClientHost});

NumberVar totalsec := end_time - start_time;

NumberVar hours := totalsec / 3600;

NumberVar mins := (hours - truncate(hours)) * 60;

NumberVar secs := (mins - truncate(mins)) *60;

StringVar HH := totext(truncate(hours));

StringVar MM := totext(truncate(mins));

StringVar SS := totext(round(secs, 0));

HH+":"+MM+":"+SS;

 

[konad]

I was using this to subtract my date/time table in my database from another date/time table in my database, and put the total into minutes, you will have to of course convert it back to what you need.

Datevar StatusCallTime := date({table.LastActionTime});
//declares and initializes variables for the start date,
Datevar CallTime := date({table.CallTime});
//end date and date difference converted to seconds

Numbervar SecondsBetweenDate := (StatusCallTime - CallTime) * 8640;

Numbervar SecBetweenTime := (Time({table.LastActionTime}) -
Time(0,0,0)) - (Time({table.CallTime}) - Time(0,0,0));
//calculates the difference between start and end times and
//assigns to a variable

SecondsBetweendate + SecBetweenTime / 60;
//adds two time variables

 

[izachar]

My problem is that I am working only out of one database and only one table and column that I need to break the entry, exit and the difference.

IZ

 

[dcm]

izachar: The solution is to use TimeVars rather than DateVars as these will automatically give you the resultant time between in seconds e.g.

TimeVar strip_t:=Time({InternetLog.LogTime});
TimeVar start_time:=Minimum(strip_t,{InternetLog.ClientHost});
TimeVar end_time:=Maximum(strip_t,{InternetLog.ClientHost});
Numbervar time_spent_in_seconds:=end_time-start_time;

Stringvar hours_spent:='';
Stringvar minutes_spent:='';
Stringvar seconds_spent:='';
Stringvar time_spent:='';

If time_spent_in_seconds>60*60 then
hours_spent:=totext(truncate(time_spent_in_seconds/60*60),0)+' hour'+ if truncate(time_spent_in_seconds/60*60)>1 then 's ' else ' '
Else
hours_spent:='';

If time_spent_in_seconds>60 then
If time_spent_in_seconds>60*60 then
minutes_spent:=totext(remainder (time_spent_in_seconds,60*60),0)+' minute'+ if remainder (time_spent_in_seconds,60*60)>1 then 's ' else ' '
Else
minutes_spent:=totext(truncate(time_spent_in_seconds/60),0)+' minute'+ if truncate(time_spent_in_seconds/60)>1 then 's' else ' '
Else
minutes+spent:='';

If time_spent_in_seconds>60 then
seconds_spent:=totext(remainder(time_spent_in_seconds,60),0)+' second'+ if remainder(time_spent_in_seconds,60)>1 then 's' else ''
Else
seconds_spent:=totext(time_spent_in_seconds,0)+' second'+ if time_spent_in_seconds>1 then 's' else '';

Time_spent:=hours_spent+minutes_spent+seconds_spent

Hope this helps

David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner