locked
Help on datetime calculating RRS feed

  • Question

  • I am trying to make a timekeeping program right now

    my goal with this question is to get the correct total values of a column called total hours which was calculated from timein and timeout in dd:hh:mm:ss or just hh:mm:ss. but for a specific userid

    here is my intial code
    it calculates the total hours per userid but the problem is the total time is wrong with my calculation

    example:
    user1 should be 58 hours for dates from 08/12 to 8/18 with a standard 8hr work day and 1 2hrs overtime but it displays 43:51:00 instead

    while user2 should be at 43 hours from date 8/12 to 8/16 with 1 2hr overtime and 1 1hr overtime but it displays
    48:52

    LOCAL lnTotal,lntottime,lnHrs,lnRemainder,lnMin ,lnSec,lcTime 
    STORE 0 TO lnTotal
    
    SELECT * FROM t WHERE t.userid=m.m_id AND !DELETED() INTO CURSOR cur_t
    SELECT cur_t
    lntottime=RECCOUNT()
    
    SELECT SUM(ROUND(timeout-timein,0)) AS totalhours FROM t;
    	WHERE t.userid=m.m_id AND !DELETED();
    	INTO CURSOR cur_tt
    
    IF !ISNULL(totalhours)
    	lnTotal=cur_tt.totalhours
    ELSE
    	STORE 0 TO lnTotal
    ENDIF
    lnSec = lnTotal
    
    lnHrs = lnSec/3600
    lnRemainder = mod(lnSec,3600)
    lnMin = lnRemainder/60
    lnSec = mod(lnRemainder,60)
    lcTime = padl(lnHrs,2,"0")+":"+padl(lnMin,2,"0")+":"+padl(lnSec,2,"0")
    THISFORM.ttotal.VALUE=lcTime 
    IDK What went wrong.


    • Edited by Rai_Ohki Friday, August 16, 2019 6:53 AM
    Friday, August 16, 2019 4:58 AM

Answers

  • Hi Rai_Ohki,

    I just build a small cursor for being able to simulate your problem.

    CREATE CURSOR crsTest( user c(10), log_in t, log_out t )

    I filled the cursor with some data:

    and ran your select against it. With one little difference, though. I wanted the correct sum values and not some rounded ones. I got absolutely correct values.

    SELECT SUM( log_out - log_in ) AS totalseconds FROM crstest iNTO CURSOR crsSum

    In my examples every day has a worktime of 28800 seconds (= 8h) and all five days together get 144000 seconds (=40h)

    If you indeed have correct timestamps, then you should remove the ROUND() function and check what real value you get without it. This real value then can be divides by 3600 and get FLOOR()ed or INT()ed.

    HTH


    Gruss / Best regards
    -Tom
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011


    • Edited by Tom BorgmannEditor Monday, August 19, 2019 8:07 AM type in seconds corrected
    • Marked as answer by Rai_Ohki Tuesday, August 20, 2019 7:20 AM
    Monday, August 19, 2019 6:56 AM
    Answerer
  • OK i think i got it working i just deleted all the values on my table and reentered them again and it became the correct total values . idk why it didn't work out in the first place but it works fine now.

    thank you for your help sir tom and sir pavel

    • Marked as answer by Rai_Ohki Monday, September 2, 2019 1:45 AM
    Tuesday, August 20, 2019 7:22 AM

