Oracle——按时间段查询

最新推荐文章于 2025-07-17 08:42:29 发布

原创最新推荐文章于 2025-07-17 08:42:29 发布 · 417 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

SQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了如何使用SQL查询在多个时间段内进行筛选，并详细解释了如何将日期转换为特定格式，包括从服务器获取时间、格式化日期以及比较日期的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

按照多个时间段查询：包括秒

String sql = "from Sedimentpermit";
			sql += " where Carno like '%" + carno + "%'";
			sql += " and road like '%" + road + "%'";
			sql += " and to_char(VALIDDATE,'yyyy-mm-dd') >= '" + validDate
					+ "'";
			sql += " and ((to_char(BEGINDATE1,'yyyy-mm-dd hh24:mi:ss') <= '"
					+ time + "'";
			sql += " and to_char(ENDDATE1,'yyyy-mm-dd hh24:mi:ss') >= '" + time
					+ "') ";
			sql += " or (to_char(BEGINDATE2,'yyyy-mm-dd hh24:mi:ss') <= '"
					+ time + "'";
			sql += " and to_char(ENDDATE2,'yyyy-mm-dd hh24:mi:ss') >= '" + time
					+ "') ";
			sql += " or (to_char(BEGINDATE3,'yyyy-mm-dd hh24:mi:ss') <= '"
					+ time + "'";
			sql += " and to_char(ENDDATE3,'yyyy-mm-dd hh24:mi:ss') >= '" + time
					+ "')) ";

常用的日期转换方法

//获得服务器时间
	public static Date getServerTime(Context con) {
		String seamurl = DBHelp.getConfigValue(con, ConfigKey.seamurl);
		if (seamurl.equals("")) {
			seamurl = StringHelp.getResSeamUrl(con);
		}
		seamurl += "?requestType=getTime";
		String bs = "";
		Date date = null;
		try {
			for (int i = 0; i <= 5; i++) {
				bs = HttpHelp.getHttpBack(seamurl);
				if (bs.contains("time")) {
					date = DateHelp.stringToDate2(StringHelp.getXMLAtt(bs,
							"time"));
					return date;
				}
				// Thread.sleep(300);
			}
		} catch (Exception e) {
			e.printStackTrace();
			LogHelp.Log2SDErr(e);
			bs = "<time>" + getCurrentTime() + "</time>";
		}

		LogHelp.LogI("服务器时间=" + bs);
		if (date == null) {
			bs = "<time>" + getCurrentTime() + "</time>";
			date = DateHelp.stringToDate2(StringHelp.getXMLAtt(bs, "time"));
		}
		return date;
	}

	public static String convertDate(Date date, String format) {
		if (date != null) {
			DateFormat format1 = new SimpleDateFormat(format);
			String s = format1.format(date);
			return s;
		}
		return "";
	}

	public static String getCurTime() {
		return convertDate(new Date(), "HH:mm");
	}

	public static String getCurrentTime() {
		return convertDate(new Date(), "yyyy-MM-dd HH:mm:ss");
	}
	

	public static String getCurDate() {
		return convertDate(new Date(), "yyyy-MM-dd");
	}

	public static String getCurDate(Date date) {
		return convertDate(date, "yyyy-MM-dd");
	}

	public static String getFormateDate(int type, Date date) {
		// 获取系统时间，并格式化
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		int year = calendar.get(Calendar.YEAR);
		int mounth = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DATE);
		int hour = calendar.get(Calendar.HOUR_OF_DAY);
		int min = calendar.get(calendar.MINUTE);
		int secound = calendar.get(calendar.SECOND);

		// 格式一：2011-11-1 12-12-66+mima 作为图片文件名
		if (type == 1) {
			String a[] = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j" };
			Random random = new Random();
			int i = random.nextInt(10);
			String extra = a[i];
			String mima = MiMa.jiami(extra + AddZero(min) + AddZero(secound)
					+ random.nextInt(10), 6);
			return String.valueOf(year) + "-" + AddZero(mounth) + "-"
					+ AddZero(day) + " " + AddZero(hour) + "-" + AddZero(min)
					+ "-" + AddZero(secound) + " " + mima;
		}
		// 格式二：2011年11月1日 12时12分45秒 写在照片上
		else if (type == 2) {
			return String.valueOf(year) + "年" + AddZero(mounth) + "月"
					+ AddZero(day) + "日  " + AddZero(hour) + "时" + AddZero(min)
					+ "分" + AddZero(secound) + "秒";
		}
		// 格式三：2011-11-1 12-12-11 作为录音文件名
		else if (type == 3) {
			return String.valueOf(year) + "-" + AddZero(mounth) + "-"
					+ AddZero(day) + " " + AddZero(hour) + "-" + AddZero(min)
					+ "-" + AddZero(secound) + " ";
		}
		return null;
	}

	/**
	
	 * @annotation 获取指定格式的日期
	 */
	@SuppressWarnings("static-access")
	public static String getFormateDate(int type) {
		// 获取系统时间，并格式化
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(new Date());
		int year = calendar.get(Calendar.YEAR);
		int mounth = calendar.get(Calendar.MONTH) + 1;
		int day = calendar.get(Calendar.DATE);
		int hour = calendar.get(Calendar.HOUR_OF_DAY);
		int min = calendar.get(calendar.MINUTE);
		int secound = calendar.get(calendar.SECOND);

		// 格式一：2011-11-1 12-12-66+mima 作为图片文件名
		if (type == 1) {
			String a[] = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j" };
			Random random = new Random();
			int i = random.nextInt(10);
			String extra = a[i];
			String mima = MiMa.jiami(extra + AddZero(min) + AddZero(secound)
					+ random.nextInt(10), 6);
			return String.valueOf(year) + "-" + AddZero(mounth) + "-"
					+ AddZero(day) + " " + AddZero(hour) + "-" + AddZero(min)
					+ "-" + AddZero(secound) + " " + mima;
		}
		// 格式二：2011年11月1日 12时12分45秒 写在照片上
		else if (type == 2) {
			return String.valueOf(year) + "年" + AddZero(mounth) + "月"
					+ AddZero(day) + "日  " + AddZero(hour) + "时" + AddZero(min)
					+ "分" + AddZero(secound) + "秒";
		}
		// 格式三：2011-11-1 12-12-11 作为录音文件名
		else if (type == 3) {
			return String.valueOf(year) + "-" + AddZero(mounth) + "-"
					+ AddZero(day) + " " + AddZero(hour) + "-" + AddZero(min)
					+ "-" + AddZero(secound) + " ";
		}
		return null;
	}

	public static String AddZero(int i) {
		if (i >= 0 && i <= 9) {
			return "0" + i;
		}
		return String.valueOf(i);

	}

	/**
	 * 

	 * @annotation string 转 日期类型
	 */
	public static Date stringToDate(String string) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = null;
		try {
			date = sdf.parse(string);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}

	public static Date stringToDate2(String string) {
		// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date date = null;
		try {
			date = sdf.parse(string);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}

	/**
	 * 

	 * @annotation 日期比较：都是sring型:0相等，>0大于，<0 小于
	 */
	public static long compareDate(String date1, String date2) {
		Date beginTime = DateHelp.stringToDate(date1);
		Date endTime = DateHelp.stringToDate(date2);
		return beginTime.getTime() - endTime.getTime();
	}

	/**
	 * 

	 * @annotation 日期比较：都是Date型
	 */
	public static long compareDate(Date date1, Date date2) {
		return date1.getTime() - date2.getTime();
	}