外包业务中，关于夏令时的处理

业务表中的记录是按小时统计的，做成报表时需要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();
    }
    
}