Stupid Time Tricks 2

[Scott24x7]

All,
I have been away from VFP development for about 5 years. I'm back working on some things, and something that I thought should be fairly easy/straight forward is becoming really elusive.
What I'm seeking is general guidance here on the best approach to dealing with time calculations. I'm working on a "time recording" system, which will determine when a person works over-time (which is either 8 hours in the same day, or more than 40 hours in a week...) Late night (work done between 2200 and 0500. And then also weekend rate, which is the same as above, but occurring on Sunday. (I can work that part out, but thought I'd give the big picture).
The thing I find is if dealing with time such as 1330 1400 if I just "subtract" these two values, I get 70... because dealing with a partial hour is 60 minutes, not based on 100... pulling the two pieces apart to hours and minutes gets really messy, when there is something like 0945 - 1915 involved...
I'm sure other must have had to deal with manipulating time in the past. The DateTime functions seem totally lacking. It seems to be better to deal with these as character values, and manipulate them with some "time" functions instead. Does anyone have any elegant/creative/interesting ideas on doing this?
Many thanks!

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Oh, sorry, should have mentioned, using VFP9 with SP2 and Sedna add ons.

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

What's the problem with Time functions?

? (Datetime(2000,1,1,14,00,00)-Dattime(2000,1,1,13,30,00))/60

It *is* much easier to calculate with Datetimes than with character strings of (date)Times.

Also what comes in automatic - turnover at midnight dow() gives you the weekday etc.

I wouldn't want to fiddle around with char times.

Bye, Olaf.

 

[Scott24x7]

Olaf,
Thanks for the reply. Part of the problem is there are 6 time entries... Leavetime, StartTime, Finishtime, Break, OTBreak, EXTBreak. These all come from a source that are just text entries, such as 12:35 19:40, etc. They don't have the date attached to them. The date is in another field, which originates as a text entry, but I import it into a date field, which converts automatically for me. Do you suggest that I append the date to the times, and store them in datetime fields, and then calculate based on that?
This is also complicated by the fact that there are often multiple entries for the same day. (More than one client, and each gets its own entry... but for calculating the persons pay based on "overtime" rates, I have to gather up all the time seperately... but that's another matter...) Anyway, is this the best approach given what I mention above?

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

You'll have to make sure, that you don't mix up times with wrong dates. Eg if the seperate date of a record you talk of is some day and a time entered after midnight rather belongs to the next date, then simply adding that date will of course yield wrong results in calculating.

Besides that, yes, I'd rather compose Datetime() values from the date and time and calculate on these instead of figuring out mathlympics formulas for time strings.

Bye, Olaf.

 

[Scott24x7]

Olaf,
You hit one problem I have as well... if someone dispatches at say 8:00pm (20:00) and works until 2:00am to fix the problem, the system has them enter the start time as 20:00 and the finish time as 26:00. In theory, this is "one work day", and if they did something from say Noon to 5:00pm that day, it those 5 hours are regular time, from 20:00 to 22:00 is regular time, 22:00 to 23:00 is Latenight, from 23:00 to 26:00 is Late Night Over Time...
Any suggestion for dealing with this? This is very complicated arrangment... And I can't build DATETIME() from values that are after 23:59... or they are invalid.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

New insights...

But then you could easily assign the real date to the times, by subtracting 24:00 and adding 1 to the date.

Still, this sounds as if you already have logic built upon these unusual times >24. Eg I assume you determine "Latenight" and/or "Over Time" by these values, it's easier to compare time>'26' than >'02', because normal times are >'02' too.

If you have times in this unusual format this makes computing differences in strings less painful of course. Something like:

? MinuteDifference("1130","1130")
? MinuteDifference("1130","1150")
? MinuteDifference("2330","2500")
? MinuteDifference("1150","1130")
? MinuteDifference("1350","1155")

Function MinuteDifference()
   Lparameters tcTime1, tcTime2
 
   LOCAL lcHour1, lcHour2, lcMin2, lcMin2

   lcHour1 = LEFT(tcTime1,2)
   lcHour2 = LEFT(tcTime2,2)
   lcMin1 = RIGHT(tcTime1,2)
   lcMin2 = RIGHT(tcTime2,2)

   Return (Val(lcHour2)-Val(lcHour1))*60+Val(lcMin2)-Val(lcMin1)
Endfunc

Bye, Olaf.

 

[alvechurchdata]

Any suggestion for dealing with this?

Give up with the date and time data types and just store everything as "seconds since 01-01-2000". You'll have to write a few functions to convert to and from your users' odd display requirements but all the arithmetical problems will vanish. No problem with changes of day, month or year or whether or not it's daylight saving. Just subtract one number from the other.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Scott24x7]

Olaf,
Thanks. Got it working. The trick was doing some calculation to increment the date by + 1 when the "ending hour" is greater than 23. (24 - 24 = 0, and 24 - 27 = 3, which is 3am in this case...) so when that happens, I add 1 to the date, and then subtract 24 from the number, add the minutes back on, and like magic, works!
Still a lot of work to do to make this work, but this was a big hurdle I'm finally past.
Many thanks!


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[cbasoz]

Scott,
Date/Datetime conversion and calculation functions are rich. To calculate those you need to have at least an entry date. If a person cannot work 24 hrs or more then only entry date is enough. Suppose your fields are:

dStart: date
tStart: you have integers as I understand like 2000, 1400 ...
tEnd: same as tStart can go over 2359 such as 2600

Here are simple conversions:

RAND(-1)
Create Cursor myTable (dStart d, tStart i, tEnd i)
For ix = 1 To 10
tStart = Int(Rand()*23)*100 + Int(Rand()*3) *15
tEnd = m.tStart + Int(Rand()*12)*100
Insert Into myTable Values ( Date(2009,1,m.ix), m.tStart, m.tEnd)
ENDFOR
locate
BROWSE nowait


SELECT CDOW(dtStart), dtStart, dtEnd, ;
DOW(dtStart,2) > 5 OR DOW(dtEnd,2) > 5 as weekend ;
FROM (;
Select ;
CAST(dStart As Datetime) + ;
(Cast(Transform(tStart%2400,'@R 99:99') As Datetime) - ;
cast('0' As Datetime)) As dtStart, ;
CAST(dStart As Datetime) + ;
(Cast(Transform(tEnd%2400,'@R 99:99') As Datetime) - ;
cast('0' As Datetime) + Iif(tEnd > 2359,86400,0)) As dtEnd ;
FROM myTable) tmp



Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

I can image other urdles you have, eg time interval that are partly normal time, partly overtime. I'd cut such intervals into two intervals.

Regarding the weekend rate: DOW(date) will be 7 or 1 on the weekend, DOW(date,2) will be 6 and 7 on a weekend...

Bye, Olaf.

 

[alvechurchdata]

You can change the numbers assigned by DOW() but there's a gotcha. The SET FDOW command sounds as though it's what you'll need but you've got to specifically tell DOW() to pay attention by adding a second parameter:

?DOW(DATE())        && Gives us 4 on Wednesday
SET FDOW TO 2       && Make Monday the first day
?DOW(DATE())        && Still gives us 4
?DOW(DATE(), 0)     && Now we get 3

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

Yes, Geoff!

Or you simply use DOW(date,2) directly without using the FDOW setting at all.

Bye, Olaf.

 

[Scott24x7]

Actually, I just fixed this by using CDOW(), which always returns the name for the day it falls on (Sunday, Monday, etc.) While it's not a numeric, I don't need it to be, and is more readable in the code as to what is going on anyway. So, just another way to deal with that issue.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

Hi Maniac,

but you'd be surprised that CDOW() returns different names in other countries, when VFP takes eg the VFP9rDEU.DLL at runtime...

Not a problem, perhaps, but I just want to point this out, this is not reliable internationally, the numbers of DOW() are.

Bye, Olaf.