Assign production shift to TimeStamp

[paweln]

Dear All,

I have TimeStamps ( 22.08.2020 14:18:21) of some events and I would like to sort them into 3 buckets. 

I shift from 6:00 till 14:00,

II shift from 14:00 till 22:00

III shift from 22:00 til 6:00

I've tried:

if(time(TimeStamp)>'06:00:00' and time(TimeStamp)<'14:00:00', 'I shift',
if(time(TimeStamp)>'14:00:00' and time(TimeStamp)<'22:00:00', 'II shift',
if(time(TimeStamp)>'22:00:00' and time(TimeStamp)<'06:00:00', 'III shift',
)))

Dosn't work. Any suggestions? I guess I can't compare time this way. 

[arulsettu · Accepted solution]

First extract time from the Timestamp field and use it in the condition

Time(Frac('22.08.2020 14:18:21'))

or
Time#(right('22.08.2020 14:18:21',8), 'HH:MM:SS')

[Taoufiq_Zarra · Accepted solution]

using IntervalMatch is better.

and if you have a format problem you can always use this option, like :

for example   :

Data:

LOAD *,hour(Date#(Date,'DD.MM.YYYY hh:mm:ss'))&':'&Minute(Date#(Date,'DD.MM.YYYY hh:mm:ss'))  as TimeStamp1 Inline [
Date, Field1, Field2
22.08.2020 11:18:21, 0, Start of shift 
22.08.2020 10:18:21, 1, Line stop
22.08.2020 14:18:21, 2, Line restart 50%
22.08.2020 00:18:21, 3, Line speed 100%
22.08.2020 06:18:21, 4, Start of shift 
22.08.2020 21:18:21, 5, End of production
];


shiftMap:
LOAD * INLINE [
Start, End, Flag
06:00, 14:00, I shift
14:00, 22:00, II shift
22:00, 00:00, III shift
00:00, 06:00, III shift
];


Inner Join IntervalMatch ( TimeStamp1 )
 
LOAD Start, End
Resident shiftMap;

the output :

[paweln]

Taoufiq ZARRA,

thank you for showing such possibility. I think I'll use it. What if I have some event that took place at 06:00:00 exactly? will it be I or II shift?

[Taoufiq_Zarra]

@paweln 

if I understood correctly the two borders are included, so you can use :

shiftMap:
LOAD * INLINE [
Start, End, Flag
06:00, 14:00, I shift
14:00, 22:00, II shift
22:00, 00:00, III shift
00:00, 05:59, III shift
];

otherwise it's going to be in both shift

[paweln]

OK, you're right. I can set 13:59:59 as the end of I shift and 14:00:00 as begining of II shift. 

Something more soficticated:

Let's say night shift starts at 22:00:00 on 15th of August 2020 and it lasts till midnight. Time between midnight and 06:00:00 of 16th of August 2020 is in fact next day but in my company we treat it like night shift 15th of August. 

When I make a graph showing number of events per shift and date taken from TimeStamp it will count number of events incorrectly. 

Any idea how to solv that?