从时间开始（1）－－AES系统按时间搜索文档java/sql

时间类的数据类型。这中数据类型表面上看看，像字符型的数据类型，但是又不是。因为他可以像数字型的数据类型那样，进行运算。所以说，他是兼有数字型与字符型数据类型的特点。掌握好时间型的数据类型，是掌握ORACLE数据库设计的一个必须具备的知识。因为在实际的数据库设计中，基本上没没张表都要用到这个数据类型

简洁！时间工具类：

public class TimeUtil {
	/************************系统时间**********************/
	public static String now() {//包含时间
		SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return fm.format(new Date());
	}
	
	public static String getDate() {//日期，不包含时间
		return now().substring(0, 10);
	}
	/*－－－－－－－－－－－－－－－－－－时间转换－－－－－－－－－－－－－－－－－－*/
	public static String getDate(String fmt) {
		SimpleDateFormat fm = new SimpleDateFormat(fmt);
		return fm.format(new Date());
	}
	
	public static String getTime(Date date) {
		SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return fm.format(date);
	}
	
	public static Date toDate(String date) {
		SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd");
		try {
			return fm.parse(date);
		} catch (Exception e) {
			return null;
		}
	}
	
	public static Date toTime(String date) {
		SimpleDateFormat fm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			return fm.parse(date);
		} catch (Exception e) {
			return null;
		}
	}
}

import com.lysoft.business.component.aes.common.TimeUtil;
// 按时间搜索
public class SearchDaoImpl implements SearchDao{
	@Override
	public ButterflyPagedResult<Map<String, Object>> findResultList(IDaos idaos, Map<String , Object> params)
			throws PersistenceException, Exception {
		final String endTime = TimeUtil.now();
		final long WEEKS = 604800000; // 一周的毫秒数6*24*60*60*1000
		
		String keyword = (String) params.get("keyword");
		String fileType = (String) params.get("fileType");
		String resName = (String) params.get("resName");
		String creator = (String) params.get("creator");
		String ownLib = (String) params.get("ownLib");
		Integer skip = (Integer) params.get("skip");
		Integer limit = (Integer) params.get("limit");
		String searchTime = (String) params.get("searchTime");
		String flag = (String) params.get("flag");
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		Date date = sdf.parse(endTime);
		long endTimes = date.getTime();
		long l = endTimes - WEEKS;
		final String sWeek = sdf.format(new Date(l));	// 一周前的时间
		final String sMonth = endTime.substring(0,7);
		final String sYear = endTime.substring(0,4);
		
		String sql = "select r.*, substr(r.update_time,1,10) update_time1, u.user_name from AES_RESOURCE r" +
					 " left join aes_user u on r.creator=u.guuid" +
					 " where 1=1";
		if(keyword != null && !"".equals(keyword)) {
			sql = sql + " AND (r.intro LIKE '%" + keyword + "%' OR r.res_name LIKE '" + keyword + "%')";
		}
		if(fileType != null && !"".equals(fileType)) {
			sql = sql + " AND r.file_type='" + fileType + "'";
		}
		if(resName != null && !"".equals(resName)) {
			sql = sql + " AND r.res_name LIKE '%" + resName + "%'";
		}
		if(creator != null && !"".equals(creator)) {
			sql = sql + " AND u.user_name LIKE '%" + creator + "%'";
		}
		if(ownLib != null && !"".equals(ownLib)) {
			sql = sql + " AND r.own_dir LIKE '%" + ownLib + "-%'";// 注意所属目录的格式AES01-0001-0001
		}
		if(searchTime != null && !"".equals(searchTime)) {
			if (searchTime.equals("week")){
				sql = sql + " AND r.update_time > '"+ sWeek +"' and r.update_time < '"+endTime+"'";// 最近一周
			}else if (searchTime.equals("month")){
				sql = sql + " AND r.update_time > '"+ sMonth +"' and r.update_time < '"+endTime+"'";// 本月
			}else if (searchTime.equals("year")){
				sql = sql + " AND r.update_time > '"+ sYear +"' and r.update_time < '"+endTime+"'";// 本年内
			}
		}
		if(flag != null && !"".equals(flag)) {
			sql = sql + " AND 2!=2";
		}
		sql = sql  + " ORDER BY r.update_time desc";
		return idaos.getMapDao().queryPagedResult(sql, skip, limit);
	}
}

to_char() 和 to_date()

SQL里的时间转换：

//oracle
to_char(ama.SICK_LEAVE_TIME,'yyyy-MM-dd') SICK_LEAVE_TIME
and t.begin_time > add_months(to_date('"+beginMonth+"','yyyy-MM'),-0) and t.end_time < add_months(to_date('"+endMonth+"','yyyy-MM'),1)" +



//系统时间：
         
 SELECT * FROM CAMPUS_NOTICE 
         where (TYPE='2' or TYPE='0') and to_date(OVER_TIME,'yyyy-MM-dd')>= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd') ORDER BY BEGIN_TIME DESC;