[
https://issues.apache.org/jira/browse/SQOOP-1420?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michele Giusto updated SQOOP-1420:
----------------------------------
Summary: Wrong timestamp lower/upper bounds due to timezone (was: Wrong
timestamp upper bound due to timezone)
> Wrong timestamp lower/upper bounds due to timezone
> --------------------------------------------------
>
> Key: SQOOP-1420
> URL: https://issues.apache.org/jira/browse/SQOOP-1420
> Project: Sqoop
> Issue Type: Bug
> Components: connectors/oracle
> Affects Versions: 1.4.4
> Reporter: Michele Giusto
>
> Hi,
> I am using Sqoop 1.4.4 to do incremental imports from an Oracle database,
> checking a timestamp column representing the instant of last update. The
> timestamp column is in GMT, while the server running Sqoop is in GMT+2, and I
> am looking for updates every 15 minutes. Due to the different timezones,
> Sqoop launches queries filtering on timestamp ranges that are in the future,
> resulting in no rows exported.
> Looking at the source code of Sqoop, it seems that to calculate the upper
> bound (lower bound at next iteration) for the query, the program gets the
> current time in millisecond (getCurrentDbTimestamp() in
> org.apache.sqoop.manager.ConnManager) and creates a java.sql.Timestamp object
> from it. Then, org.apache.sqoop.tool.ImportTool calls
> datetimeToQueryString(String datetime, int columnType) of
> org.apache.sqoop.manager.OracleManager passing the timestamp as a string, and
> get back the sql filter to put in the final query. The problem seems to be
> that the toString() method of a java.sql.Timestamp object considers the
> user.timezone of the JVM (Europe/Rome in my case), so I get a String
> representing a date that is in the future if considered as GMT (as the Oracle
> database does with the SQL function TO_TIMESTAMP).
> To solve this problem, it may be enough to force the conversion from
> Timestamp to String to work in GMT timezone (like setting user.timezone=GMT).
> Another solution, when interacting with Oracle, may be to use the
> TO_TIMESTAMP_TZ function, that allows to specify the timezone of the String
> representing the timestamp.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
