title: Spark SQL - How to select on dates stored as UTC millis from the epoch?
body: <p>I have been searching and have not found a solution as to how one might query on dates stored as UTC milliseconds from the epoch using Spark SQL.  The schema I have pulled in from a NoSQL datasource (JSON from MongoDB) has the target date as:</p>
  
  <pre><code>|-- dateCreated: struct (nullable = true)
  
  ||-- $date: long (nullable = true)
  </code></pre>
  
  <p>The complete schema is as follows:</p>
  
  <pre><code>scala&gt; accEvt.printSchema
  root
   |-- _id: struct (nullable = true)
   |    |-- $oid: string (nullable = true)
   |-- appId: integer (nullable = true)
   |-- cId: long (nullable = true)
   |-- data: struct (nullable = true)
   |    |-- expires: struct (nullable = true)
   |    |    |-- $date: long (nullable = true)
   |    |-- metadata: struct (nullable = true)
   |    |    |-- another key: string (nullable = true)
   |    |    |-- class: string (nullable = true)
   |    |    |-- field: string (nullable = true)
   |    |    |-- flavors: string (nullable = true)
   |    |    |-- foo: string (nullable = true)
   |    |    |-- location1: string (nullable = true)
   |    |    |-- location2: string (nullable = true)
   |    |    |-- test: string (nullable = true)
   |    |    |-- testKey: string (nullable = true)
   |    |    |-- testKey2: string (nullable = true)
   |-- dateCreated: struct (nullable = true)
   |    |-- $date: long (nullable = true)
   |-- id: integer (nullable = true)
   |-- originationDate: struct (nullable = true)
   |    |-- $date: long (nullable = true)
   |-- processedDate: struct (nullable = true)
   |    |-- $date: long (nullable = true)
   |-- receivedDate: struct (nullable = true)
   |    |-- $date: long (nullable = true)
  </code></pre>
  
  <p>and my goal is to write queries along the lines of:</p>
  
  <pre><code>SELECT COUNT(*) FROM myTable WHERE dateCreated BETWEEN [dateStoredAsLong0] AND [dateStoredAsLong1]
  </code></pre>
  
  <p>My process thus far has been:</p>
  
  <pre><code>scala&gt; val sqlContext = new org.apache.spark.sql.SQLContext(sc)
  sqlContext: org.apache.spark.sql.SQLContext = org.apache.spark.sql.SQLContext@29200d25
  
  scala&gt; val accEvt = sqlContext.jsonFile("/home/bkarels/mongoexport/accomplishment_event.json")
  
  ...
  14/10/29 15:03:38 INFO SparkContext: Job finished: reduce at JsonRDD.scala:46, took 4.668981083 s
  accEvt: org.apache.spark.sql.SchemaRDD = 
  SchemaRDD[6] at RDD at SchemaRDD.scala:103
  
  scala&gt; accEvt.registerAsTable("accomplishmentEvent")
  </code></pre>
  
  <p><em>(At this point the following base line query executes successfully)</em></p>
  
  <pre><code>scala&gt; sqlContext.sql("select count(*) from accomplishmentEvent").collect.foreach(println)
  ...
  [74475]
  </code></pre>
  
  <p>Now, the voodoo that I cannot get right is how to form my select statement to reason about the dates.  For example the following executes w/o error, but returns zero rather than the count of all records as it should (74475).</p>
  
  <pre><code>scala&gt; sqlContext.sql("select count(*) from accomplishmentEvent where processedDate &gt;= '1970-01-01'").collect.foreach(println)
  ...
  [0]
  </code></pre>
  
  <p>I have also tried some ugliness like:</p>
  
  <pre><code>scala&gt; val now = new java.util.Date()
  now: java.util.Date = Wed Oct 29 15:05:15 CDT 2014
  
  scala&gt; val today = now.getTime
  today: Long = 1414613115743
  
  scala&gt; val thirtydaysago = today - (30 * 24 * 60 * 60 * 1000)
  thirtydaysago: Long = 1416316083039
  
  
  scala&gt; sqlContext.sql("select count(*) from accomplishmentEvent where processedDate &lt;= %s and processedDate &gt;= %s".format(today,thirtydaysago)).collect.foreach(println)
  </code></pre>
  
  <p>As recommended, I've selected on a named field to ensure that works.  So:</p>
  
  <pre><code>scala&gt; sqlContext.sql("select receivedDate from accomplishmentEvent limit 10").collect.foreach(println)
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>[[1376318850033]]
  [[1376319429590]]
  [[1376320804289]]
  [[1376320832835]]
  [[1376320832960]]
  [[1376320835554]]
  [[1376320914480]]
  [[1376321041899]]
  [[1376321109341]]
  [[1376321121469]]
  </code></pre>
  
  <p>Then extending to try and get some kind of dates working I have tried:</p>
  
  <pre><code>scala&gt; sqlContext.sql("select cId from accomplishmentEvent where receivedDate.date &gt; '1970-01-01' limit 5").collect.foreach(println)
  </code></pre>
  
  <p>Results in the error:</p>
  
  <pre><code>java.lang.RuntimeException: No such field date in StructType(ArrayBuffer(StructField($date,LongType,true)))
  ...
  </code></pre>
  
  <p>Prefixing our field name with <code>$</code> as also suggested results in a different kind of error:</p>
  
  <pre><code>scala&gt; sqlContext.sql("select cId from accomplishmentEvent where receivedDate.$date &gt; '1970-01-01' limit 5").collect.foreach(println)
  java.lang.RuntimeException: [1.69] failure: ``UNION'' expected but ErrorToken(illegal character) found
  
  select actualConsumerId from accomplishmentEvent where receivedDate.$date &gt; '1970-01-01' limit 5
  </code></pre>
  
  <p>Clearly I am not getting how to select on dates stored in this way - can anyone help me fill in this gap?</p>
  
  <p>I am newer to both Scala and Spark, so forgive me if this is an elementary question, but my searches have turned up empty on the forums and Spark documentation.</p>
  
  <p>Thank you.</p>