{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/19250","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/19250/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/19250/comments","events_url":"https://api.github.com/repos/apache/spark/issues/19250/events","html_url":"https://github.com/apache/spark/pull/19250","id":258143554,"number":19250,"title":"[SPARK-12297] Table timezone correction for Timestamps","user":{"login":"squito","id":71240,"avatar_url":"https://avatars2.githubusercontent.com/u/71240?v=4","gravatar_id":"","url":"https://api.github.com/users/squito","html_url":"https://github.com/squito","followers_url":"https://api.github.com/users/squito/followers","following_url":"https://api.github.com/users/squito/following{/other_user}","gists_url":"https://api.github.com/users/squito/gists{/gist_id}","starred_url":"https://api.github.com/users/squito/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/squito/subscriptions","organizations_url":"https://api.github.com/users/squito/orgs","repos_url":"https://api.github.com/users/squito/repos","events_url":"https://api.github.com/users/squito/events{/privacy}","received_events_url":"https://api.github.com/users/squito/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":20,"created_at":"2017-09-15T19:39:38Z","updated_at":"2017-11-06T19:35:04Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/19250","html_url":"https://github.com/apache/spark/pull/19250","diff_url":"https://github.com/apache/spark/pull/19250.diff","patch_url":"https://github.com/apache/spark/pull/19250.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nWhen reading and writing data, spark will adjust timestamp data based on the delta between the current session timezone and the table time zone (specified either by a persistent table property, or an option to the DataFrameReader / Writer).  This is particularly important for parquet data, so that it can be treated equivalently by other SQL engines (eg. Impala and Hive).  Furthermore, this is useful if the same data is processed by multiple clusters in different time zones, and \"timestamp without time zone\" semantics are desired.\r\n\r\nDesign doc: https://docs.google.com/document/d/1mcbkVo-PSsFh6iOOYx6Rk_34aY25H_zT1E2f7KmLMOU/edit#heading=h.rj5dmsuhw2zg\r\n\r\n## How was this patch tested?\r\n\r\nUnit tests."},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/342260896","html_url":"https://github.com/apache/spark/pull/19250#issuecomment-342260896","issue_url":"https://api.github.com/repos/apache/spark/issues/19250","id":342260896,"user":{"login":"cloud-fan","id":3182036,"avatar_url":"https://avatars3.githubusercontent.com/u/3182036?v=4","gravatar_id":"","url":"https://api.github.com/users/cloud-fan","html_url":"https://github.com/cloud-fan","followers_url":"https://api.github.com/users/cloud-fan/followers","following_url":"https://api.github.com/users/cloud-fan/following{/other_user}","gists_url":"https://api.github.com/users/cloud-fan/gists{/gist_id}","starred_url":"https://api.github.com/users/cloud-fan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cloud-fan/subscriptions","organizations_url":"https://api.github.com/users/cloud-fan/orgs","repos_url":"https://api.github.com/users/cloud-fan/repos","events_url":"https://api.github.com/users/cloud-fan/events{/privacy}","received_events_url":"https://api.github.com/users/cloud-fan/received_events","type":"User","site_admin":false},"created_at":"2017-11-06T19:35:04Z","updated_at":"2017-11-06T19:35:04Z","author_association":"CONTRIBUTOR","body":"What's the interoperability issue with Impala? I think both Spark and Impala store timestamp as parquet INT96, representing nanoseconds from epoch, there is no timezone confusion. Internally Spark uses a long to store timestamp, representing microseconds from epoch, so we don't and shoud't consider timezone when reading parquet INT96 timestamp.\r\n\r\nI think your problem may about display. When Spark displays a timestamp value, via `df.show`, we convert the internal long value to standard timestamp string according to the session local timezone. Some examples:\r\n```\r\n// 1000 milliseconds from epoch, no timezone confusion\r\nscala> val df = Seq(new java.sql.Timestamp(1000)).toDF(\"ts\")\r\ndf: org.apache.spark.sql.DataFrame = [ts: timestamp]\r\n\r\nscala> spark.conf.set(\"spark.sql.session.timeZone\", \"GMT\")\r\n\r\nscala> df.show\r\n+-------------------+\r\n|                 ts|\r\n+-------------------+\r\n|1970-01-01 00:00:01|\r\n+-------------------+\r\n\r\nscala> spark.conf.set(\"spark.sql.session.timeZone\", \"PST\")\r\n\r\nscala> df.show\r\n+-------------------+\r\n|                 ts|\r\n+-------------------+\r\n|1969-12-31 16:00:01|\r\n+-------------------+\r\n```\r\n\r\nThis behavior, I think makes sense, but may not be SQL-compliant. A clean solution is to add `TIMESTAMP WITE TIMEZONE` type, so that when we convert the internal long value to string, we can know which timezone to use.\r\n\r\nYour proposal seems to hack the internal long value and lie to Spark about the microseconds from eppch, which doesn't look good."}}
{"actor":{"display_login":"cloud-fan"}}