sql - 将时间戳舍入到最接近的秒

Question

我有一个包含时间戳值的表，我想将这些值中的每一个四舍五入到最接近的秒数，但我无法正常工作。

到目前为止我的测试数据和方法:

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

我所有的解决方案都有一个缺陷:

solution_round 总是向上取整

solution_cast 适用于 11gR1，但在 11gR2 中，它总是向下舍入(原因:Oracle 改变了行为——它现在截断而不是舍入，参见 https://forums.oracle.com/forums/thread.jspa?threadID=2242066)

对于最后三行，solution_add 返回 10:49:29 而不是 10:49:31

我想 solution_add 应该可以工作，我只是犯了一些愚蠢的错误:-)

编辑:

Ben 的解决方案(见下文)对我有用，但依赖 to_char(timestamp, 'FF') 似乎很危险 - 返回的位数取决于时间戳的定义。

我正在使用 to_char(timestamp, 'FF3') 代替，它似乎可靠地返回毫秒。

Answer 1

我的首选方法是使用 CASE 语句以及您可以将小数秒转换为数字的事实，即:

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')) >= 500000000 
                    then interval '1' second
               else interval '0' second
          end as solution_add
  from v_data

这将删除小数秒。然后计算出 TIMESTAMP 的小数秒部分是 0.5 秒还是更多。如果是，则添加第二个，否则不要。

我发现它更清晰，更容易理解正在发生的事情。它返回以下内容:

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')) >= 500000000 
                    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