【问题】
I have a 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 dates per record is within 15 minutes:
ArrayList per15Min = new ArrayList() {{
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 from 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
【回答】
给每条时间标注所属区间范围,硬编码需要两层循环,日期的处理也比较麻烦,推荐用集算器,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:读取 csv 数据
A3:使用 pseg 函数返回数据所属的区间编号
A4:通过区间编号获取数据所属的区间,生成最终结果
集算器不仅可以进行关联计算,还可以通过 JDBC 与 JAVA 集成,参考《Java 如何调用 SPL 脚本》。