title: Spark Scala - Minimum Difference in Timestamps
body: <p>I'm trying to figure out over a 24 hour time period, what is the minimum temperature and maximum temperature in a specific 24 hour window, then find the day with the largest difference. Once I have this largest difference, I'm trying to determine the shortest timeframe that this difference occurred in.</p>
  
  <p>For example a sample of my processed data looks like:
  Current Output from code below:</p>
  
  <pre><code>+------------+-------------------+---------+-------+-------+
  |TemperatureF|               Date|timestamp|MinTemp|MaxTemp|
  +------------+-------------------+---------+-------+-------+
  |        28.0| 01/01/2000 6:53 AM|946709580|   28.0|   37.4|
  |        28.0| 01/01/2000 7:53 AM|946713180|   28.0|   37.4|
  |        28.0| 01/01/2000 8:53 AM|946716780|   28.0|   37.4|
  |        30.2|01/01/2000 10:24 PM|946765440|   30.2|   37.4|
  |        30.9|01/01/2000 10:53 PM|946767180|   30.9|   37.4|
  |        37.4| 01/02/2000 4:39 AM|946787940|   28.0|   37.4|
  |        36.0| 01/02/2000 4:53 AM|946788780|   28.0|   36.0|
  |        36.0| 01/02/2000 5:53 AM|946792380|   28.0|   36.0|
  +------------+-------------------+---------+-------+-------+
  </code></pre>
  
  <p>If I look at the 3 first rows where the MinTemp is 28 and MaxTemp is 37.4. Since I'm looking at the difference between these 2 (9.4) over a 24 hour period (1/1/2000 6:53AM to 1/2/2000 6:53AM).  The MaxTemp occurs at 01/02/2000 at 4:39AM. Given this, I'd want my answer to be:</p>
  
  <pre><code>+-----------------+-------------------+----------+
  |Start Time       |            EndTime|difference|
  +-----------------+-------------------+----------+
  |01/01/2000 8:53AM|  01/02/2000 4:39AM|9.4       |
  +-----------------+-------------------+----------+
  </code></pre>
  
  <p>This is because, this is the shortest timeframe to reach this difference in temperature. </p>
  
  <p>I think the approach I want to take is create columns for the start time, the temperature at that time (That matches the Minimum for that period), the end time, the temperature at that time (That matches the maximum for that period). Then take a difference in time to get the shortest time difference.</p>
  
  <p>Example output:</p>
  
  <pre><code>+-----------------+-----------+-----------------+-----------+-------+-------+
  StartTime         |Temperature|EndTime.         |Temperature|MinTemp|MaxTemp|
  +-----------------+-----------+-----------------+-----------+-------+-------+
  01/01/2000 6:53AM | 28.0      |01/02/2000 4:39AM| 37.4      | 28.0  | 37.4  |
  01/01/2000 7:53AM | 28.0      |01/02/2000 4:39AM| 37.4      | 28.0  | 37.4  |
  01/01/2000 8:53AM | 28.0      |01/02/2000 4:39AM| 37.4      | 28.0  | 37.4  |
  +-----------------+-----------+-----------------+-----------+-------+-------+
  
  </code></pre>
  
  <p>From here I would calculate the minimum difference in time, and the difference in temperature.</p>
  
  <p>Any thoughts on the best way to do this?</p>
  
  <p>Current Code:</p>
  
  <pre><code>val data = osh.select(col("TemperatureF"), concat(format_string("%02d",col("Month")),lit("/"),format_string("%02d",col("Day")),lit("/"),col("Year"),lit(" "),col("TimeCST")).as("Date")).filter(col("TemperatureF") &gt; -9999)
  
  val oshdata = data.withColumn("timestamp",unix_timestamp(to_timestamp(col("Date"),"MM/dd/yyyy hh:mm a")))
  
  import org.apache.spark.sql.expressions._
  val myWindow = Window.orderBy("timestamp").rangeBetween(Window.currentRow, 86400)
  val myData = oshdata.withColumn("MinTemp", min(col("TemperatureF")).over(myWindow))
    .withColumn("MaxTemp",max(col("TemperatureF")).over(myWindow))
  
  
  myData.createOrReplaceTempView("oshView")
  spark.sqlContext.sql("Select * from oshView where TemperatureF == MinTemp or TemperatureF == MaxTemp").show(25)
  </code></pre>