Arrondir une date

On est souvent confronté à la problématique suivante, comment arrondir une date en dehors des sentiers tracés par trunc (la minute, l’heure, la semaine …) et donc stackoverflow donne ces exemples de réponses pour arrondir une date à 10 minutes mais qui sont facilement extrapolables à d’autres intervals :

Le plus compréhensible

SELECT
  sysdate, TRUNC(sysdate, 'MI') - MOD(TO_CHAR(sysdate, 'MI'), 10) / (24 * 60)
FROM dual;

Le moins lisible

select
  trunc(sysdate, 'mi') - numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10), 'minute')
from dual;

Le plus à jour

select
  trunc(sysdate, 'mi') 
  - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
from dual;

Au niveau du coût pour les 3 on a (avec un temps d’execution identique inférieur au centième de seconde) :

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        657  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Un exemple ?

alter session set NLS_DATE_FORMAT='YY-MM-DDHH24:MI:SS' ;

col DT   for a8 wrap
col S10  for a8 wrap
col M    for a8 wrap
col M5   for a8 wrap
col M10  for a8 wrap
col M15  for a8 wrap
col M30  for a8 wrap
col H2   for a8 wrap
col H3   for a8 wrap
col H6   for a8 wrap
col H8   for a8 wrap
col H12  for a8 wrap
col DAY  for a8 wrap
col WEEK for a8 wrap

select sysdate                                                                                     DT
     , sysdate - mod(EXTRACT(second FROM cast(sysdate as timestamp)), 10) / (24 * 60 * 60)         S10
     , trunc(sysdate, 'mi')                                                                        M
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 5) / (24 * 60)  M5
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60) M10
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 15) / (24 * 60) M15
     , trunc(sysdate, 'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 30) / (24 * 60) M30
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 2) / 24         H2
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 3) / 24         H3
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 6) / 24         H6
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 8) / 24         H8
     , trunc(sysdate, 'hh24') - mod(EXTRACT(hour FROM cast(sysdate as timestamp)), 12) / 24        H12
     , trunc(sysdate, 'dd')                                                                        DAY
     , trunc(sysdate, 'd')                                                                         WEEK
from dual
/
DT S10 M M5 M10 M15 M30 H2 H3 H6 H8 H12 DAY WEEK
18-05-14
17:28:39
18-05-14
17:28:30
18-05-14
17:28:00
18-05-14
17:25:00
18-05-14
17:20:00
18-05-14
17:15:00
18-05-14
17:00:00
18-05-14
16:00:00
18-05-14
15:00:00
18-05-14
12:00:00
18-05-14
16:00:00
18-05-14
12:00:00
18-05-14
00:00:00
18-05-13
00:00:00

C’était pourtant pas si compliqué

Créer un package ADRCI

[oracle@serveur ~/work]$ adrci

ADRCI: Release 11.2.0.4.0 - Production on Thu Jan 7 14:14:24 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"
adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/titi/titi:
*************************************************************************
0 rows fetched

ADR Home = /opt/oracle/diag/rdbms/serveur/DB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
208129               ORA 600 [ktcccod-1]                                         2016-01-05 06:45:45.367000 +01:00
592465               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:27:32.712000 +01:00
592481               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:29:22.188000 +01:00
592466               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:08:52.179000 +01:00
592457               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:11:18.073000 +01:00
592449               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:22:53.246000 +01:00
592467               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:57:21.733000 +01:00
592441               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:20:34.972000 +01:00
592442               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:36:02.718000 +01:00
592443               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:43:26.175000 +01:00



adrci> IPS CREATE PACKAGE PROBLEM 592443
DIA-48448: This command does not support multiple ADR homes


adrci> show homes
ADR Homes:
diag/rdbms/titi/titi
diag/rdbms/serveur/DB

adrci> SET HOME diag/rdbms/serveur/DB
adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/serveur/DB:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
208129               ORA 600 [ktcccod-1]                                         2016-01-05 06:45:45.367000 +01:00
592465               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:27:32.712000 +01:00
592481               ORA 600 [kdBlkCheckError]                                   2016-01-07 11:29:22.188000 +01:00
592466               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:08:52.179000 +01:00
592457               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:11:18.073000 +01:00
592449               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:22:53.246000 +01:00
592467               ORA 600 [kdBlkCheckError]                                   2016-01-07 12:57:21.733000 +01:00
592441               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:20:34.972000 +01:00
592442               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:36:02.718000 +01:00
592443               ORA 600 [kdBlkCheckError]                                   2016-01-07 13:43:26.175000 +01:00
10 rows fetched

adrci> IPS CREATE PACKAGE PROBLEMKEY "ORA 600 [kdBlkCheckError]"
Created package 1 based on problem key ORA 600 [kdBlkCheckError], correlation level typical

adrci> show problem

ADR Home = /opt/oracle/diag/rdbms/serveur/DB:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 600 [ktcccod-1]                                         208129               2016-01-05 06:45:45.367000 +01:00
2                    ORA 600 [kdBlkCheckError]                                   592443               2016-01-07 13:43:26.175000 +01:00
2 rows fetched

adrci> IPS ADD FILE /opt/oracle/diag/rdbms/serveur/DB/trace/DB_s001_217994.trc PACKAGE 2
DIA-49404: No such package [2]

adrci> IPS SHOW PACKAGE
   PACKAGE_ID             1
   PACKAGE_NAME           ORA600kdB_20160107142052
   PACKAGE_DESCRIPTION
   DRIVING_PROBLEM        2
   DRIVING_PROBLEM_KEY    ORA 600 [kdBlkCheckError]
   DRIVING_INCIDENT       592465
   DRIVING_INCIDENT_TIME  2016-01-07 11:27:32.712000 +01:00
   STATUS                 New (0)
   CORRELATION_LEVEL      Typical (2)
   PROBLEMS               1 main problems, 1 correlated problems
   INCIDENTS              6 main incidents, 2 correlated incidents
   INCLUDED_FILES         41

adrci> IPS GENERATE PACKAGE 1 IN /opt/oracle/work
Generated package 1 in file /opt/oracle/work/ORA600kdB_20160107142052_COM_1.zip, mode complete
adrci> exit