从时间开始（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{@Overridepublic 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*1000String 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里的时间转换：

//oracleto_char(ama.SICK_LEAVE_TIME,'yyyy-MM-dd') SICK_LEAVE_TIMEand 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;