title: Rounding of timestamp to nearest second
body: <p>I've got a table containing timestamp values, and I want to round each of these values to the nearest second, but I can't get it work properly.</p>
  
  <p>My test data and approaches so far:</p>
  
  <pre><code>with v_data as
   (select to_timestamp('2012-12-10 10:49:30.00000000',
                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
           to_timestamp('2012-12-10 10:49:30',
                        'YYYY-MM-DD HH24:mi:ss') expected
      from dual
    union all
    select to_timestamp('2012-12-10 10:49:30.46300000',
                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
           to_timestamp('2012-12-10 10:49:30',
                        'YYYY-MM-DD HH24:mi:ss') expected
      from dual
    union all
    select to_timestamp('2012-12-10 10:49:30.50000000',
                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
           to_timestamp('2012-12-10 10:49:31',
                        'YYYY-MM-DD HH24:mi:ss') expected
      from dual
    union all
    select to_timestamp('2012-12-10 10:49:30.56300000',
                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
           to_timestamp('2012-12-10 10:49:31',
                        'YYYY-MM-DD HH24:mi:ss') expected
  
      from dual
    union all
    select to_timestamp('2012-12-10 10:49:30.99999999',
                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
           to_timestamp('2012-12-10 10:49:31',
                        'YYYY-MM-DD HH24:mi:ss') expected
      from dual)
  select v1.base_val,
         v1.expected,
         v1.base_val + (0.5 / 86400) solution_round,
         cast(v1.base_val as date) as solution_cast,
         extract(second from v1.base_val) - trunc(extract(second from v1.base_val)) fractional_seconds,
         v1.base_val -
         (extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add
    from v_data v1
  </code></pre>
  
  <p>All my solutions have a flaw:</p>
  
  <ul>
  <li>solution_round always rounds up</li>
  <li>solution_cast works up to 11gR1, but in 11gR2, it always rounds down (cause: Oracle changed the behaviour - it now truncates instead of rounding, see <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2242066" rel="nofollow">https://forums.oracle.com/forums/thread.jspa?threadID=2242066</a> )</li>
  <li>solution_add returns 10:49:29 instead of 10:49:31 for the last three rows</li>
  </ul>
  
  <p>I <em>guess</em> solution_add should work, and I just made some stupid mistake :-)</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Ben's solution (see below) works for me, but relying on to_char(timestamp, 'FF') seems to be dangerous - the number of digits returned depends on the definition of the timestamp.</p>
  
  <p>I'm using to_char(timestamp, 'FF3') instead, which seems to return milliseconds reliably. </p>