Rounding of timestamp to nearest second

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.

My test data and approaches so far:

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

All my solutions have a flaw:

solution_round always rounds up
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 https://forums.oracle.com/forums/thread.jspa?threadID=2242066 )
solution_add returns 10:49:29 instead of 10:49:31 for the last three rows

I guess solution_add should work, and I just made some stupid mistake :-)

EDIT:

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.

I'm using to_char(timestamp, 'FF3') instead, which seems to return milliseconds reliably.

Solution

My preferred method would be to use a CASE statement and the fact that you can convert the fractional seconds to a number, i.e.:

select base_val, expected
     , to_timestamp(to_char(base_val,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD HH24:mi:ss')
        + case when to_number(to_char(base_val, 'FF8')) >= 50000000
                    then interval '1' second
               else interval '0' second
          end as solution_add
  from v_data

This removes the fractional seconds. Then works out whether the fractional seconds portion of your TIMESTAMP is 0.5 seconds, or more. If so then add a second, otherwise don't.

I find it a lot clearer and easier to understand what's going on. It returns the following:

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 base_val, expected
     , to_timestamp(to_char(base_val, 'YYYY-MM-DD HH24:mi:ss'), 'YYYY-MM-DD HH24:mi:ss')
        + case when to_number(to_char(base_val, 'FF8')) >= 50000000
                    then interval '1' second
               else interval '0' second
          end as solution_add
  from v_data;

BASE_VAL                     EXPECTED                     SOLUTION_ADD
---------------------------- ---------------------------- ----------------------------
10-DEC-12 10.49.30.000000000 10-DEC-12 10.49.30.000000000 10-DEC-12 10.49.30.000000000
10-DEC-12 10.49.30.463000000 10-DEC-12 10.49.30.000000000 10-DEC-12 10.49.30.000000000
10-DEC-12 10.49.30.500000000 10-DEC-12 10.49.31.000000000 10-DEC-12 10.49.31.000000000
10-DEC-12 10.49.30.563000000 10-DEC-12 10.49.31.000000000 10-DEC-12 10.49.31.000000000
10-DEC-12 10.49.30.999999990 10-DEC-12 10.49.31.000000000 10-DEC-12 10.49.31.000000000