title: How to convert UNIX timestamp to the given timezone in Spark?
body: <p>I use Spark 1.6.2</p>
  <p>I have epochs like this:</p>
  <pre class="lang-none prettyprint-override"><code>+--------------+-------------------+-------------------+
  |unix_timestamp|UTC                |Europe/Helsinki    |
  +--------------+-------------------+-------------------+
  |1491771599    |2017-04-09 20:59:59|2017-04-09 23:59:59|
  |1491771600    |2017-04-09 21:00:00|2017-04-10 00:00:00|
  |1491771601    |2017-04-09 21:00:01|2017-04-10 00:00:01|
  +--------------+-------------------+-------------------+
  </code></pre>
  <p>The default timezone is the following on the Spark machines:</p>
  <p>#timezone = DefaultTz: <strong>Europe/Prague</strong>, SparkUtilTz: <strong>Europe/Prague</strong></p>
  <p>the output of</p>
  <pre class="lang-scala prettyprint-override"><code>logger.info(&quot;#timezone = DefaultTz: {}, SparkUtilTz: {}&quot;, TimeZone.getDefault.getID, org.apache.spark.sql.catalyst.util.DateTimeUtils.defaultTimeZone.getID)
  </code></pre>
  <p>I want to count the timestamps grouped by date and hour in the given timezone (now it is <strong>Europe/Helsinki +3hours</strong>).</p>
  <p>What I expect:</p>
  <pre class="lang-none prettyprint-override"><code>+----------+---------+-----+
  |date      |hour     |count|
  +----------+---------+-----+
  |2017-04-09|23       |1    |
  |2017-04-10|0        |2    |
  +----------+---------+-----+
  </code></pre>
  <p>Code (using <code>from_utc_timestamp</code>):</p>
  <pre class="lang-scala prettyprint-override"><code>def getCountsPerTime(sqlContext: SQLContext, inputDF: DataFrame, timeZone: String, aggr: String): DataFrame = {
  
      import sqlContext.implicits._
  
      val onlyTime = inputDF.select(
           from_utc_timestamp($&quot;unix_timestamp&quot;.cast(DataTypes.TimestampType),  timeZone).alias(&quot;time&quot;)
      )
  
      val visitsPerTime =
          if (aggr.equalsIgnoreCase(&quot;hourly&quot;)) {
              onlyTime.groupBy(
                  date_format($&quot;time&quot;, &quot;yyyy-MM-dd&quot;).alias(&quot;date&quot;),
                  date_format($&quot;time&quot;, &quot;H&quot;).cast(DataTypes.IntegerType).alias(&quot;hour&quot;),
              ).count()
          } else if (aggr.equalsIgnoreCase(&quot;daily&quot;)) {
              onlyTime.groupBy(
                  date_format($&quot;time&quot;, &quot;yyyy-MM-dd&quot;).alias(&quot;date&quot;)
              ).count()
          }
  
      visitsPerTime.show(false)
  
      visitsPerTime
  }
  </code></pre>
  <p>What I get:</p>
  <pre class="lang-none prettyprint-override"><code>+----------+---------+-----+
  |date      |hour     |count|
  +----------+---------+-----+
  |2017-04-09|22       |1    |
  |2017-04-09|23       |2    |
  +----------+---------+-----+
  </code></pre>
  <p>Trying to wrap it with <code>to_utc_timestamp</code>:</p>
  <pre class="lang-scala prettyprint-override"><code>def getCountsPerTime(sqlContext: SQLContext, inputDF: DataFrame, timeZone: String, aggr: String): DataFrame = {
  
      import sqlContext.implicits._
  
      val onlyTime = inputDF.select(
          to_utc_timestamp(from_utc_timestamp($&quot;unix_timestamp&quot;.cast(DataTypes.TimestampType), timeZone), DateTimeUtils.defaultTimeZone.getID).alias(&quot;time&quot;)
      )
  
      val visitsPerTime = ... //same as above
  
      visitsPerTime.show(false)
  
      visitsPerTime
  }
  </code></pre>
  <p>What I get:</p>
  <pre class="lang-none prettyprint-override"><code>+----------+---------+-----+
  |tradedate |tradehour|count|
  +----------+---------+-----+
  |2017-04-09|20       |1    |
  |2017-04-09|21       |2    |
  +----------+---------+-----+
  </code></pre>
  <p>How to get the expected result?</p>