[jira] [Commented] (SQOOP-423) Sqoop import of timestamps to Avro from Postgres - Timezone Issue

["Paul Mazak (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SQOOP-423?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14146887#comment-14146887 ] 

Paul Mazak commented on SQOOP-423:
----------------------------------

This was a major issue for us.  We had to re-import a bunch of data.
Sqoop is importing a SQL Server Timestamp, which the driver turns into a java.sql.Timestamp datatype, and doing a ((Timestamp) o).getTime() which returns a long in the timezone of the executing datanode, not the timezone of SQL Server where the datatype was created.  It seems that ((Timestamp) o).toString() would be better as that represents what SQL Server shows when selecting the column.

We've since added the workaround suggested above.  BTW, the property is now called:
{code}-D mapreduce.map.java.opts=" -Duser.timezone=GMT"{code}


> Sqoop import of timestamps to Avro from Postgres - Timezone Issue
> -----------------------------------------------------------------
>
>                 Key: SQOOP-423
>                 URL: https://issues.apache.org/jira/browse/SQOOP-423
>             Project: Sqoop
>          Issue Type: Bug
>    Affects Versions: 1.3.0
>            Reporter: Lynn Goh
>
> I am running sqoop-1.3.0-cdh3u2 on a Mac and when I sqoop import from a postgres table with columns of type 'timestamp without time zone', they are converted to longs in the time zone of my local operating system, even after I have started Hadoop up with TZ=GMT or passed in HADOOP_OPTS="-Duser.timezone=GMT".  My ultimate goal is to sqoop import into long representations that are in GMT timezone rather than my operating system's timezone.
> Postgres example:
> {code}
> acamp_id |     start_time      |      end_time       
> ----------+---------------------+---------------------
>        1 | 2008-01-01 00:00:00 | 2011-12-16 00:00:00
> {code}
> After import, you can see the values are 8 hours ahead, even with TZ=GMT and user.timezone set properly (this is the json representation of the parsed imported avro file):
> {code}
> {"acamp_id": 1, "end_time": 1324022400000, "start_time": 1199174400000}
> {code}
> date utility invocation:
> {code}
> lynngoh@unknown:~$ date -u -r 1199174400
> Tue Jan  1 08:00:00 UTC 2008
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)