datetime stamp duration

[#1]

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.

Max(Checked)-Min(Checked)

[Anonymous]

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.

[hemachandran]

HI,

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')

[hemachandran]

Hi,

Try this expression for getting the correct time interval..

[#5]

This is what happens

should be by person by day

[#6]

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.

[Anonymous]

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:

datetest:

LOAD

PersonID,

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 below

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

floor(hour(PersonTime)/12).

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

Timestamp#(

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