一、本文背景
项目中有使用mysql,db2,sql server,oracle四种数据库,有进行时间的对比,和获取当前的时间,和数据库分页查询,特在本文总结一下我这次在这四种数据库不同的sql写法,作为笔记记录
二、获取当前时间
1.mysql获取当前时间:select now();
2.db2获取当前时间:select current timestamp from sysibm.sysdummy1;
3.sql server获取当前时间:select GETDATE();
4.oracle获取当前时间:select sysdate from dual
三、java传入字符串与数据库时间对比
表中的FIX_BEGINTIME 为DATETIME类型
1.mysql字符串转时间对比:SELECT FIX_BEGINTIME from tb_queue WHERE FIX_BEGINTIME < STR_TO_DATE(' 2020-12-10 15:34:58','%Y-%m-%d %H:%i:%s');
2.db2字符串转时间对比:SELECT FIX_BEGINTIME from tb_queue WHERE FIX_BEGINTIME < to_date('2020-12-10 15:54:26','yyyy-mm-dd hh24:mi:ss');
3.sql server字符串转时间对比:SELECT FIX_BEGINTIME FROM TB_QUEUE WHERE FIX_BEGINTIME < convert(varchar(100),'2020-12-09 17:40:43',120);
4.oracle字符串转时间对比:SELECT FIX_BEGINTIME from tb_queue WHERE FIX_BEGINTIME < to_date('2020-12-10 15:54:26','yyyy-mm-dd hh24:mi:ss');
四、获取当前时间前5分钟
1.mysql获取当前时间前5分钟:select SUBDATE(NOW(),interval 5 minute)
2.db2获取当前时间前5分钟:select TIMESTAMP(current timestamp - 5 minute) from sysibm.sysdummy1;
3.sql server获取当前时间前5分钟:select dateadd(minute,-5,GETDATE());
4.oracle获取当前时间前5分钟:select (SYSDATE-5/1440) from dual
五、分页查询数据
selectsql字符串即为正常不带分页的查询sql,beginCount为开始行数,endcount为结束行数
1.mysql
selectSql + " limit " + beginCount + "," + endCount;
2.db2
"select * from(select row_number() over() as rownum ,t.* from (" +
selectSql + ")t )temp_t where rownum between " +
beginCount + " and " + endCount
3.sql server
"select * from (select row_number() over(order by tempColumn) tempRowNumber,* from ( select top " + endCount +
" 0 as tempColumn," + selectSql +
") t) tt where tempRowNumber>= " + beginCount
4.oracle
查询条数<=500000
"select * from (select t.*, rownum rn from (" + selectSql +
") t where rownum <= " + endCount + ") where rn>= " +beginCount
查询条数>500000
"select * from (select t.*, rownum rn from ( " + selectSql +
") t) where rn between " + beginCount + " and " + endCount
上面的所有分页sql的嵌套查询有些多,之所以多嵌套了一些循环,是因为在项目背景中,对于selectsql传入进来,我不需要修改任何东西,可以直接匹配以上任意数据库语法的分页查询。