Закръгляване на клеймото за време до най-близката секунда

Имам таблица, съдържаща стойности на клеймо за време и искам да закръгля всяка от тези стойности до най-близката секунда, но не мога да я накарам да работи правилно.

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

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 - напр. за 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", когато се използва cast, докато SQL се държи като кръгъл. Oracle реши, че PLSQL е прав, и промени 11g съответно.

т.е. използвайте:

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

въпреки че вероятно бих го посочил изрично, за да премахна имплицитното преобразуване от клеймо за време до дата (избягвайки странния cast())

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

FYI: Оттеглих приемането си и вместо това приех решението на Бен, тъй като IMO е по-ясно и по-лесно за разбиране, но гласувах за вашето - благодаря за помощта. - Frank Schmitt; 11.12.2012

arrow_upward
1
arrow_downward

Много, много закъснях за тази дискусия, но в случай, че някой търси, има едноредов отговор на този въпрос:

Първо CAST към TIMESTAMP(0) - няма дробни секунди, така че те ще бъдат закръглени. След това CAST резултата до ДАТА

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“

---АКТУАЛИЗАЦИЯ---

Прехвърлянето към дата ще се закръгли до най-близката секунда, тъй като типът данни ДАТА има точност от секунди, така че преобразувайте към дата и след това преобразувайте към клеймо за време:

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

twoleggedhorse 10.12.2012

comment

Очевидно няма опция SS при закръгляване, защото CAST(timestamp 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)

Подобни въпроси