过程:
准备数据
UPSERT INTO COM_MART.TEST_TABLE (ID,TIME_TEST) VALUES('1116',CURRENT_TIME());
结果显示的时间比服务器当前时间少了整整8个小时,原因是phoenix默认是0时区,并无法设置时区(也可以理解为不会或没有找到设置的方法)
1116 | 2019-11-27 06:48:50.551
程序执行
无条件查询
String sql = "select * from COM_MART.TEST_TABLE";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Timestamp time = rs.getTimestamp("TIME_TEST");
String time_str = rs.getString("TIME_TEST");
System.out.println("timestamp类型:" + time);
System.out.println("String类型:" + time_str);
}
使用timestamp接收会自动根据服务器时区转换为正常时间,使用string类型接收的结果,不会根据服务器时区转换;
带上时间条件查询
String sql = "select * from COM_MART.TEST_TABLE WHERE TIME_TEST BETWEEN ? AND ? ";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setTimestamp(1, Timestamp.valueOf("2019-11-27 06:00:00"));
ps.setTimestamp(2, Timestamp.valueOf("2019-11-27 07:00:00"));
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println("6点到7点查询出来数据");
}
String sql1 = "select * from COM_MART.TEST_TABLE WHERE TIME_TEST BETWEEN ? AND ? ";
PreparedStatement ps1 = conn.prepareStatement(sql1);
ps1.setTimestamp(1, Timestamp.valueOf("2019-11-27 14:00:00"));
ps1.setTimestamp(2, Timestamp.valueOf("2019-11-27 15:00:00"));
ResultSet rs1 = ps1.executeQuery();
while (rs1.next()) {
System.out.println("14点到15点查询出来数据");
}
使用预编译,timestamp参数,能在正常时区的时间段检索到结果;
插入
String sql = "upsert into COM_MART.TEST_TABLE values ('1',TO_TIMESTAMP('2019-11-27 18:00:00'))";
PreparedStatement ps = conn.prepareStatement(sql);
ps.execute();
String sql1 = "upsert into COM_MART.TEST_TABLE values ('2',?)";
PreparedStatement ps1 = conn.prepareStatement(sql1);
ps1.setTimestamp(1, Timestamp.valueOf("2019-11-27 18:00:00"));
ps1.execute();
conn.commit();
conn.close();
再次查询的结果是:
总结
phoenix时区默认是0时区,使用程序进行操作的时候,需要统一方式:
要么使用string接收结果,查询条件和新增使用拼接sql的形式
或使用timestamp接收结果,查询条件和新增适用于编译,设置timestamp参数的形式