title: Hive SELECT records from 1 hour ago
body: <p>I have a hive table that contains a column called timestamp. The timestamp is a bigint field generated from java <code>System.currenttimemillis()</code>. I suppose it should be in UTC. Right now I am trying to select records from 1 hour ago. I know in MySQL you can do something like:</p>
  
  <pre><code>SELECT * FROM table WHERE datetimefield &gt;= DATE_SUB(NOW(), INTERVAL 1 HOUR)
  </code></pre>
  
  <p>In hive, it seems like <code>NOW()</code> is missing. I did some searching and find <code>unix_timestamp()</code>. I should be able to get the current UTC time in milliseconds by doing a <code>unix_timestamp()*1000</code>.</p>
  
  <p>So if i want to get records from 1 hour ago I am thinking about doing something like:</p>
  
  <pre><code>SELECT * FROM hivetable WHERE datetimefield &gt;= (unix_timestamp()*1000-3600000); 
  </code></pre>
  
  <p>Can someone suggest if it's the right way to approach this problem? Also what if I want to select like 1 day ago? Seems inconvenient to convert that to milliseconds. Any help or suggested readings will be highly appreciated. Thanks in advance for your help.</p>