6 Replies Latest reply: Mar 8, 2017 6:25 PM by David Maurice RSS

    datetime stamp duration

    Robert Winkel

      I am trying to get duration from datetime stamps but I have some employees that work evening from 10pm until 7am the following day.

      Is there way to get the true duration?  The last AM time and the first PM time I do not want to include.  for this person it shows 23 hours of work.  when in fact its closer to 10 hours.




        • Re: datetime stamp duration
          David Maurice

          Hi Robert, probably best to explain your data and how it is used. I am assuming you have a dimension of each person and measures of their login/logout times.


          With the times you have shown, the user has logged out at 6:53 PM and then logged in again at 9:38 PM (or is it at 6:51 AM -> 9:55PM)?


          So the problem is that your logic max(date)-min(date) works for daytime workers but not overnight workers. Is there a way to determine those workers in your data and then user a different formula? I'd argue you might be able to use a tricky formula to determine if the first login time (say after a weekend) is either in the morning or evening, which then determines the working hours based on starting in either of those periods, then apply the formula at that point, but ideally the logic would be already in your data.

            • Re: datetime stamp duration
              Robert Winkel


              is there a way to have it use the max and min over twelve hours or split by AM and PM


              What these people do is check prescriptions and the do 50 or more an hour so not to worried about the time between two prescriptions at 12:00 am or 12 pm.

                • Re: datetime stamp duration
                  David Maurice

                  Thanks for that - the timestamps in the middle need to be ignored. I think the easiest way to do this is in the loadscript, as it's getting messy in my table, but I think I'm getting close.


                  With this test data:




                  ts as PersonTimeRaw,

                  Timestamp#(ts, 'DD/MM/YYYY hh:mm:ss') as PersonTime

                  inline [ PersonID, ts

                  1, '12/02/2016 06:51:39'

                  1, '12/02/2016 06:53:37'

                  1, '12/02/2016 21:38:40'

                  1, '12/02/2016 21:54:01'

                  1, '12/02/2016 21:55:49'

                  1, '13/02/2016 02:40:12'

                  2, '12/02/2016 09:01:25'

                  2, '12/02/2016 10:31:25'

                  2, '12/02/2016 17:12:46'



                  I've created a table as belowdates.jpg


                  So for dimensions, I have the PersonID, the day of the PersonTime, and the AM/PM flag



                  The next dimesion is the 12-hour end period from that AM/PM,


                  date(makedate(year(PersonTime), month(PersonTime), day(PersonTime) + if(floor(hour(PersonTime)/12) = 1, 1, 0)), 'DD/MM/YYYY') & ' ' &

                  time(makeTime(if(floor(hour(PersonTime)/12) = 1, 0, 12), 0, 0), 'hh:mm:ss')

                  , 'DD/MM/YYYY hh:mm:ss')


                  Then there are min/max and interval calculations afterwards


                  So the question is, by splitting up the min/max of the timestamp into AM/PM sections, is it useful? Possibly not, since you might be able to work out the same thing based on the hour values - we can work out if the min(timestamp) = max(timestamp) in that period that the endpoint of that time is in the next period (so you would add together the interval of the max(timestamp) -> AM/PM and AM/PM -> min(timestamp) of the next period, but that is getting complicated quickly.


                  Hopefully that's a good starting point, but it might just be simpler if you could assume anyone's timestamp after (say 7PM) is a nighttime worker

              • Re: datetime stamp duration
                Hemachandran Deyvasigamani



                Try this Set Analysis for getting time interval


                =Interval(Timestamp(Date(Date#('08-03-2017 07:00:00 AM','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY HH:MM:SS TT'),'DD-MM-YYYY HH:MM:SS TT')

                -Timestamp(Date(Date#('07-03-2017 10:00:00 PM','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY HH:MM:SS TT'),'DD-MM-YYYY HH:MM:SS TT'),'HH:MM:SS')

                • Re: datetime stamp duration
                  Hemachandran Deyvasigamani



                  Try this expression for getting the correct time interval..