{"action":"created","issue":{"url":"https://api.github.com/repos/apache/spark/issues/16308","repository_url":"https://api.github.com/repos/apache/spark","labels_url":"https://api.github.com/repos/apache/spark/issues/16308/labels{/name}","comments_url":"https://api.github.com/repos/apache/spark/issues/16308/comments","events_url":"https://api.github.com/repos/apache/spark/issues/16308/events","html_url":"https://github.com/apache/spark/pull/16308","id":196011843,"number":16308,"title":"[SPARK-18936][SQL] Infrastructure for session local timezone support.","user":{"login":"ueshin","id":506656,"avatar_url":"https://avatars.githubusercontent.com/u/506656?v=3","gravatar_id":"","url":"https://api.github.com/users/ueshin","html_url":"https://github.com/ueshin","followers_url":"https://api.github.com/users/ueshin/followers","following_url":"https://api.github.com/users/ueshin/following{/other_user}","gists_url":"https://api.github.com/users/ueshin/gists{/gist_id}","starred_url":"https://api.github.com/users/ueshin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ueshin/subscriptions","organizations_url":"https://api.github.com/users/ueshin/orgs","repos_url":"https://api.github.com/users/ueshin/repos","events_url":"https://api.github.com/users/ueshin/events{/privacy}","received_events_url":"https://api.github.com/users/ueshin/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":18,"created_at":"2016-12-16T09:09:06Z","updated_at":"2016-12-24T04:37:34Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/apache/spark/pulls/16308","html_url":"https://github.com/apache/spark/pull/16308","diff_url":"https://github.com/apache/spark/pull/16308.diff","patch_url":"https://github.com/apache/spark/pull/16308.patch"},"body":"## What changes were proposed in this pull request?\r\n\r\nAs of Spark 2.1, Spark SQL assumes the machine timezone for datetime manipulation, which is bad if users are not in the same timezones as the machines, or if different users have different timezones.\r\n\r\nWe should introduce a session local timezone setting that is used for execution.\r\n\r\nAn explicit non-goal is locale handling.\r\n\r\n### Semantics\r\n\r\nSetting the session local timezone means that the timezone-aware expressions listed below should use the timezone to evaluate values, and also it should be used to convert (cast) between string and timestamp or between timestamp and date.\r\n\r\n- `CurrentDate`\r\n- `CurrentBatchTimestamp`\r\n- `Hour`\r\n- `Minute`\r\n- `Second`\r\n- `DateFormatClass`\r\n- `ToUnixTimestamp`\r\n- `UnixTimestamp`\r\n- `FromUnixTime`\r\n\r\nand below are implicitly timezone-aware through cast from timestamp to date:\r\n\r\n- `DayOfYear`\r\n- `Year`\r\n- `Quarter`\r\n- `Month`\r\n- `DayOfMonth`\r\n- `WeekOfYear`\r\n- `LastDay`\r\n- `NextDay`\r\n- `TruncDate`\r\n\r\nFor example, if you have timestamp `\"2016-01-01 00:00:00\"` in `GMT`, the values evaluated by some of timezone-aware expressions are:\r\n\r\n```scala\r\nscala> val df = Seq(new java.sql.Timestamp(1451606400000L)).toDF(\"ts\")\r\ndf: org.apache.spark.sql.DataFrame = [ts: timestamp]\r\n\r\nscala> df.selectExpr(\"cast(ts as string)\", \"year(ts)\", \"month(ts)\", \"dayofmonth(ts)\", \"hour(ts)\", \"minute(ts)\", \"second(ts)\").show(truncate = false)\r\n+-------------------+----------------------+-----------------------+----------------------------+--------+----------+----------+\r\n|ts                 |year(CAST(ts AS DATE))|month(CAST(ts AS DATE))|dayofmonth(CAST(ts AS DATE))|hour(ts)|minute(ts)|second(ts)|\r\n+-------------------+----------------------+-----------------------+----------------------------+--------+----------+----------+\r\n|2016-01-01 00:00:00|2016                  |1                      |1                           |0       |0         |0         |\r\n+-------------------+----------------------+-----------------------+----------------------------+--------+----------+----------+\r\n```\r\n\r\nwhereas setting the session local timezone to `\"PST\"`, they are:\r\n\r\n```scala\r\nscala> spark.conf.set(\"spark.sql.session.timeZone\", \"PST\")\r\n\r\nscala> df.selectExpr(\"cast(ts as string)\", \"year(ts)\", \"month(ts)\", \"dayofmonth(ts)\", \"hour(ts)\", \"minute(ts)\", \"second(ts)\").show(truncate = false)\r\n+-------------------+----------------------+-----------------------+----------------------------+--------+----------+----------+\r\n|ts                 |year(CAST(ts AS DATE))|month(CAST(ts AS DATE))|dayofmonth(CAST(ts AS DATE))|hour(ts)|minute(ts)|second(ts)|\r\n+-------------------+----------------------+-----------------------+----------------------------+--------+----------+----------+\r\n|2015-12-31 16:00:00|2015                  |12                     |31                          |16      |0         |0         |\r\n+-------------------+----------------------+-----------------------+----------------------------+--------+----------+----------+\r\n```\r\n\r\nNotice that even if you set the session local timezone, it affects only in `DataFrame` operations, neither in `Dataset` operations, `RDD` operations nor in `ScalaUDF`s. You need to properly handle timezone by yourself.\r\n\r\n\r\n### Design of the fix\r\n\r\nI introduced an analyzer to pass session local timezone to timezone-aware expressions and modified DateTimeUtils to take the timezone argument.\r\n\r\n## How was this patch tested?\r\n\r\nExisting tests and added tests for timezone aware expressions.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/apache/spark/issues/comments/269068764","html_url":"https://github.com/apache/spark/pull/16308#issuecomment-269068764","issue_url":"https://api.github.com/repos/apache/spark/issues/16308","id":269068764,"user":{"login":"SparkQA","id":8108735,"avatar_url":"https://avatars.githubusercontent.com/u/8108735?v=3","gravatar_id":"","url":"https://api.github.com/users/SparkQA","html_url":"https://github.com/SparkQA","followers_url":"https://api.github.com/users/SparkQA/followers","following_url":"https://api.github.com/users/SparkQA/following{/other_user}","gists_url":"https://api.github.com/users/SparkQA/gists{/gist_id}","starred_url":"https://api.github.com/users/SparkQA/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SparkQA/subscriptions","organizations_url":"https://api.github.com/users/SparkQA/orgs","repos_url":"https://api.github.com/users/SparkQA/repos","events_url":"https://api.github.com/users/SparkQA/events{/privacy}","received_events_url":"https://api.github.com/users/SparkQA/received_events","type":"User","site_admin":false},"created_at":"2016-12-24T04:37:34Z","updated_at":"2016-12-24T04:37:34Z","body":"**[Test build #70565 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/70565/testReport)** for PR 16308 at commit [`7917d0f`](https://github.com/apache/spark/commit/7917d0f100e47e8e3cce8440a38c3b16ef74732d)."}}
{"actor":{"display_login":"SparkQA"}}