oracle数据库时间作为条件的处理

代码维修专家

已于 2024-08-28 16:08:00 修改

阅读量977

点赞数 16

文章标签：数据库 oracle sql

于 2024-08-28 15:21:14 首次发布

本文链接：https://blog.csdn.net/weixin_46458356/article/details/141636684

版权

表中的字段为timestamp类型

查找某个时间范围
转化为date类型

SELECT * FROM BIZ_TRANSACTION_RECORD
WHERE record_datetime BETWEEN TO_DATE('2019-01-10', 'yyyy-MM-dd') AND TO_DATE('2019-01-20', 'yyyy-MM-dd')
ORDER BY record_datetime ASC;`

转化成string类型

SELECT * FROM BIZ_TRANSACTION_RECORD WHERE TO_CHAR(RECORD_DATETIME,'yyyy-MM-dd HH24:mm:ss') >= ('2019-01-10 11:04:22')

用timestamp类型进行限制

SELECT * FROM BIZ_TRANSACTION_RECORD
WHERE record_datetime BETWEEN TO_TIMESTAMP('2019-01-10', 'yyyy-MM-dd') AND TO_TIMESTAMP('2019-01-20', 'yyyy-MM-dd')
ORDER BY record_datetime ASC;

查找时间为具体的某天

SELECT * FROM BIZ_TRANSACTION_RECORD
WHERE to_char(record_datetime,'yyyy-MM-dd') = ('2020-01-03')

表中字段为date类型

查找某个时间范围
用date类型进行限制

SELECT * FROM biz_dealerinfo
WHERE CREATE_DATETIME BETWEEN TO_DATE('2019-01-10','yyyy-MM-dd') AND TO_DATE('2024-01-20','yyyy-MM-dd');

SELECT * FROM biz_dealerinfo
WHERE CREATE_DATETIME >= TO_DATE('2019-01-10','yyyy-MM-dd') AND CREATE_DATETIME <= TO_DATE('2024-01-20','yyyy-MM-dd');

用String类型限制

SELECT * FROM biz_dealerinfo
WHERE to_char(CREATE_DATETIME,'yyyy-MM-dd') >= ('2019-01-10') AND to_char(CREATE_DATETIME,'yyyy-MM-dd') <=('2024-01-20');

SELECT * FROM biz_dealerinfo
WHERE to_char(CREATE_DATETIME,'yyyy-MM-dd') BETWEEN ('2019-01-10') AND ('2024-01-20')

限定某一天

SELECT * FROM biz_dealerinfo
WHERE to_char(CREATE_DATETIME,'yyyy-MM-dd') = ('2020-01-03')

表中字段为string类型

限定时间范围

SELECT *  FROM biz_dealerinfo WHERE PBOC_TIME >= ('2019-01-10') AND PBOC_TIME <= ('2024-01-10');

SELECT *  FROM biz_dealerinfo WHERE PBOC_TIME BETWEEN ('2019-01-10') AND ('2024-01-10');

具体到时分秒也可以

SELECT *  FROM biz_dealerinfo WHERE PBOC_TIME >= ('2019-01-10 11:04:22') AND PBOC_TIME <= ('2024-01-10 11:04:22');

SELECT *  FROM biz_dealerinfo WHERE PBOC_TIME BETWEEN ('2019-01-10 11:04:22') AND ('2024-01-10 11:04:22');

限定某一个时刻

SELECT *  FROM biz_dealerinfo WHERE PBOC_TIME = ('2022-12-27 16:54:00')

以上是sql语句
在java项目中时间范围可以作为参数

Date date = new Date();
		LOG.info("系统时间是"+date);
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Calendar calendarstart = Calendar.getInstance();
		//设置时间为当前系统时间
		calendarstart.setTime(date);
		//注意这里用add，在当前时间上减一天
		calendarstart.add(Calendar.DATE, -2);
		//设置时分秒是要设置为固定的9：30 ，用set
		calendarstart.set(Calendar.HOUR_OF_DAY, 23);
		calendarstart.set(Calendar.MINUTE, 30);
		calendarstart.set(Calendar.SECOND, 00);
		calendarstart.set(Calendar.MILLISECOND,000);
		设置一个结束的时间节点也String类型的
                               //结尾的范围
		Date dateend = new Date();
		SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Calendar calendarend = Calendar.getInstance();
		//设置时间为当前系统时间
		calendarend.setTime(dateend);
		//注意这里用add，在当前时间上减一天
		calendarend.add(Calendar.DATE, -1);
		//设置时分秒是要设置为固定的9：30 ，用set
		calendarend.set(Calendar.HOUR_OF_DAY, 23);
		calendarend.set(Calendar.MINUTE, 30);
		calendarend.set(Calendar.SECOND, 00);
		calendarend.set(Calendar.MILLISECOND,000);
		//calendar.getTime() 转为date类型
		String calendarstarttime = simpleDateFormat.format(calendarstart.getTime());
		LOG.info("calendarstarttime是"+calendarstarttime);
		String calendarendtime = simpleDateFormat1.format(calendarend.getTime());
		LOG.info("calendarendtime是"+calendarendtime);

其中
calendarend和calendarstart是date类型的
calendarstarttime和calendarendtime是String类型的
对应的xml文件的sql语句如下

<select id="selectAmountNew" resultMap="BaseResultMap" parameterType="map">
		select abs((select case when sum(fi.record_amount) is null then 0 else sum(fi.record_amount) end    from  BIZ_TRANSACTION_RECORD fi where
		fi.debit_credit_type='40' and fi.PAYMENT_TYPE!='paytodp' and fi.PAYMENT_TYPE!='paytobank' and fi.PAYMENT_TYPE!='bankaccount' and fi.PAYMENT_TYPE!='dealerwallet' and fi.PAYMENT_TYPE!='dealerpurse' and (fi.record_status='success' or fi.record_status='handsuccess')
		<if test="calendarstarttime != null">
			and to_char(fi.RECORD_DATETIME,'yyyy-MM-dd HH24:mm:ss') &gt;= #{calendarstarttime,jdbcType=VARCHAR}
		</if>
		<if test="calendarendtime != null">
			and to_char(fi.RECORD_DATETIME,'yyyy-MM-dd HH24:mm:ss') &lt;= #{calendarendtime,jdbcType=VARCHAR}
		</if>
		)-
		(select case when sum(fi.record_amount) is null then 0 else sum(fi.record_amount) end   from  BIZ_TRANSACTION_RECORD fi
		where  fi.debit_credit_type='50' and fi.PAYMENT_TYPE!='paytodp' and fi.PAYMENT_TYPE!='paytobank' and (fi.PAYMENT_TYPE='bankaccount' or fi.PAYMENT_TYPE='dealerwallet' ) and (fi.record_status='success' or fi.record_status='handsuccess')
		<if test="calendarstarttime != null">
			and to_char(fi.RECORD_DATETIME,'yyyy-MM-dd HH24:mm:ss') &gt;= #{calendarstarttime,jdbcType=VARCHAR}
		</if>
		<if test="calendarendtime != null">
			and to_char(fi.RECORD_DATETIME,'yyyy-MM-dd HH24:mm:ss') &lt;= #{calendarendtime,jdbcType=VARCHAR}
		</if>
		)) record_amount from  dual
	</select>

其中，需要注意
1.其中需要注意的是timestamp类型的字段需要转换成string类型，用to_char()，其中需要放两个参数，第一个为被转换的对象，后边为转换的格式 to_char(fi.RECORD_DATETIME,‘yyyy-MM-dd HH24:mm:ss’)
2.传入这个sql语句的参数calendarstarttime，需要用#{}括起来取值 #{calendarstarttime,jdbcType=VARCHAR}
3.在xml文件中>=会被当成特殊符号对待，所以>=在这里应该写成>=，<=在这里应该写成<=
4.注意and的数量（是小问题，但容易忽略）