业务表中的记录是按小时统计的,做成报表时需要group 成按天统计的。
在夏令时开始和结束的时,他们的某一天的时间间隔不是24小时(在开始时小于24小时,结束时大于24小时或者说开始时候把表往前拨一段时间,结束时往回拨一段时间)。
一般的做法就是循环判断TimeZone.inDaylightTime(Date)是否处于夏令时内。
找出分界点的那一天,再根据TimeZone.getDSTSavings()去计算 dayOfDayLightSavingEnd 。
当然,jdk的TimeZone信息是自己会自动更新的。如果在mysql里的time_zone添加数据的话,需要自己手动去更新。
import java.util.TimeZone;
public class Test {
public static void main(String[] args) {
Test t=new Test();
String timeCondition= t.calcTimeCondition(1235894400, 1238569200, TimeZone.getTimeZone("America/Los_Angeles"));
System.out.println(timeCondition);
}
/**
* split the time period which use "case when" clause ,if the start and end time are not in the same dst .
* it is must be appended a alias name for this time condition.example =>" as ts,"
* @param startTime
* @param endTime
* @param timeZone=>the timezone of client
* @return time condition expression .
*/
public String calcTimeCondition(int startTime,int endTime,TimeZone timeZone){
String defaultTimeCondition=generateTimeCondition(startTime);
//not use dst
if(timeZone.useDaylightTime()==false)
{
return defaultTimeCondition;
}
//in the same dst
if(daylightTimeState(startTime, timeZone)==daylightTimeState(endTime, timeZone))
{
return defaultTimeCondition;
}
// not in the same dst
int dayOfDayLightSavingStart = startTime, dayOfDayLightSavingEnd = endTime;
int dayStartTime = startTime;
for (int i = startTime; i <= endTime; i += 86400)
{
//dst start
if (daylightTimeState(dayStartTime, timeZone) == false && daylightTimeState(i, timeZone) == true)
{
dayOfDayLightSavingStart = dayStartTime;
dayOfDayLightSavingEnd = i - (int) (timeZone.getDSTSavings() / 1000);
break;
}
//dst end
else if (daylightTimeState(dayStartTime, timeZone) == true && daylightTimeState(i, timeZone) == false)
{
dayOfDayLightSavingStart = dayStartTime;
dayOfDayLightSavingEnd = i + (int) (timeZone.getDSTSavings() / 1000);
break;
}
dayStartTime = i;
}
String timeCondition = " Case " +
" WHEN time_stamp>"+startTime+" AND time_stamp<="+dayOfDayLightSavingStart+" THEN "+
defaultTimeCondition+
" WHEN time_stamp>"+dayOfDayLightSavingStart+" AND time_stamp<="+dayOfDayLightSavingEnd+" THEN "+
dayOfDayLightSavingStart+
" WHEN time_stamp>"+dayOfDayLightSavingEnd+" AND time_stamp<="+endTime+" THEN "+
generateTimeCondition(dayOfDayLightSavingEnd)+
" END ";
return timeCondition;
}
private boolean daylightTimeState(int timeStamp, TimeZone tz)
{
return tz.inDaylightTime(new java.util.Date(1000L * timeStamp));
}
/**
* CEIL is used to project the time stamp to the end of the period, after which it is
* shifted back to the start of the period, in order to ensure that time stamps that fall
* on a one-minute boundary are treated as belonging to the previous minute.
* @param startTime
* @return
*/
private String generateTimeCondition(int startTime){
StringBuilder condition=new StringBuilder();
condition.append(startTime).append("+86400*(CEIL((time_stamp -").append(startTime).append(")/86400) - 1)");
return condition.toString();
}
}