Time format saved a character field

Time format saved a character field

• andreskruse

  Hi,

  Im the table i have this character field "cnumwork" this is where the number of hours worked is saved. I have to compute this "04:10:00", this is the number of hours worked by an employee in the morning (4 hours 10 minutes) and "05:10:00" is the number of hours worked in the afternoon (5 hours and 10 minutes). I have to compute the "04:10:00" and "05:10:00" to get the total number of hours worked for the day for an employee.

  Thank u

  
  
  

• AnandMVP

  You have all the needed tools in the previous responses already, especially Cetin's Time2Str() function.

  It sounds like you have a bad table design. If the fields are used for match they probably should not be char but numeric (number of seconds).

  Staying with you char time fields, all you need are the functions already provided to convert to numeric number of seconds, do the math and convert back to text (HHH:MM:SS) for storage.

  Just read the samples carefully.

  
  

• hologram

  There are several ways, for example converting the time string to seconds multipliying each portion by 60 then 60*60, etc.

  A shorter way is to convert to a DateTime type then do DateTime math.

  E.g.

  x="05:32:44"
  x=Strtran(x,":",",")
  x="Datetime(1900,1,1,"+x+")"
  x=Evaluate(x)
  

  
  

• crazy

  You have all the tools in the functions Cetin and I gave you. You can join them as in this simple example (no error checking done and the function is done step by step for clarity but could be shortened).

  lcTimeIn="05:07:04"

  lctimeOut="09:00:00"

  TimeDiff(lcTimeIn,lcTimeOut)

  Return

  Function TimeDiff(tcTimeIn,tcTimeOut)
    Local lcIn, lcOut, ltIn, ltOut

    * convert colons to commas for DateTime()
    lcIn = Strtran(tcTimeIn,":",",")
    lcOut = Strtran(tcTimeOut,":",",")

    * make them DateTime types and subtract times

    ltIn = Evaluate("Datetime(1900,1,1,"+lcIn+")")
    ltOut = Evaluate("Datetime(1900,1,1,"+lcOut+")")

    * obtain difference in seconds and convert to time string
    Return Seconds2TimeStr(ltOut - ltIn)
  EndFunc
  

  Function Seconds2TimeStr(tnSeconds)
    Local lcTimeStr
    Text to m.lcTimeStr textmerge noshow
  <<Int(m.tnSeconds/3600)>>:<<Int((m.tnSeconds%3600)/60)>>:<<Int(m.tnSeconds%60)>>
    EndText
    Return m.lcTimeStr
  EndFunc

  
   

  
  

• DaveyC11111

  Hi Alex,

  How do you do:) My first thought was also datetime math but regretted thinking he might have stored values like 123:45:00. Otherwise it might be as short as:

  select sum( ctot(myTimeField) - ctot('0') ) as TotSeconds ...

  
  

• Dencore

  Sounds like a timetracker job. A very dangerous way to save in and out times. Assuming you don't care about the dangers:

  select sum(ctot(timeout) - ctot(timein)) from myTable into array aTotalSeconds
  Seconds2TimeStr(aTotalSeconds)

  
  

• kingofnexus

  Hi,

  I'm sorry if i haven't made my question clear and detailed. This is the situation:

  an employee with an id number of "001" has to punch in in the morning at "08:00:00" and punch out at lunch break at "12:00:00" and punch in again at "01:00:00" and punch out at "05:00:00" . For an employee there are two records saved in a table (1) the punched in time in the morning from "08:00:00" to "12:00:00" and (2) punch in in the afternoon from "01:00:00" to "05:00:00". What i am assigned to do is to compute the total number of hours worked for the day. I can get already the total number of hours worked in the morning and in the afternoon. In the example of time i h ave given there are four hours rendered in the morning (8:00:00 to 12:00:00) and another four in the afternoon (01:00:00 to 05:00:00). Now i have to compute the four hours in the morning and the four hours in the afternoon. As i said these records are saved as a character field in the table, the record is saved exactly like this format "08:00:00".

  Thank u

  
  
  

• GavinG

  You can use conversion functions like:

  Function TimeStr2Seconds
  Lparameters tcTime
  Local Array aTime[3]
  Alines(aTime,m.tcTime,.T.,':')
  Return Val(aTime[1]) * 3600 + ;
  Val(aTime[2]) * 60 + ;
  Val(aTime[3])

  Function Seconds2TimeStr
  Lparameters tnSeconds
  Local lcTimeStr
  Text to m.lcTimeStr textmerge noshow
  <<Int(m.tnSeconds/3600)>>:<<Int((m.tnSeconds%3600)/60)>>:<<Int(m.tnSeconds%60)>>
  EndText
  Return m.lcTimeStr

  ie:

  select sum(TimeStr2Seconds(myTimeField)) ;
  from myTable into array aSeconds
  if _Tally > 0 and !isnull(aSeconds)
  aSeconds, Seconds2TimeStr(aSeconds)
  endif

  It would be easier for you if you saved seconds instead from the start.

  
  

• Magic Hat 9

  Hi,

  Thanks for the response, the time that i am supposed to compute is like this:

  time in time out

  05:07:04 09:00:00

  05:32:44 09:01:01

  This is saved in a character field, i just have no idea how am i going to sum it up like a total for the time in and another total for the time out.

  Thank u

  
  

  
  
  
• Time format saved a character field