代码之家 › 专栏 › 技术社区 › Rodrick

为什么从Oracle花费的时间不一致?

oracle java

Rodrick · 技术社区 · 9 年前

我正在编写一个java应用程序,它从Oracle获取UTC时间,然后在表上查询某行是否与该时间匹配。如果没有结果,请再次使用UTC,以此类推。

但有时时间倒退了,或者提前1或2分钟,然后在正确的时间继续。

这是UTC时间记录:

15/03/2016 16:42:59 [INFO]  Time (UTC): 21:42:25 {1} 
15/03/2016 16:42:59 [INFO]  Time (UTC): 21:42:25 {2} 
15/03/2016 16:42:59 [INFO]  Time (UTC): 21:42:25 {3} 
15/03/2016 16:42:59 [INFO]  Time (UTC): 21:42:25 {4} 
15/03/2016 16:42:59 [INFO]  Time (UTC): 21:42:25 {5} 
15/03/2016 16:43:00 [INFO]  Time (UTC): 21:44:12 {6} 
15/03/2016 16:43:00 [INFO]  Time (UTC): 21:42:26 {7} 
15/03/2016 16:43:00 [INFO]  Time (UTC): 21:42:26 {8} 
15/03/2016 16:43:00 [INFO]  Time (UTC): 21:42:26 {9} 
15/03/2016 16:43:00 [INFO]  Time (UTC): 21:42:26 {10} 
15/03/2016 16:43:00 [INFO]  Time (UTC): 21:42:26 {11} 
15/03/2016 16:43:01 [INFO]  Time (UTC): 21:42:27 {12} 
15/03/2016 16:43:01 [INFO]  Time (UTC): 21:42:27 {13} 
15/03/2016 16:43:01 [INFO]  Time (UTC): 21:42:27 {14} 
15/03/2016 16:43:01 [INFO]  Time (UTC): 21:42:27 {15} 
15/03/2016 16:43:01 [INFO]  Time (UTC): 21:42:27 {16} 
15/03/2016 16:43:01 [INFO]  Time (UTC): 21:42:27 {17} 
15/03/2016 16:43:02 [INFO]  Time (UTC): 21:42:28 {18} 
15/03/2016 16:43:02 [INFO]  Time (UTC): 21:42:28 {19} 
15/03/2016 16:43:02 [INFO]  Time (UTC): 21:42:28 {20} 
15/03/2016 16:43:02 [INFO]  Time (UTC): 21:42:28 {21} 
15/03/2016 16:43:02 [INFO]  Time (UTC): 21:42:28 {22} 
15/03/2016 16:43:02 [INFO]  Time (UTC): 21:42:28 {23} 
15/03/2016 16:43:03 [INFO]  Time (UTC): 21:44:15 {24} 
15/03/2016 16:43:03 [INFO]  Time (UTC): 21:44:15 {25} 
15/03/2016 16:43:03 [INFO]  Time (UTC): 21:44:15 {26} 
15/03/2016 16:43:03 [INFO]  Time (UTC): 21:44:15 {27} 
15/03/2016 16:43:03 [INFO]  Time (UTC): 21:44:16 {28} 
15/03/2016 16:43:04 [INFO]  Time (UTC): 21:44:16 {29} 
15/03/2016 16:43:04 [INFO]  Time (UTC): 21:42:30 {30}

这是我代码的一部分:

int arraycounter = 0;
int index = 0;

while (arraycounter = 0)
{       
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    String utc_time = "";

    String querytime = "select to_char(sys_extract_utc(systimestamp),'HH24:MI:SS') from dual";

    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:"+ConnectionString);
        stmt = con.createStatement();
        rs = stmt.executeQuery(querytime);

        while(rs.next()) {
            utc_time = rs.getString(1);
        }

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            stmt.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    String query = "select * from table where to_char(date, 'hh24:mi:ss') = '"+utc_time+"'";

    index++;
    logger.info("Time (UTC): "+ utc_time +" {"+(index)+"}");

    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:"+ConnectionString);
        stmt = con.createStatement();
        rs = stmt.executeQuery(querytime);

        while( rs.next() ){             

        //save the result into an arraylist
        arraycounter++;

        }

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            stmt.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }   
}

为什么会发生这种情况? 有没有更好的方法来计算UTC时间?

1 回复 | 直到 9 年前

Arashsoft 8 年前

我的Oracle数据库位于RAC上,实例的时间不同步。我检查了在一个实例上输入并检查时间,然后输入另一个实例并检查时间。

要知道你在什么情况下,你可以运行:

select instance_name from v$instance;

为了连接到数据库的特定实例,还可以在连接描述符中指定特定实例的instance_NAME。如果您有Oracle Real Application Clusters配置,则此功能非常有用。例如,下面的连接描述符指定了与sales.us.acme.com关联的sales1的实例名。

(DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
    (SERVICE_NAME=sales.us.acme.com)
    (INSTANCE_NAME=sales1)))

您可以在Oracle SQL Developer上将其指定为SID=sales1