Time Differential

Jeremy
Jeremy Member
in Magic ETL

I'm trying to calculate how much time on average an employee spends at a location.  We have a clock in and clock out time.  Currently, I have the forumula set up as:

 

SEC_TO_TIME(Avg(TIME_TO_SEC(`Time Out`) - TIME_TO_SEC(`Time In`)))

 

This works fine for almost everything I need.  However, if an employee clocks in between 12:00pm and 1:00pm then this formula does not work.  It does the calculation as if 12:00pm is 12:00am for some reason and then shows that the employee was there for much longer than they were.  

 

This is real data from this past week (same employees

 

Employee Name 

Time In - 10:00am

Time Out - 13:15pm

Time Spent - 15:15

 

Time In - 16:30pm

Time Out - 17:00pm

Time Spent - 0:30

 

I need to know how to correct this formula.

 

Best Answer

  • ASperry
    ASperry Domo Employee
    Answer ✓

    Hi,

    Your calculation looks correct. I would suggest that your contact Domo Support to further take a look at the issue. If your `Time Out` and `Time In` fields are time data types then this should work. Please go to https://support.domo.com to submit a support ticket. Please provide them with a link to the card, dataset, and/or dataflow in question.

    Thanks,

    Sperry

Answers

  • ASperry
    ASperry Domo Employee
    Answer ✓

    Hi,

    Your calculation looks correct. I would suggest that your contact Domo Support to further take a look at the issue. If your `Time Out` and `Time In` fields are time data types then this should work. Please go to https://support.domo.com to submit a support ticket. Please provide them with a link to the card, dataset, and/or dataflow in question.

    Thanks,

    Sperry

  • Darius
    Darius Domo Employee

    Hello user09032,

     

    We were able to get this to work outside of your instance by adding the DATE_FORMAT function to the query. Using your query as an example, that may look like:

     

    SEC_TO_TIME(Avg(TIME_TO_SEC(DATE_FORMAT(`Time Out`,'%Y-%m-%d %T')) - TIME_TO_SEC(DATE_FORMAT(`Time In`,'%Y-%m-%d %T')))

     

    Please let us know if the DATE_FORMAT function gives you better results, otherwise, a support case may be the best way to proceed, as mentioned before.


    Darius Rose
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Jeremy
    Jeremy Member

    I'm using average as the formula is for a summary number.  I need it to give me the average time spent on site during the prior week.  The way I have it (I think) is it's taking average of Time Out - Time In.  The average parentheses is set up to enclose both the time out and time in).  I tried using the provided formula (removing avg and with average) and the results are always nil.  

  • Darius
    Darius Domo Employee

    Thank you user09032, 

     

    For follow up, we would recommend reaching out to support so they can review your card in more detail. Could you please reach out to them to take those next steps? They can be reached through support.domo.com.


    Darius Rose
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories