Округление метки времени до ближайшей секунды

У меня есть таблица, содержащая значения меток времени, и я хочу округлить каждое из этих значений до ближайшей секунды, но не могу заставить его работать должным образом.

Мои тестовые данные и подходы до сих пор:

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 должен работать, и я только что сделал глупую ошибку :-)

ИЗМЕНИТЬ:

Решение Бена (см. ниже) работает для меня, но полагаться на to_char(timestamp, 'FF') кажется опасным - количество возвращаемых цифр зависит от определения временной метки.

Вместо этого я использую to_char(timestamp, 'FF3'), который надежно возвращает миллисекунды.

sql oracle oracle11g

Frank Schmitt 10.12.2012 источник

comment

Разве там не должно быть скобок? x + 0.5 / 86400 == x + (0.5 / 86400) - JJJ 10.12.2012

comment

Строго говоря, круглые скобки не нужны, потому что приоритет / выше, чем у +. Но я добавил их, тем не менее, чтобы сделать это более ясным, спасибо. - Frank Schmitt 10.12.2012

comment

... да, это то, что я имел в виду. 0,5/86400 не имеет смысла (x + 0.5 / 86400 ~= x + 0.0000058). Разве это не должно быть ( x + 0.5 ) / 86400? - JJJ 10.12.2012

comment

