从Oracle DB读取时，日期(数据类型)到时间戳(数据类型)的错误转换 [英] Incorrect conversion of Date (data type) to TimeStamp (data type) while reading from Oracle DB

问题描述

我们正在尝试从Oracle表中读取数据，将基于日期"的数据类型转换为时间戳"数据类型.

We are trying to read data from Oracle tables, "Date" based data types are converted into "Timestamp" Data types.

例如:表是Oracle.

e.g: Table is Oracle.

desc hr.employees;

Name Null? Type
-----------------------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SSN VARCHAR2(55)

和在Scala的DataFrame中读取的模式

and schema read in the DataFrame in Scala

|-- EMPLOYEE_ID: decimal(6,0) (nullable = false)    
|-- FIRST_NAME: string (nullable = true)    
|-- LAST_NAME: string (nullable = false)    
|-- EMAIL: string (nullable = false)    
|-- PHONE_NUMBER: string (nullable = true)    
|-- HIRE_DATE: timestamp (nullable = false) (Incorrect data type read here)    
|-- JOB_ID: string (nullable = false)    
|-- SALARY: decimal(8,2) (nullable = true)    
|-- COMMISSION_PCT: decimal(2,2) (nullable = true)    
|-- MANAGER_ID: decimal(6,0) (nullable = true)    
|-- DEPARTMENT_ID: decimal(4,0) (nullable = true)    
|-- SSN: string (nullable = true)

Hire_Date被误读为TimeStamp，有一种纠正方法.

Hire_Date is read incorrectly as TimeStamp, is there a way to correct.

正在从Oracle即时读取数据，并且该应用程序不具有数据类型的先验知识，并且在读取后无法将其转换.

Data is being read from Oracle on the fly and the application does not have an upfront knowledge of datatypes and can't convert it after being read.

推荐答案

分析: 按照甲骨文-

Oracle Database 8i和更早版本不支持TIMESTAMP 数据，但Oracle DATE数据过去曾将时间成分作为 SQL标准的扩展.因此，Oracle数据库8i和更早版本 版本的JDBC驱动程序将oracle.sql.DATE映射到java.sql.Timestamp 保留时间部分.从Oracle数据库9.0.1开始， 包括TIMESTAMP支持，并且9i JDBC驱动程序开始映射 oracle.sql.DATE到java.sql.Date.此映射不正确，因为它 截断了Oracle DATE数据的时间部分.为了克服这个 问题，Oracle数据库11.1引入了新标志 mapDateToTimestamp.此标志的默认值为true，即 表示默认情况下，驱动程序将正确映射oracle.sql.DATE 到java.sql.Timestamp，保留时间信息.如果你仍然 希望将不正确但兼容10g的oracle.sql.DATE转换为java.sql.Date 映射，那么您可以通过设置 mapDateToTimestamp标志设置为false.

Oracle Database 8i and earlier versions did not support TIMESTAMP data, but Oracle DATE data used to have a time component as an extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped oracle.sql.DATE to java.sql.Timestamp to preserve the time component. Starting with Oracle Database 9.0.1, TIMESTAMP support was included and 9i JDBC drivers started mapping oracle.sql.DATE to java.sql.Date. This mapping was incorrect as it truncated the time component of Oracle DATE data. To overcome this problem, Oracle Database 11.1 introduced a new flag mapDateToTimestamp. The default value of this flag is true, which means that by default the drivers will correctly map oracle.sql.DATE to java.sql.Timestamp, retaining the time information. If you still want the incorrect but 10g compatible oracle.sql.DATE to java.sql.Date mapping, then you can get it by setting the value of mapDateToTimestamp flag to false.

引用链接位于此处.

解决方案:

1. 因此，按照oracle的指示，将属性jdbc.oracle.mapDateToTimestamp设置为false-

1. So as instructed by oracle provide property jdbc.oracle.mapDateToTimestamp as false -

  Class.forName("oracle.jdbc.driver.OracleDriver")
  var info : java.util.Properties = new java.util.Properties()
  info.put("user", user)
  info.put("password", password)
  info.put("oracle.jdbc.mapDateToTimestamp", "false")
  val jdbcDF = spark.read.jdbc(jdbcURL, tableFullName, info)

添加支持"oracle.jdbc.mapDateToTimestamp"标志的Oracle数据库连接器jar为ojdbc14.jar

Add Oracle database connector jar which supports "oracle.jdbc.mapDateToTimestamp" flag is ojdbc14.jar

希望有帮助！

这篇关于从Oracle DB读取时，日期(数据类型)到时间戳(数据类型)的错误转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！