【Question】

I have CSV records with timestamps like or every 5 minutes:

- 2015/05/19 16:15:00

 

- 2015/05/19 16:20:00

 

- 2015/05/19 16:35:00

 

- 2015/05/19 16:10:00

 

- 2015/05/19 16:55:00

 

I’m using an array to compare if the date per record is within 15 minutes:

 

ArrayList<String> per15Min = new ArrayList<String>() {{

 

 add("00,15");

 

 add("15,30");

 

 add("30,45");

 

 add("45,00");

 

}};

 

What I do is read each record, split it based on “,” to extract the Dates:

 

private SimpleDateFormat csvDateFormat = new SimpleDateFormat("yyyy/MM/dd HH🇲🇲ss");

 

private SimpleDateFormat fileDateFormat = new SimpleDateFormat("yyyyMMddHHmm");

 

// Loop thru each record

 

while ((perLine = br.readLine()) != null) {

 

 // Store date per record in a string

 

 String[] perColumn = perLine.split(",", -1);

 

 String date = perColumn[0];

 

 // Convert record date to yyyyMMddHHmm

 

 Date subDateP = csvDateFormat.parse(csvDate);

 

 String subDateF = fileDateFormat.format(subDateP);

 

 // Extract the date without the day (dd)

 

 String subDate = subDateF.substring(0,10);

 

 for (int j = 0 ; j < per15Min.size() ; j++) {

 

 String[] s = per15Min.get(j).split(",", -1);

 

 String m1 = s[0];

 

 String m2 = s[1];

 

 // All dates are in a yyyyMMddHHmm format

 

 Date before = fileDateFormat.parse(subDate + m1);

 

 Date after = fileDateFormat.parse(subDate + m2);

 

 Date csvRd = fileDateFormat.parse(date);

 

 System.out.println("DATE" + before + ":" + after + ":" + csvRd);

 

 // Having problems doing date comparison

 

 if ((before.compareTo(csvRd) >= 0)&& (csvRd.compareTo(after) < 0)) {

 

 System.out.println("DATE HERE" + before + ":" + after + ":" + csvRd);

 

 }

 

 }

 

}

As you can see based on the sysout it doesn’t seem to work:

 

DATE HEREWed May 20 07:30:00 SGT 2015 : Wed May 20 07:45:00 SGT 2015 : Wed May 20 07:30:00 SGT 2015

 

DATE HEREWed May 20 07:30:00 SGT 2015 : Wed May 20 07:45:00 SGT 2015 : Wed May 20 07:25:00 SGT 2015

 

DATE HEREWed May 20 07:30:00 SGT 2015 : Wed May 20 07:45:00 SGT 2015 : Wed May 20 07:20:00 SGT 2015

 

DATE HEREWed May 20 07:30:00 SGT 2015 : Wed May 20 07:45:00 SGT 2015 : Wed May 20 07:15:00 SGT 2015

 

DATE HEREWed May 20 07:30:00 SGT 2015: Wed May 20 07:45:00 SGT 2015: Wed May 20 07:10:00 SGT 2015

 

What I need is something like if the timestamp (per 5 min) is within the 15 minute array it will enter the condition:

 

00-10 minutes must enter at 00,15

 

15-25 minutes must enter at 15,30

 

30-40 minutes must enter at 30,45

 

45-55 minutes must enter at 45,00

 

【Answer】

To put each timestamp in a time interval with hardcoding, we need a two-level loop and it’s inconvenient to write the dates. It’s simple and easy to do it in esProc SPL:

 

	A
1	=file("D:/file.csv").import@c()
2	=[0,15,30,45,60]
3	=A1.new(~:point,A2.pseg(minute(datetime(~.#1,"yyyy/MM/dd   HH🇲🇲SS"))):range)
4	=A3.run(string(A2.m(range))+","+string(A2.m(range+1)):range)

A1: Import the CSV data.

A3: pseg() function returns the sequence number of the range each date belongs to.

A4: Get the ranges by A3’s sequence numbers to generate the final result.

An SPL script can integrate with a Java application via esProc JDBC. For details about the integration, refer to How to Call an SPL Script in Java.