title: Oracle timestamp rounding
body: <p>I have a date with the value - 08.06.16 19:56:20,473000000 in orcle db.</p>
  
  <pre><code>CR_DATE - TIMESTAMP(6)
  </code></pre>
  
  <p>I need the date to be truncated to the top of the prior hour and converted to epoch format. For example if the date is as mentioned above then it should get rounded to 08.06.16 19:00:00 and then converted to epoch which should give me final o/p as 1465412400</p>
  
  <p>I can use the below query to change to epoch format but with that rounding to nearest hour is not working.</p>
  
  <pre><code>ROUND((CAST(CR_DATE AS DATE) - DATE '1970-01-01') * 24 * 60 * 60, 0) AS creationDate
  </code></pre>
  
  <p>If I use </p>
  
  <pre><code>select CR_DATE,TRUNC(CREATION_DATE, 'HH24')  AS creationDate
  </code></pre>
  
  <p>then I get the value as 08.06.16 and the HH:MM details gets truncated.</p>
  
  <p>The NLS_TIMESTAMP_FORMAT set in DB is DD.MM.RR HH24:MI:SSXFF</p>
  
  <p>Can someone help me to fix this without changing the NSL Format ?</p>