title: How to convert Java timestamp stored as bigint to timestamp in Presto?
body: <p>I've had little luck searching for this over a couple days.</p>
  
  <p>If my avro schema for data in a hive table is:</p>
  
  <pre><code>{
    "type" : "record",
    "name" : "messages",
    "namespace" : "com.company.messages",
    "fields" : [ {
      "name" : "timeStamp",
      "type" : "long",
      "logicalType" : "timestamp-millis"
    }, {
    …
  </code></pre>
  
  <p>and I use presto to query this, I do not get formatted timestamps.</p>
  
  <pre><code>select "timestamp", typeof("timestamp") as type,
  current_timestamp as "current_timestamp", typeof(current_timestamp) as current_type
  from db.messages limit 1
  </code></pre>
  
  <blockquote>
  <pre><code>timestamp     type   current_timestamp                  current_type
  1497210701839 bigint 2017-06-14 09:32:43.098 Asia/Seoul timestamp with time zone
  </code></pre>
  </blockquote>
  
  <p>I thought it would be a non-issue then to convert them to timestamps with millisecond precision, but I'm finding I have no clear way to do that.</p>
  
  <pre><code>select cast("timestamp" as timestamp) from db.messages limit 1
  </code></pre>
  
  <blockquote>
  <pre><code>line 1:16: Cannot cast bigint to timestamp
  </code></pre>
  </blockquote>
  
  <p>Also they've changed presto's timestamp casting to always assume the source is in seconds.
  <a href="https://issues.apache.org/jira/browse/HIVE-3454" rel="nofollow noreferrer">https://issues.apache.org/jira/browse/HIVE-3454</a></p>
  
  <p>So if I used <code>from_unixtime()</code> I have to chop off the milliseconds or else it gives me a very distant date:</p>
  
  <pre><code>select from_unixtime("timestamp") as "timestamp" from db.messages limit 1
  </code></pre>
  
  <blockquote>
  <pre><code>timestamp  
  +49414-08-06 07:15:35.000
  </code></pre>
  </blockquote>
  
  <p>Surely someone else who works with Presto more often knows how to express the conversion properly. (I can't restart the Presto nor Hive servers to force the timezone into UTC either btw).</p>