Filter Interval of two Timestamps

[alexandermllr]

Hello everyone,


I have one table with two timestamps, Start and End.
I also greated a calendar with 15 minutes timestamps, TimeStamp_Start and TimeStamp_End.
I want to get only the 15 minutes Timestamps, when it is between the Start and End.

First table:

Start	End	Value
01.01.2020 14:20	01.01.2020 15:30	300
01.03.2020 22:55	02.03.2020 04:10	1450
01.01.2020 15:10	01.01.2020 15:14	20

Second table (calendar):

TimeStamp_Start	TimeStamp_End
01.01.2020 00:00	01.01.2020 00:15
01.01.2020 00:15	01.01.2020 00:30
01.01.2020 00:30	01.01.2020 00:45

I hope I could describe it good 🙂

Thanks for your help!

[Taoufiq_Zarra]

for example here what is the output ?

[alexandermllr]

The output should be:

Start	End	TimeStamp_Start	TimeStamp_End	Value
01.01.2020 14:20	01.01.2020 15:30	01.01.2020 14:15	01.01.2020 14:30	300
01.01.2020 14:20	01.01.2020 15:30	01.01.2020 14:30	01.01.2020 14:45	300
01.03.2020 22:55	02.03.2020 04:10	01.03.2020 22:45	01.03.2020 23:00	1450
01.03.2020 22:55	02.03.2020 04:10	01.03.2020 23:00	01.03.2020 23:15	1450

So for each 15 min TimeStamp Interval a new row, when it is between Start and End.

[Taoufiq_Zarra]

it's a beautiful challenge

Script :

Firsttable:

LOAD num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))) as nStart,num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm'))) as nEnd,
Div(floor((num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm')))-num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))))*1440),15) as Valuetmp,* 

INLINE [
    Start, End, Value
    01.01.2020 14:20, 01.01.2020 15:30, 300
    01.03.2020 22:55, 02.03.2020 04:10, 1450
    01.01.2020 15:10, 01.01.2020 15:14, 20
];


Let NumRows=NoOfRows('Firsttable');

FOR i=0 to $(NumRows)-1
  LET vStart=Peek('Start',$(i),'Firsttable');
  LET vEnd=peek('End',$(i),'Firsttable');
  LET vnStart=Peek('nStart',$(i),'Firsttable');
  LET vnEnd=peek('nEnd',$(i),'Firsttable');
  LET v0=0;
  LET v15=peek('Valuetmp',$(i),'Firsttable');
  LET vValue=peek('Value',$(i),'Firsttable');
  
  if $(v15)<>0 then
		Input:
			load '$(vStart)' as A,'$(vEnd)' as B,'$(vValue)' as C,IterNo() as Idt,if(IterNo()=1,TimeStamp(TimeStamp#('$(vStart)','DD.MM.YYYY hh:mm'))) as D
			AutoGenerate 1  While $(v0) + IterNo() -1 <= $(v15);
  endif
NEXT;



Tmp:
noconcatenate

load A as Start,B as End,C as Value,If(Idt=1, D, TimeStamp(Peek(ValueInreval) + Time#(15,'mm') ,'DD.MM.YYYY hh:mm:ss')) as ValueInreval resident Input;

drop table Input,Firsttable;



Second:
LOAD TimeStamp(TimeStamp#(TimeStamp_Start,'DD.MM.YYYY hh:mm')) as TimeStamp_Start,TimeStamp(TimeStamp#(TimeStamp_End,'DD.MM.YYYY hh:mm')) as TimeStamp_End INLINE [
    TimeStamp_Start, TimeStamp_End
    01.01.2020 14:15, 01.01.2020 14:30
    01.01.2020 14:30, 01.01.2020 14:45
    01.01.2020 14:45, 01.01.2020 15:15
    01.01.2020 14:15, 01.01.2020 15:45
    01.01.2020 14:30, 01.01.2020 14:45
    01.03.2020 22:45, 01.03.2020 23:00
    01.03.2020 23:00, 01.03.2020 23:15
];



inner Join IntervalMatch ( ValueInreval )
 
LOAD TimeStamp_Start, TimeStamp_End
Resident Second;

output :

if the Second table is filled the null values will disappear.

[alexandermllr]

Thank you so much!

I tried your script and it works, except for the line: 

01.01.2020 15:10, 01.01.2020 15:14, 20

This line will not appear.

[Taoufiq_Zarra]

can you share a sample data with this line (FirstTable and the second ) ?

[alexandermllr]

The second table is a created calendar for every 15 minutes.

In the first table the data is:

01.01.2020 15:10, 01.01.2020 15:14, 20

The output should be:

Start	End	Value	TimeStamp_Start	TimeStamp_End
01.01.2020 15:10	01.01.2020 15:14	20	01.01.2020 15:00	01.01.2020 15:15

I think the reason why it does not work is because there is only one 15 min interval for this data.

[Taoufiq_Zarra]

New version :

Firsttable:

LOAD num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))) as nStart,num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm'))) as nEnd,
Div(floor((num(TimeStamp(TimeStamp#(End,'DD.MM.YYYY hh:mm')))-num(TimeStamp(TimeStamp#(Start,'DD.MM.YYYY hh:mm'))))*1440),15) as Valuetmp,* 

INLINE [
    Start, End, Value
    01.01.2020 14:20, 01.01.2020 15:30, 300
    01.03.2020 22:55, 02.03.2020 04:10, 1450
    01.01.2020 15:10, 01.01.2020 15:14, 20
];


Let NumRows=NoOfRows('Firsttable');

FOR i=0 to $(NumRows)-1
  LET vStart=Peek('Start',$(i),'Firsttable');
  LET vEnd=peek('End',$(i),'Firsttable');
  LET vnStart=Peek('nStart',$(i),'Firsttable');
  LET vnEnd=peek('nEnd',$(i),'Firsttable');
  LET v0=0;
  LET v15=peek('Valuetmp',$(i),'Firsttable');
  LET vValue=peek('Value',$(i),'Firsttable');
  
  
		Input:
			load '$(vStart)' as A,'$(vEnd)' as B,'$(vValue)' as C,IterNo() as Idt,if(IterNo()=1,TimeStamp(TimeStamp#('$(vStart)','DD.MM.YYYY hh:mm'))) as D
			AutoGenerate 1  While $(v0) + IterNo() -1 <= $(v15);
  
NEXT;



Tmp:
noconcatenate

load A as Start,B as End,C as Value,If(Idt=1, D, TimeStamp(Peek(ValueInreval) + Time#(15,'mm') ,'DD.MM.YYYY hh:mm:ss')) as ValueInreval resident Input;

drop table Input,Firsttable;



Second:
LOAD TimeStamp(TimeStamp#(TimeStamp_Start,'DD.MM.YYYY hh:mm')) as TimeStamp_Start,TimeStamp(TimeStamp#(TimeStamp_End,'DD.MM.YYYY hh:mm')) as TimeStamp_End INLINE [
    TimeStamp_Start, TimeStamp_End
    01.01.2020 14:15, 01.01.2020 14:30
    01.01.2020 14:30, 01.01.2020 14:45
    01.01.2020 14:45, 01.01.2020 15:00
    01.01.2020 15:00, 01.01.2020 15:15
    01.01.2020 15:15, 01.01.2020 15:30
    01.03.2020 22:45, 01.03.2020 23:00
    01.03.2020 23:00, 01.03.2020 23:15
];



inner Join IntervalMatch ( ValueInreval )
 
LOAD TimeStamp_Start, TimeStamp_End
Resident Second;

output :