title: Convert string to timestamp in Hive
body: <p>I have the following string representation of a timestamp in my Hive table:</p>
  
  <pre><code>20130502081559999
  </code></pre>
  
  <p>I need to convert it to a string like so:</p>
  
  <pre><code>2013-05-02 08:15:59
  </code></pre>
  
  <p>I have tried following ({code} >>> {result}): </p>
  
  <pre><code>from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmss')) &gt;&gt;&gt; 2013-05-03 00:54:59
  from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssMS')) &gt;&gt;&gt; 2013-09-02 08:15:59
  from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssMS')) &gt;&gt;&gt; 2013-05-02 08:10:39
  </code></pre>
  
  <p>Converting to a timestamp and then unixtime seems weird, what is the proper way to do this?</p>
  
  <p><strong>EDIT</strong>
  I figured it out.</p>
  
  <pre><code>from_unixtime(unix_timestamp(substr('20130502081559999',1,14), 'yyyyMMddHHmmss')) &gt;&gt;&gt; 2013-05-02 08:15:59
  </code></pre>
  
  <p>or </p>
  
  <pre><code>from_unixtime(unix_timestamp('20130502081559999', 'yyyyMMddHHmmssSSS')) &gt;&gt;&gt; 2013-05-02 08:15:59
  </code></pre>
  
  <p>Still... Is there a better way?</p>