Нет, не должно. Я хочу добавить полсекунды (0,5/(24*60*60), обрезать и использовать это (округление для бедняков) - Frank Schmitt 10.12.2012

comment

Хорошо, я думал, что мы говорим о временных метках Unix. Версия Oracle звучит довольно запутанно. - JJJ 10.12.2012

Ответы (5)

arrow_upward
5
arrow_downward

Мой предпочтительный метод - использовать оператор 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

Ben 10.12.2012

comment

Стоит отметить, что точность, возвращаемая to_char(timestamp, 'FF'), зависит от определения метки времени - например. для timestamp(3) он вернет тысячные доли секунды, и сравнение не будет работать должным образом. Но по моим данным нормально. - Frank Schmitt; 11.12.2012

comment

Это действительно должен был быть FF8 @FrankSchmitt. Как вы заметили, в вашем случае это не имеет значения, но лучше указать. - Ben; 11.12.2012

arrow_upward
3
arrow_downward

ваш окончательный подход работает (solution_add), это просто то, что вы использовали - вместо +. поведение 11g связано с исправлением ошибки (в 10g и ниже plsql вел себя как «trunc» при использовании приведения, тогда как SQL вел себя как круглый. Oracle решил, что PLSQL был прав, и соответственно изменил 11g.

то есть использовать:

   v1.base_val +
   (extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add

хотя я бы, вероятно, указал это явно, чтобы убрать неявное преобразование из временной метки в дату (избегая хитрого приведения())

to_date(to_char(v1.base_val, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss')+
       (( extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400)

eg:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> with v_data as
  2   (select to_timestamp('2012-12-10 10:49:30.00000000',
  3                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
  4           to_timestamp('2012-12-10 10:49:30',
  5                        'YYYY-MM-DD HH24:mi:ss') expected
  6      from dual
  7    union all
  8    select to_timestamp('2012-12-10 10:49:30.46300000',
  9                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 10           to_timestamp('2012-12-10 10:49:30',
 11                        'YYYY-MM-DD HH24:mi:ss') expected
 12      from dual
 13    union all
 14    select to_timestamp('2012-12-10 10:49:30.50000000',
 15                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 16           to_timestamp('2012-12-10 10:49:31',
 17                        'YYYY-MM-DD HH24:mi:ss') expected
 18      from dual
 19    union all
 20    select to_timestamp('2012-12-10 10:49:30.56300000',
 21                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 22           to_timestamp('2012-12-10 10:49:31',
 23                        'YYYY-MM-DD HH24:mi:ss') expected
 24      from dual
 25    union all
 26    select to_timestamp('2012-12-10 10:49:30.99999999',
 27                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 28           to_timestamp('2012-12-10 10:49:31',
 29                        'YYYY-MM-DD HH24:mi:ss') expected
 30      from dual)
 31  select v1.base_val,
 32         v1.expected,
 33         v1.base_val +
 34         (extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add,
 35         to_date(to_char(v1.base_val, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss')+
 36         (( extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400) solution_add2
 37    from v_data v1;

BASE_VAL                  EXPECTED                  SOLUTION_ADD              SOLUTION_ADD2
------------------------- ------------------------- ------------------------- -------------------------
10-dec-12 10:49:30000     10-dec-12 10:49:30000     10-dec-2012 10:49:30      10-dec-2012 10:49:30
10-dec-12 10:49:30463     10-dec-12 10:49:30000     10-dec-2012 10:49:30      10-dec-2012 10:49:30
10-dec-12 10:49:30500     10-dec-12 10:49:31000     10-dec-2012 10:49:31      10-dec-2012 10:49:31
10-dec-12 10:49:30563     10-dec-12 10:49:31000     10-dec-2012 10:49:31      10-dec-2012 10:49:31
10-dec-12 10:49:30999     10-dec-12 10:49:31000     10-dec-2012 10:49:31      10-dec-2012 10:49:31




SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> with v_data as
  2   (select to_timestamp('2012-12-10 10:49:30.00000000',
  3                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
  4           to_timestamp('2012-12-10 10:49:30',
  5                        'YYYY-MM-DD HH24:mi:ss') expected
  6      from dual
  7    union all
  8    select to_timestamp('2012-12-10 10:49:30.46300000',
  9                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 10           to_timestamp('2012-12-10 10:49:30',
 11                        'YYYY-MM-DD HH24:mi:ss') expected
 12      from dual
 13    union all
 14    select to_timestamp('2012-12-10 10:49:30.50000000',
 15                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 16           to_timestamp('2012-12-10 10:49:31',
 17                        'YYYY-MM-DD HH24:mi:ss') expected
 18      from dual
 19    union all
 20    select to_timestamp('2012-12-10 10:49:30.56300000',
 21                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 22           to_timestamp('2012-12-10 10:49:31',
 23                        'YYYY-MM-DD HH24:mi:ss') expected
 24      from dual
 25    union all
 26    select to_timestamp('2012-12-10 10:49:30.99999999',
 27                        'YYYY-MM-DD HH24:mi:ss.FF8') base_val,
 28           to_timestamp('2012-12-10 10:49:31',
 29                        'YYYY-MM-DD HH24:mi:ss') expected
 30      from dual)
 31  select v1.base_val,
 32         v1.expected,
 33         v1.base_val +
 34         (extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400 solution_add,
 35         to_date(to_char(v1.base_val, 'dd-mm-yyyy hh24:mi:ss'), 'dd-mm-yyyy hh24:mi:ss')+
 36         (( extract(second from v1.base_val) - trunc(extract(second from v1.base_val))) / 86400) solution_add2
 37    from v_data v1;

BASE_VAL                  EXPECTED                  SOLUTION_ADD              SOLUTION_ADD2
------------------------- ------------------------- ------------------------- -------------------------
10-dec-12 10:49:30000     10-dec-12 10:49:30000     10-dec-12 10:49:30        10-dec-12 10:49:30
10-dec-12 10:49:30463     10-dec-12 10:49:30000     10-dec-12 10:49:30        10-dec-12 10:49:30
10-dec-12 10:49:30500     10-dec-12 10:49:31000     10-dec-12 10:49:31        10-dec-12 10:49:31
10-dec-12 10:49:30563     10-dec-12 10:49:31000     10-dec-12 10:49:31        10-dec-12 10:49:31
10-dec-12 10:49:30999     10-dec-12 10:49:31000     10-dec-12 10:49:31        10-dec-12 10:49:31

DazzaL 10.12.2012

comment

К вашему сведению: я отозвал свое согласие и вместо этого принял решение Бена, так как IMO оно яснее и проще для понимания, но я проголосовал за ваше - спасибо за вашу помощь. - Frank Schmitt; 11.12.2012

arrow_upward
1
arrow_downward

Я очень, очень опаздываю на это обсуждение, но на всякий случай, если кто-то ищет, есть однострочный ответ на этот вопрос:

Первый CAST к TIMESTAMP (0) - он не имеет дробных секунд, поэтому они будут округлены. Затем ПРИВЕСТИ результат к ДАТЕ

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
with data as (select sys_extract_utc(localtimestamp) ts from dual)
select ts, cast(cast(ts as timestamp(0)) as date) dte
from data;

Stew Ashton 20.09.2018

comment

Вы можете опоздать, но это решение просто блестящее. Спасибо. - Frank Schmitt; 20.09.2018

arrow_upward
0
arrow_downward

Вы пытались преобразовать to_date и выполнить округление в этот момент?

http://www.java2s.com/Tutorial/Oracle/0260__Date-Timestamp-Functions/RoundingtotheNearestMinute.htm

Затем вы сможете преобразовать в временную метку.

Очевидно, измените «MI» в строке 3 на «SS».

---UPDATE---

Приведение к дате будет округлено до ближайшей секунды, поскольку тип данных DATE имеет точность в секундах, поэтому приведение к дате, а затем к отметке времени:

to_timestamp(CAST('2012-12-10 10:49:30.99999999' КАК ДАТА))

twoleggedhorse 10.12.2012

comment

По-видимому, при округлении нет опции SS, потому что CAST (отметка времени AS DATE) автоматически округляется до ближайшей секунды. - twoleggedhorse; 10.12.2012

comment

Это не работает на 11gR2, так как CAST() теперь усекает вместо округления (см. solution_cast в моем вопросе) - Frank Schmitt; 10.12.2012

comment

Интересно, что это ошибка, которая была исправлена. CAST будет усекаться с 11gR2, вам нужно использовать TO_DATE:sqlines.com/oracle/functions/to_date Дайте мне знать, если это сработает, и я обновлю ответ. - twoleggedhorse; 10.12.2012

comment

Извините, но мне непонятно, что именно вы предлагаете - что я использую to_date(timestamp) ? - Frank Schmitt; 10.12.2012

comment

давайте продолжим обсуждение в чате - twoleggedhorse; 10.12.2012

arrow_upward
-1
arrow_downward

Это T-SQL (не уверен, что он работает на Oracle)

DECLARE @tblTime TABLE (ID INT, myTime DATETIME2);
INSERT INTO @tblTime SELECT 1, GETDATE()

SELECT  myTime
    , CONVERT( DATETIME2, CONVERT( VARCHAR, DATEADD( SECOND, CASE WHEN DATEPART( MS, myTime) >= 500 THEN 1 ELSE -1 END, myTime ), 20), 20 ) AS timeRounded
FROM    @tblTime

Joseph Lee 10.12.2012

comment

Это вообще не сработает (даже при удалении табличных переменных) - a_horse_with_no_name; 10.12.2012

comment

Извините, но это точно работает с T-SQL, я пробовал. Но в любом случае, вы можете получить представление о том, как заставить его работать. По сути, это использование оператора CASE, чтобы определить, добавлять ли секунду или нет. И используйте механизм округления (с CONVERT/CAST с форматом, который удаляет часть mmmm из исходной даты и настраивает вторую базу на корректировку из CASE. - Joseph Lee; 11.12.2012

Округление метки времени до ближайшей секунды

Ответы (5)

Похожие вопросы