数据库查询当前时间操作，分页查询

一、本文背景

项目中有使用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传入进来，我不需要修改任何东西，可以直接匹配以上任意数据库语法的分页查询。