All replies

  • Hi Rai_Ohki,

    based on the information you give on your saved timestamps, you won't be able to compute anything with them. VFP only can calculate hours with correct Datetime values. VFP doesn't know a field type called TIME but DATETIME and it contains YY.MM.DD.hh.mm.ss based values.

    If you want to calculate anything based on your semi timestamps in their current format, all you get are values based on 100 and not on 60. So you will have to convert each value into a correct Datetime value and THEN you can substract one from another.

    The following little code snipet should give you an idea on how to convert your data. This also means, you will have to calculate each records individually in a loop.

    LOCAL liValueStart as Integer, liValueEnd as Integer, ;
          ltStart as DateTime, ltEnd as DateTime
    
    liValueStart = 800
    liValueEnd   = 1645
    
    ltStart = DATETIME( YEAR( DATE() ) , 1 , 1 , FLOOR( liValueEnd / 100 ) , MOD( liValueStart ,100 ) , 0 )
    ltEnd = DATETIME( YEAR( DATE() ) , 1 , 1 , FLOOR( liValueEnd / 100 ) , MOD( liValueEnd , 100 ) , 0 )
    
    ? ( ltEnd - ltStart ) / 3600

    This code always uses January 1 as a base date.

    ADDED:

    it would be a lot better to save the logIn and logOut values as real DATETIME value in your table. That way you wouldn't have to convert the values an could work with them directly.

    HTH


    Gruss / Best regards
    -Tom
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011


    Friday, August 16, 2019 6:59 AM
    Answerer
  • Hi,

    Martina has just recently published a fine set of classes to work with date's / date-times, please download for GitHub: https://github.com/MJindrova/dtclass

    Regards,

    Koen


    Pensionado, VFP programer

    Friday, August 16, 2019 4:30 PM
  • To say what's wrong with the calculation we would need to see the data.

    If you just say "user1 should be 58 hours for dates from 08/12 to 8/18" how do we recognize the used date range was correct?

    Friday, August 16, 2019 9:11 PM
  • THE Aforementioned data values of time in and out is already at datetime value and i can already subtract timeout - timein to get the seconds and convert the total hours to just 00:00:00 the prob now is to total of all user1 or user2 total hours.
    Friday, August 16, 2019 10:54 PM
  • Time in and time out are already at datetime value and i can get the total hours for it the problem i have is total all of total hours.
    Friday, August 16, 2019 10:56 PM
  • OK, that's clear BUT if you are saying the sum should be 58 but the result from the code says 43:51 and the code does not look bad at the first view then we have no other possibility than to ask for sample data which will allow to recalculate the result.

    Export the data for one or two users into CSV and post them here as a code block.

    If you are sure the 58 hours is correct result then the answer is: The "t" table contains incorrect data.

    Saturday, August 17, 2019 9:17 AM
  • This is the time in and out of the first user

    8/12/19 6:30 am to 4:05 pm
    8/13/19 6:44 am to 4:04 pm
    8/14/19 6:30 am to 6:15 pm - 2hrs ot
    8/15/19 6:48 am to 4:05 pm
    8/16/19 6:46 am to 4:10 pm
    8/17/19 6:44 am to 4:05 pm
    8/18/19 6:55 am to 4:04 pm

    then this is the time in and out of the second user

    8/12/19 6:40 am to 4:02 pm
    8/13/19 6:55 am to 6:10 pm - 2 hrs ot
    8/14/19 7:00 am to 4:00 pm
    8/15/19 7:00 am to 5:00 pm - 1 hr ot
    8/16/19 6:55 am to 4:10 pm

    idk how to put it by code block so i just did it manually
    the time vary depending on login in biometrics

    both time in and time out are date time i just didn't write the date for the timeout cuz its the same

    but work time starts and ends at 7am to 4pm


    • Edited by Rai_Ohki Sunday, August 18, 2019 11:37 PM
    Sunday, August 18, 2019 11:36 PM
  • Hi Rai_Ohki,

    I just build a small cursor for being able to simulate your problem.

    CREATE CURSOR crsTest( user c(10), log_in t, log_out t )

    I filled the cursor with some data:

    and ran your select against it. With one little difference, though. I wanted the correct sum values and not some rounded ones. I got absolutely correct values.

    SELECT SUM( log_out - log_in ) AS totalseconds FROM crstest iNTO CURSOR crsSum

    In my examples every day has a worktime of 28800 seconds (= 8h) and all five days together get 144000 seconds (=40h)

    If you indeed have correct timestamps, then you should remove the ROUND() function and check what real value you get without it. This real value then can be divides by 3600 and get FLOOR()ed or INT()ed.

    HTH


    Gruss / Best regards
    -Tom
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011


    • Edited by Tom BorgmannEditor Monday, August 19, 2019 8:07 AM type in seconds corrected
    • Marked as answer by Rai_Ohki Tuesday, August 20, 2019 7:20 AM
    Monday, August 19, 2019 6:56 AM
    Answerer
  • SUM(ROUND(timeout-timein,0)) and SUM(timeout-timein)

    should be approximately equal if the timeout and timein are datetime columns (and if they contain correct data).

    Nothing explains the difference in hours except the possibility Rai_Ohki does not tell everything. And that's the reason I have been asking for sample data.

    Is it so difficult to post some CSV here?

    @Tom could you please explain where is the missing second in your calculation? 28799 seconds (= 8h) 

    It points to the ROUND()ing necessity.

    Monday, August 19, 2019 7:37 AM
  • LOL, sorry, I didn't notice my fingerslipping, it should read 28800. I slipped 1 pos to the left.

    Gruss / Best regards
    -Tom
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011

    Monday, August 19, 2019 8:06 AM
    Answerer
  • sorry i cant put pictures with csv in because i have a new account i already posted my data here but it was gone. what i got for tables are just userid /date / timein / timeout / OT / and total hours(in one day).


    • Edited by Rai_Ohki Tuesday, August 20, 2019 12:46 AM
    Monday, August 19, 2019 11:14 PM
  • sir i already removed the round but its still then same and then i copied and edited the code you posted but its still the same answer

    the values are not exact on the dot time because the data came from a bio-metrics scanner. so user1 login in on 6:30 am and time's out at 4:10 so those are my datatime values

    i have a 3rd column total hours where i already got my calculation to get the total hours in a day time in time out which is on 00:00:00 values could i get a total from just one column of my table like add all the total hours ?

    i do have a relation where employee.id = time.id
    so when i click on the first user in the grid it displays all the time of that user on a second grid on the same form.


    • Edited by Rai_Ohki Tuesday, August 20, 2019 12:43 AM
    Monday, August 19, 2019 11:28 PM
  • OK i think i got it working i just deleted all the values on my table and reentered them again and it became the correct total values . idk why it didn't work out in the first place but it works fine now.

    thank you for your help sir tom and sir pavel

    • Marked as answer by Rai_Ohki Monday, September 2, 2019 1:45 AM
    Tuesday, August 20, 2019 7:22 AM
  • This is great!

    Next time please post the CSV as a text. Believe or not I would not retype the CSV posted as an image to my PC but to write a short code which would convert textual CSV into DBF is much better option.

    Tuesday, August 20, 2019 7:05 PM
  • Hi Rai_Ohki,

    2 things

    #1

    there is another option to get your data into a post, without creating a file that has to be uploaded. The _VFP object offers a method called 'DataToClip' that transforms your currently selected table/cursor into text and copies it into clipboard. Its syntax is descibed like this:

    ApplicationObject.DataToClip(
    [nWorkArea | cTableAlias] [, nRecords]
    [, nClipFormat]
    )

    * // fields seperated by BLANKS
    _VFP.DataToClip([myCursor],RECCOUNT([myCursor]),1)
    
    * // fields separated by TABS
    _VFP.DataToClip([myCursor],RECCOUNT([myCursor]),3)
    After that you can paste your cursor data into your post without any problems ;)

    #2

    As your timestamps are coming from a biometric scanner, then it would be interesting to know HOW the data is placed into your table. I can't imagine, that you place all these values into your table by hand, don't you? So, there might be something going wrong in the routine that receives the biometric data and puts it in the table and therefore, your current data will be corrupted as soon as the next biometrics are added.

    One option (in case the biometric import is handled by VFP) would be to check the original timestamp value with VARTYPE(). This function must show a 't' (=time) and if not, then you know where you have work to do.

    JM2C


    Gruss / Best regards
    -Tom
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011


    Wednesday, August 21, 2019 6:18 AM
    Answerer
  • ok i understand sorry for the inconvenience
    Wednesday, August 21, 2019 11:43 PM
  • i understand i will try it next time i need help and need to post csv's

    for now its manual its a work in progress of a program but i get excel files from the biometrics and im trying to import it as dbf in my program

    Wednesday, August 21, 2019 11:45 PM